Appsheet VERY slow to save changes to googlesheet

Phil_Waite · 08-17-2020 11:56 AM

I have an app that is used by 2 sales people (usually 3). Starting this morning appsheet has taken a VERY long time to save their changes to the googlesheet. Three hours after they stopped using it the googlesheet is still being updated. They had up to 150 changes each showing as pending in the top right hand corner by the sync button.

Previously to that I told them to stop and let it get down to zero and catch up. After that we had no choice but to continue for the day and let the changes build up as customers needed calling so they can place orders.

I can’t even make copies of the app and try and make changes/delete tables to try and rectify the problem as the googlesheet is still updating 3 hours later and at the current rate will take another 2.

Any help is much appreciated

Phil

pravse

Any changes in the app definition on your side — eg: any workflow rules added?

Also, were you able to look at the audit logs/ perf logs for the app?

Phil_Waite

Hi Praveen,

I added a new column to a table over the weekend and had it set to “price” type rather than yes/no which it should have been. So when an order was entered by one of our team on the system it wouldn’t accept $ No as an input.

I went in and changed the setting and told them to resync so the change would take effect.

I also got emails from Zapier saying that something was wrong and my Zaps wouldn’t work. After that the changes started taking ages.

This is the error that came up in the log in the morning

Properties:
{
“TableName”: “Full List table”,
“apiLevel”: “2”,
“appStartTime”: “2020-08-17T08:56:33.508Z”,
“appTemplateVersion”: “1.000264”,
“build”: “12ca9763df86f135c448-1597390092254-171cbfa46”,
“checkCache”: “true”,
“clientId”: “7dd25606-f3de-474a-985c-1648b7e84f7f”,
“dataStamp”: “2020-08-17T09:31:51.067Z”,
“isPreview”: “false”,
“lastSyncTime”: “2020-08-17T08:58:16.5777613Z”,
“localVersion”: “1.000264”,
“locale”: “en-GB”,
“mechanism”: “QuickEdit”,
“requestId”: “74032574”,
“requestStartTime”: “2020-08-17T09:32:45.593Z”,
“timestamp”: “2020-08-17T09:10:21.925Z”,
“tzOffset”: “-60”,
“viewName”: “USER 60”,
“AppTemplateVersion”: “1.000264”,
“RowSize”: 7471,
“AppTemplateName”: “e1a35901-4d2f-4d34-8594-fcbd15566d05”,
“Operation”: “Edit row”,
“RecordType”: “Stop”,
“ResultError”: “A duplicate request is already in progress. Please wait a moment and try again.”,
“ReturnedFromCache”: false,
“Performance”: “{“Version”:1,“Time”:“00:00:00.0031208”,“PerformanceTimingRoot”:{“Mid”:154,“Params”:{“ParamList”:[{“Pid”:13,“Value”:“Full List table”}]},“Timer”:{“Time”:“00:00:00.0031208”}},“IsEmpty”:false}”,
“Result”: “Failure”
}

Thanks

Phil

Phil_Waite

This is the googlesheet formula that is in the new column

=ISNUMBER(SEARCH(“FOC Rep”,S662))

It doesn’t look bad - I have much longer more complicated ones

Other than that I haven’t added anything. Do you think it is an expression in appsheet or a gogglesheet formula that is slowing it?

thanks

Phil

pravse

It sounds like it is slowly draining the changes, right? The most likeluy explanations are:

There is a new formula added in the sheet that takes a long time to compute
There is a workflow rule added in the app definitoon that takes a long time to process

Again, the audit log / performance log should provide a lot of insight.

pravse

That formula seems harmless. A very simple way to tell is to open the Google sheet in a browser, make a change (similar to what you’re trying to change via AppSheet) and see if the formula bar on the top right shows up and keeps spinning.

IN your performance logs, you want to look for add/edit entries that take a very long time. When they are in progress, if you try to sync again, those get rejected as being a duplicate entry (that’s the one you’ve posted above).

Is Zapier connected directly to your sheet, or is it connected to your AppSheet app via the API?

Phil_Waite

Hi,

I have made a copy of the sheet as I can’t make a change in the original one until all the changes have been saved.

I realised I have added a formula to look up the number of unique entries in the call logger to show how many individual outlets are targeted by phone in a day regardless of how many times they were called.

The update bar top right is taking ages when I add a call log to that table and recalculates instantly when I remove that formula.

If that is the offending formula - should I delete it from the column in the live sheet that is being saved to or shall I wait untill all changes have finally been saved?

Thanks

Phil

Phil_Waite

This was the formula

=SUMPRODUCT((‘Call Logger’!E:E=A339)/IF(COUNTIFS(‘Call Logger’!D:D,‘Call Logger’!D:D,‘Call Logger’!E:E,A339)=0,1,COUNTIFS(‘Call Logger’!D:D,‘Call Logger’!D:D&"",‘Call Logger’!E:E,A339)))

Phil_Waite

I’ve deleted the formula and it looks as though the changes are coming i faster now on the sheet.

Thank you Praveen - you’ve been a big help!

Phil

pravse

Aha. Yes!

Google Sheets recomputes that formula on every update. And if that is a long-running formula, then it delays everything.

pravse

That’s the dangerous thing about declarative logic — you can express what you want, but you don’t really know how expensive it might be.

jarcmaster

… and something more dangerous than that is a person not keeping a LOG of changes with date and time, this way when something is going on the log will point to a date and time to approximate problem when issue occurs.

A simple:
Shet Name:
Feature:
Type : add / remove
Date:
Time:

would do. Take it into consideration my friend.