Unstable column header in GSheet source for Google...

dbaum · 04-30-2023 01:54 PM

Based on the great guidance in Google Forms Integrations: Best Practices - Google Cloud Community I integrated an app with data from a Google Form, and everything was running smoothly until recently--including an AppSheet automation that is triggered by row adds (i.e., new form submissions).

However, the most recent time I received a form submission the app automation didn't function as expected.

I'm pretty sure I identified the proximate cause, although not necessarily the fundamental root cause, and have a question about options for avoiding it.
Also, the issue manifested in a couple ways that were slightly unexpected; so, this post also shares that observation.

Root cause

The cause of the issue seems to be that the data source's column headers reverted to match the form's questions instead of retaining the custom names I had assigned and used in the app. Per the best practices tip, I understood, applied, and successfully tested the following points:

You can rename the column headers in the sheet, and the responses will still go to the correct column.
Finalize Google Form first, before starting spreadsheet or connecting to app.
Rename tab and columns for easy use in AppSheet.

Per the warning that "Remember that a future edit to the Google Form could break your app!", I have not edited the form.

According to the column header cells' edit history, the reversion of all their names resulted from a form submission. Following are two examples, showing one column header that changed from "Email" to "Email Address" and another that changed from "Homepage" to "Your Organizations' Homepage URL". As anyone experienced using Forms as an app data source knows is typical, other columns have far longer names that complicate their use in apps even more.

However, the timestamp in the edit history does not match the timestamp of any form submission. It's possible that I did open the form itself in edit mode in order to reference it, but I did not make any changes to the form. If I did that, I suppose it's possible that action triggered the changes to the column headers in Sheets. FWIW, I just tested that by re-customizing the spreadsheet column headers and then fiddling with the form in edit mode without making any changes--nothing I did in the form affected the spreadsheet.

Regardless, again:

The cell edit histories explicitly reference anonymous users (i.e., not me) and form submission (i.e., not form editing).
Prior form submissions had not affected the column headers.

Questions

Have others encountered this problem?
Does anyone know whether something recently changed about whether Google Forms reverts a custom column header value in a Sheets data source?
In any case, do others have guidance for avoiding the problem (other than not customizing column headers in the first place, although the drawback of that potential constraint can be mitigated by determining clever ways to use the form's question and description fields in tandem)?

Confusing symptoms

When a row was recently added via form submission, it appropriately triggered the associated app automation. However, the column values sent to the server were the data source's reverted column headers--not the AppSheet table's column names. The values included for each column were correct. Of course, since the column names didn't match any of the steps in the automation's process, they didn't execute.

The first confusion was that the automation monitor didn't show any error. The bot run log simply included the event, and not even the subsequent step that immediately failed. That information was only in the audit log. Here are screenshots of those log entries, showing the same columns including in the previous examples.

The other confusion pertains to what did appear in the automation monitor. Based on reminders in AppSheet Help articles, such as Columns: The Essentials - AppSheet Help, that app table column names must match data source column names I expected:

The data source's values are not received by the automation at all because the app definition mediates the information from the data source and can't reconcile the discrepancies between the data source's column headers and the app table's column names.

If the automation indeed receives row data, as did occur, I would have expected that occurred only because:

The data source's values are received by the automation with the app's column names--i.e., the app definition mediates the information from the data source and applies the app's column names to the values from each data source column.

WillowMobileSys

@dbaum wrote:

In any case, do others have guidance for avoiding the problem

Any special reason for using a Google Form over an AppSheet Form? You can provide a link directly to an AppSheet Form view and in that operate like a Google Form. I do recognize that:

Google Forms are free to create and disseminate.
The controls within a Google Form might be more appealing visually and functionally.

@dbaum wrote:

that app table column names must match data source column names

This is only true in a practical sense. If you regenerate, the Table columns take on the sheet header names which might affect existing expressions. In practice, you can change either table names or Google column header names and the app will continue to function fine - until the next re-generation.

Sorry I can't help with the rest.

dbaum

Thanks a lot for sifting through my report and offering your perspective.

@WillowMobileSys wrote:

You can provide a link directly to an AppSheet Form view and in that operate like a Google Form.

Yes, understood. My use case is related to your point that "Google Forms are free to create and disseminate." The form in question is open to the public, while the app is not.

@WillowMobileSys wrote:

you can change either table names or Google column header names and the app will continue to function fine - until the next re-generation

That was always my understanding as well, which is why I was confused by what I observed and reported here. At least an app's automation definitely did not function after the spreadsheet's column headers changed without corresponding changes in the app's column names.

Also, in the course of troubleshooting this issue I noticed that the AppSheet Help instruction is more explicit than I recalled:

There should rarely be a reason to change the column name. If the column name in the editor isn't the same as the column header name in your spreadsheet, errors will occur.

If there's a mismatch between columns in the app editor and the spreadsheet data, you'll get an error that means you need to regenerate the table.

WillowMobileSys

@dbaum wrote:

At least an app's automation definitely did not function after the spreadsheet's column headers changed without corresponding changes in the app's column names.

It may be that I have never hit this scenario to experience this error. I do not rename columns after a re-generate except maybe to to test something - e.g. where was that column being used in the app expressions.

dbaum

I observed parts of this issue just happen again--repeatedly. I received a new form submission, which triggered the automation as expected, which successfully processed as expected.

Later, I opened the form and from the form I selected "View in Sheets" to open the linked spreadsheet. When the spreadsheet opened, the customized column headers appeared briefly, but then immediately reverted to match the form's questions.
I re-customized all the column headers (see following tip) and closed the sheet. Later, from my app table in the editor I selected "View data source" to open the spreadsheet. When it opened, the column headers had again reverted to match the form's questions.
I re-customized all the column headers again. Later, from my Chrome recent files I re-opened the spreadsheet. When it opened, the column headers had yet again reverted to match the form's questions.

Tip

@dbaum wrote:

re-customizing the spreadsheet column headers

I realized that one quick way to do this is using Sheet's Restore Version feature. (Of course, in terms of the full problem reported in the original post, this technique doesn't help until you realize the column headers have reverted, which may only happen when something in the app fails.)