appsheet sync not happening with google sheet

[no-premium-audi]

I see errors being logged several times a day for "Range ('Temp Sheet'!A59) exceeds grid limits. Max rows: 42, max columns: 26" or something similar. It appears that AppSheet is trying to add\edit a row in the Temp Sheet and it's providing an invalid row number. The row that is being referenced changes from error to error. I think this is caused by the script deleting rows from Temp Sheet every 10 minutes. The app isn't aware that rows are missing because the app's cached version of the sheet has more rows than the current version. This could be contributing to row's not being added and files not being recorded in the sheet. how to permanently avoid this issue ?

[Aurelien]

Hi @no-premium-audi 

What about clearing rows instead of deleting ?

It could be interesting, in my opinion, to make your script to clear content every 10mn, and to delete only if more than X rows have been added, beyond 100 rows after the last "non-empty row".

Both scenarios may prevent the app from writing informations on a deleted row, but of if the app is always running somewhere, of course it is hard to deal with this kind of issue.

[no-premium-audi]

hi @Aurelien the issue is app is already in production is and we should only delete the rows which had been moved to a sub folder which is created by using( key generated by appsheet) of user per submission, let's say there are multiple users uploaded at the same time so the files won't  be in added to the sheet in an order  and we cannot clear the rows sequentially , if we do that way then there might be blanks in the sheet and according to the code we are running the loop up to 30 rows and deleting the ones which have been moved by verifying with an array (which stores the name of the file when it is moved) . if we use this way in this kind of process wouldn't it over write any data some times ?

[Aurelien]

Hi @no-premium-audi 

I get your point, but is it necessary to delete rows ? Could you adjust your script into only clearing content in these rows ?

With use of expressions such as getValues(), and expression filter such as here:

https://www.codegrepper.com/code-examples/cobol/google+apps+script+remove+nulls+array

of course, it all depends on how you wish to manage your script, and this is not this forum's purpose.

[no-premium-audi]

hi @Aurelien let's just assume there are 30 rows existing in the sheet so when moving the files to the created folder we'll scan from top to the last row of that sheet at that particular time and   while this process is happening let's say another 30 rows got added  and by the end of the first trigger we would be clearing whatever rows we moved so far at this point we'll scan the sheet from bottom to top at this point of time we'll be having 60 rows (different from the initial point of time ) and clear the rows which have been moved by verifying with the array so this can take time and at this very point lets just say another 30 rows got added and the next time when we iterate from top to bottom (i.e that is to move the files to a folder ) or from top to bottom (i.e clearing the rows which have been moved ) the total number of rows that we have to scan would be 90 , so my point is if we only clear the values in future at some point or the other the execution time will be increased and there will be overlapping of the trigger executions . so what you firstly suggested a hybrid of clearing and deleting the rows can be implemented which can reduce the issue

[Steve]

The only way to avoid the problem is to not delete the rows.

[no-premium-audi]

hi @Steve if we don't delete the rows then the execution time of the trigger will increase and there will be an overlap of the triggers so we have to delete the rows to ensure nothing overlaps 

[Aurelien]

Hi @no-premium-audi 

There must be some optimizing to provide.

What about:

- get all informations in an array

- clear whole sheet content

- append filtered array with only necessary informations

==> No deletion, data processing optimized, no issue with trigger duration