So in my spreadsheet there’s only 3 columns that you can edit on the appsheet. (Date, Name & Working Status) but when I change the Working status to “working” the appsheet is not replacing the data on the sheet.
See image below. The process flow would be: From PSR+OBP Pending all the tickets from the sheet will be displayed here, now whenever I edit it and set it to “Working” it should appear on the PSR+OBP Working column and when I change it to “Completed” it should appear on he PSR+OBP Completed. It’s working just fine whenever I change the working status of the tickets, however it’s not updating the values in the google sheets.
Here’s the google sheet. Whenever I change the working status on the AppSheet it should also update the values in the gsheet. But it’s not working :<
Here’s the error:
Is there a Spreadsheet formula set in the column properties in your app?
None, but for the other tab I have an arrayformula and it has the same template but it’s working just fine. For this tab, all data are manually added.
The array formula in the column is the problem. Appsheet knows that the column has a spreadsheet formula in it and when it tries to write a row to your sheet it writes a new formula based on Row/Column numbers. Array formulas do not set ranges in the same way as Appsheet. Appsheet is much more primitive in that way. https://support.google.com/appsheet/answer/10099797?hl=en
My guess is that the array formula in sheets is trying to write the calculated values down each row as the array expands when a new row is added. Appsheet is trying to calculate the new formula so that it can write to the column with the sheets formula in it, but the ranges specified in the array formula and the way appsheet writes a row/column value are mismatched.
Try and reformat your formula so that Appsheet can extend the ‘array’ in the way it expects by calculating the new row/column value. This will mean getting rid of the array formula and calculating per row.
