Sync from App > Google Sheet consistently fails

smarttarget · 10-29-2022 03:08 PM

I've watched the videos and read the posts and still can't find why my app keeps failing. So, hoping to find some help here.

My app is pretty basic for the most part and the section that is failing seems like it should actually be very fast.

Info:

Using Google Sheets for data
- The worksheet in question has ~ 20 rows or less and ~10 columns (trimmed).
- There is a script that updates the worksheet from an external source but the data is static in between so this shouldn't be an issue.
- If I update data in the sheet it syncs seamlessly to the App
Table in AppSheet
- There are 4 Virtual Columns. 3 calculate the sum of 3 of the worksheet columns and 1 calculates the sum of 2 columns and then divides them.
- There is only 1 editable field per row
- The Failure happens when upating this single field whether it is just in one row or a few.

Here are screenshots of the only data I'm getting from an error standpoint. The top image is what you see when selecting the binocular icon in the error report. These errors are "Preview" = true but it also happens in the live (deployed) app.

Any thoughts or ideas would be greatly appreciated. I'm sure I've done something wrong somewhere, I just can't see it yet.

WillowMobileSys

If you click on the "red binoculars" icon in the Log entry, you will be presented with more details about the error - most likely also including a message to explain why the Row Edit is failing.

If it is not evident what the error is, please post a screenshot here of the FULL message in that detail screen. Maybe one of use can decipher it for you.

smarttarget

Thank you for responding. The first screenshot (Performance Details) is the FULL message. That is my issue at the moment, I can't see what the issue is.

WillowMobileSys

EDITED: I just realized what you were mentioning about "first screenshot"!!! It wasn't apparent to me there were two images. LOL! disregard the below.

So if you explicitly click on the "red" binoculars, you don't get more information? See image below

Luis_Rodriguez_

hi, just to start, check if your file is open, also... u got a self-looping formula elsewhere?

smarttarget

Thank you for the thought. I'll go through the app and look for anything that would loop.

smarttarget

I did some digging around and the input field that I am updating gets used in a few calculations. None of which change the total of the input value. At the same time I am doing those calculations in virtual columns in the same data table as the input field. Could this be my issue? Should calculations happen in another table all together?

WillowMobileSys

@Luis_Rodriguez_ wrote:

check if your file is open

If you referring to the datasource sheet file, that is only a problem when using Excel. Google sheets can stay open happily during the entire time you are building and/or running your app!

WillowMobileSys

@smarttarget wrote:

At the same time I am doing those calculations in virtual columns in the same data table as the input field. Could this be my issue? Should calculations happen in another table all together?

If I understand correctly, what you describe should not be an issue.

WillowMobileSys

So lets back up a bit. Looking more closely at your screenshots, I am realizing that these are from the Performance Analyzer. It's tailored more towards giving run time details.

There is also the Audit Log which is where you want to search for app processing errors. Have you also checked the Audit Log?

smarttarget

Here is what I get from the audit. I went through the last 15 or so errors and this is consistently the issue.

FYI, I have gutted the virtual column formulas and am playing with the AppSheet database for this. My use case is to have the totals for immediate reference as the input field is changed which is why I had used virtual columns in the first place. I'll update the post as I get through it but without the virtual columns I'm able to get the data to update without timing out for the first time.

WillowMobileSys

@smarttarget wrote:

I'm able to get the data to update without timing out for the first time.

I wonder if you might have sheet side calculations/service calls as well?

The error implies some wait on Google Sheet services. In a typical app, there is normally only reads and writes from sheet services. With only 20 rows, it would be almost instantaneous. Virtual Column calculations should not interfere with that.

But if you have heavy formulas in the Sheet or reliance on external services, Appsheet processing will wait until those calculations or updates are completed before finishing a Sync. If additionally, the Virtual Columns rely on data returned from sheet calcs or service updates, there could be some back and forth happening along the lines of what @Luis_Rodriguez_ what mentioning.

it may just be a matter of re-organizing the data to prevent inter-dependent operations from happening.

smarttarget

There are no calculations happening in the sheet. There is a script that updates the data once an hour. This creates the posibility that the data in the App is "stale" when it is pushing the input value and trying to compute the SUM of the columns at the same time.

smarttarget

I decided to shelve the AppSheet database idea in favor of a new worksheet with totals to hopefully shorten the learning curve and get this working. Now, the backend won't let me connect a new worksheet from the same Google Sheet that is powering everything else. Pretty frustrating to not know what the actual issue is. Just that Google Sheets are unresponsive. By the way, this isn't a new issue, I've been struggling with this off and on for months now just updating the data directly in the sheet.

smarttarget

Thanks for your help. I think I'm throwing in the towel on this. My support tickets go to a chat for one person that never answers and the errors don't give any information. I have no doubt that I probably created the issue but if it can't be found or fixed I'll go back to using the spreadsheet directly and save myself the stress.

WillowMobileSys

I didn't realize you were utilizing an AppSheet Database the entire time. In case you don't know the AppSheet Database is an extremely new feature and likely has a LOT of issues. Currently it is in preview only - meaning you should only use it to investigate what is coming with the feature and/or test it to help influence and improve the feature for once it rolls out. You should NOT currently build serious apps with it. This likely why Support has not given the tickets priority.

You can find more information on it in this thread

Additionally, you should report the issue you are having in that same thread.

After that, switch to a different datasource to get your app built.

smarttarget

Sorry for the confusion on that. I was only going to try and use the AppSheet database to solve this particular problem and decided against it. My data source was always Google Sheets.