0

I have a Google Sheet that I'm using as a database for a an app I am building in AppSmith. Really just an interface for people to work with the sheet in a controlled manner.

I'm using the Google Sheet because I'm familiar with layering formulas to make it work the way I want it to work. I have a number of columns that start with an ARRAYFORMULA that gives the name of the column in row 1, blank in blank rows that should be blank, and some programmed information in other rows.

This works great as long as I am working from the spreadsheet or reading/adding rows from the app.

However, when I try to edit the row from the app, the API update will take the "50" that it sees in the column and actually put "50" in the cell, breaking the arrayformula.

Is there any way to prevent API calls from actually editing that column? Or to automatically clear the cell and let the arrayformula expand again?

I found a temporary workaround to push "" for the column(s) I know are arrays, but it seems vulnerable to complications if I add other array columns later, or want to make another form in the app that also updates the sheet.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Eric Edwards
  • 11
  • 3
  • Tip: You might want to watch questions with [tag:google-appsheet]. This app also has the option to use [tag:google-sheets] as database – Rubén Nov 12 '22 at 04:49

1 Answers1

0

As a "rule of thumb", avoid having formulas on sheets being used as "databases" (top row used for field names, 2nd row and below used for data). If you really need to use formulas in the spreadsheet instead of doing the calculations on the "APP", add them on a "mirror" sheet.

This is a common recommendation when using ARRAYFORMULAS to do calculations with data comming from Google Forms.

If you think that creating a "mirror" sheet might cause more problems than benefits, if your "APP" is able to limit the number of columns being edited, put the formulas to the right of the last column linked to the app.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • 1
    This is a great idea. Read from one table (the mirror), write to the other (the original). You use twice as much "space" but that isn't going to be a problem at the number of cells/rows we are working with. I need get familiar with QUERY but that was already on my to-do list. Thanks! – Eric Edwards Nov 12 '22 at 18:24