AppSheet allows us to combine the computing power of Google Sheets and other spreadsheets with that of AppSheet expressions. While it’s generally better not to rely on computations in Google Sheets because it can cause syncing delays and issues, sometimes letting Google Sheets do some calculations in the background is the best way. For example, in the following post, I explain how to make a mirrored histogram using Google Sheets in a way that I don’t think is possible in AppSheet at this point:
But then there is the issue of those periods of time between user input of data, background calculations in a spreadsheet, and the proper reflection of those calculations in your app. I have found situations in which incorrect numbers are shown until all of the syncing is finished. In some situations the delay before correct data is displayed doesn’t strike me as a big problem but there are other situations where any display of “wrong” information is unacceptable.
My hack to deal with this kind of problem is as follows:
if([Session no copy]<>[Number],“Calculating . . .”,
The [Number] in this case is something that has just been changed by the user. So, AppSheet “knows” the current value. The [Session no copy] is a column that merely reproduces [Number] via the Google spreadsheet. In the spreadsheet, [Number] is J2, and [Session no copy] is “=J2”. The result is that [Session no copy] will not equal [Number] until the sync is complete. In other words, AppSheet won’t “know” what the new value of [Session no copy] is until the sync is complete. Hence, instead of displaying incorrect numbers, my app displays the following:
Then, when the sync is complete, AppSheet sees that [Number] and [Session no copy] have the same value and so it displays that the calculation, which is now correct:
By the way, the bars, numbers and stars representing the amount of work done each day are another example of something I was able to put together on Google sheets but wouldn’t know how to do on AppSheet. Notice that the data for January 18 (1月18日) changes after the sync.
Finally, I’m posting this as a tip but I’d also like to make a request to AppSheet. Could you make some kind of expression that we could use to determine when a sync has been completed so that the kind of hack I have explained here would not be necessary? For example, perhaps it could be a SYNCED() expression that would work as follows:
if(SYNCED([my_column])=true,“Your sync is complete”,“We’re working on it. Please be patient.”)