I am a new user learning how to use AppSheet....

(Prof Stephen Colbran) #1

I am a new user learning how to use AppSheet. I have created a Google Form with 6 questions which are linked to a Google Sheet and works perfectly fine. The data transfers perfectly into APP Sheet. I want to introduce a calculated variable based on values of other variables collected by Google Form and transferred into a Google Sheet. in other words a calculated column in the Google sheet which would then be transferred to AppSheet.

However, when I manually add a new calculated column in the Google sheet based on three of the variable values in sheet columns derived from the Google form I run into problems. A new user filling in the Google form only results in those values being transferred to the Google sheet, the calculation column (IFS statement) for that row is cleared, hence no calculation. Is there some way for Google Sheet calculation columns to do their job when new form data is transferred from a Google form to a Google sheet? If I manually paste the IFS statement into the row of calculated column, the statement works OK. I would like this automatically calculated as new form data rows are added to the Google sheet. If that can be achieved then the calculated variable will flow into AppSheet. Can anyone advise how I achieve this?

(Suvrutt Gurjar) #2

@Prof_Stephen_Colbran,

Based on description given by you and my understanding, you may wish to try following approach.

Does

this calculated column’s

name reflect in AppSheet editor under Data - > Columns option?

If it is so and if the spreadsheet formula option for this calculated column’s definition in AppSheet editor is blank , I request you to regenerate the table’s columns by Regenerate command under Data- > Columns option. While regenerating the table’s columns, please ensure the column has formula in Google spreadsheet in

first data row of the column.

Thereafter the Googlesgeet should do the necessary computation

as you add rows in the app.

You need not have formula in subsequent rows in the Googlesheet.

(Prof Stephen Colbran) #3

@Suvrutt_Gurjar Thanks I will try your solution. I actually found another solution by creating a virtual column in AppSheet based on the same IFS logic I was trying to use in the Google Sheet. This worked and avoided doing anything with Google Sheets. It was all calculated on the fly in AppSheet. Thanks for taking the time to come up with another solution.

(Suvrutt Gurjar) #4

@Prof_Stephen_Colbran, Thank you for your update. Good to know you found a solution. Actually I was about to request you to evaluate similar approach of using AppSheet formulas wherever feasible and applicable but concentrated on requested solution first.

Also certain very few app logic situations may need to use spreadsheet formulas, so I believe an associated discussion was useful.

AppSheet indeed has substantial functions of its own and rich documentation ,sample apps.

explaining those.