Form in Appsheet - create a new line by field in a Google sheet

[raphaellng]

Hi Everyone, 

I have a project in mind for my work and I have a technical question, before going further in it.

Basically, I want a user to input some KPI scores in each field of a form, and then datas go to a Google Sheet.

When looking in generic tools (Google Form, Smartsheet Form, ect) each field of the form will create a column, and one answer corresponds to one line in Google sheet, with as many columns as fields in the form. 

So then I need to transpose each columns so I get one column with the KPI Id, and one column with scores, so I can create some dashboard.. It is really time consuming ! 

KPI 1 KPI 2 KPI 3 ...

80%  90%   100%

transpose 

KPI 1 80%

KPI 2 90%

KPI 3 100% 

Do you know if it would be possible in Appsheet to create a new line in GoogleS for each field of a form when it is submitted, to create directly this kind of table in GoogleS: 

KPI 1 score

KPI 2 score 

KPI 3 score 

Thank you for your help 

Raphaël 

[MattJP]

Hope I have understood you.

I had something similar where I set up two tables in AppSheet/Excel, one with user/owner details and one with KPI Score and foreign key reference back to the user/owner tables

User/Owner

ID	Name
1	Bob
2	Betty

KPI Score (KPI 3 not shown for brevity) (EXCEL VLOOKUP() in spreadsheet shown for simplicity)

ID	KPI Type	KPI Score	User ID	User Name
1	1	60%	1	Bob
2	2	70%	1	Bob
3	1	70%	2	Betty
4	2	60%	2	Betty

When I came to build the dashboard in Excel I did a lookup back to the user name and built a pivot table.

One of the side effects of using AppSheet is that you generally have to normalise (at least to some extent) and split tables up in the Sheet so it works in both Sheets and AppSheet.

There are probably lots of better ways to do this.

[Joseph_Seddik · Accepted solution]

Hello Raphael and welcome to the community!

Your requirement is very easy to achieve with AppSheet and you won't have any problem fulfilling it. You just have to forgo the notions of spreadsheets and instead model your data as a database. 

This will help you get started:

Data: The Essentials | AppSheet Help Center 

[raphaellng]

Hello, 

Thank you to both of you for your answers. @MattJP I think I understand what you've down but in my opinion it's not really similar to what I need, or maybe too complexe. I think the second option with Data base is better and if in Appsheet it's possible to set the way datas are coming in the database that would be great ! thank you for sharing the doc

Raphaël