I'd like to ask about the "Spreadsheet formul...

(Kirk Masden) #1

I’d like to ask about the “Spreadsheet formulas” that

AppSheet creates for columns in our spreadsheets that contain formulas.

Here’s my question: What happens (or what should happen) to the “Spreadsheet formula” inside the AppSheet editor when we change the related formula in our Google spreadsheet?

I’ve been having some trouble with a sync issue recently (see https://plus.google.com/105035465983967629689/posts/Fkax6VuS7vp ) and so have been trying to tweak my spreadsheet and app to resolve it.

I noticed that even though I changed a formula on my spreadsheet, that change was not reflected in the “Spreadsheet formula” inside the app editor.

I used the “Regenerate” function, but to no avail.

I’m considering altering the formula by hand but I can’t understand why a formula that was generated automatically cannot be regenerated automatically as well.

Actually, even if I delete a formula, the performance of that column seems to be unchanged, so what’s going on?

(Kirk Masden) #2

Thanks @Grant_Stead!

I was just looking at the support page about “Spreadsheet formulas”:

help.appsheet.com - Spreadsheet Formulas

It doesn’t even distinguish between the formulas we put in our Google spreadsheets and the ones that AppSheet generates from those.

Hmmmm. Spreadsheet Formulas help.appsheet.com

(Kirk Masden) #3

@Grant_Stead and anyone who might be interested:

I did the ultimate “regenerate” and it solved a sync issue for me (and, of course, the Spreadsheet formulas were regenerated as well).

What I did was to make another copy of the sheet whose table I wanted to regenerate (that is, I “duplicated” it within my Google spreadsheet book).

Then, I performed the tedious task of bringing that into my app as a new table, making sure the column settings were the same as those for the old table, copying various formulas, and copying a needed action, then, finally hooking the new table up to the UX I was having problems with.

This complete regeneration fixed the sync problem I was having.

My question is: "Why the !@#$%^&*

did I have to do all this?" (pardon my grawlix :wink: )

Shouldn’t the normal regeneration process do a complete regeneration?

The sync issue I had been discussing (and which was, until a minute ago, unresolved) is described here:

plus.google.com - I’m having a sync problem very much like the problem I reported a year ago (w… I’m having a sync problem very much like the problem I reported a year ago (w… plus.google.com

(Kirk Masden) #4

UPDATE: The “ultimate regenerate” is not what fixed the syncing problem. When I made a new table I allowed rows to be deleted, even though I don’t really want to delete any of them.

Checking “DELETES” in what is allowed for a table seems to lead to the data being updated more frequently, even if it is essentially read only data.

(Grant Stead) #5

@Kirk_Masden fascinating…

(Grant Stead) #6

@Kirk_Masden I typically allow deletes, but block it with an archive layer… I with they would add the archive layer at the table level, and let us choose a column for the flag

(Kirk Masden) #7

Sounds interesting.

Thanks for the explanation.

To be honest, though, I’m afraid I don’t understand what an archive layer is.

(Grant Stead) #8

@Kirk_Masden pretty simple… Just have a column “archive” hide it, Y/N, then create an action that data change to TRUE… Then a simple security filter on the table… Everyone thinks they’re deleting a record, but really they’re just making it deleted… This way if an accident happens, or an employee goes rogue, we can fix it easy…

(Kirk Masden) #9

Thanks!

(Grant Stead) #10

Following. Yes, this one isn’t easy. I take the formula out of the sheet completely. Regen, then add the new formula in the sheet, then regen. then it usually catches it.