Key field with initial value UNIQUEID() not filled in when I add a row using Google Forms

AppSheet AppSheet Q&A
,
1

I have a table whose source spreadsheet has rows added to it when a Google Form is submitted. This works, but I wanted to replace a row-number key with a unique key, so I performed the following steps:

  1. Add a new column internal case ID to the source spreadsheet.
  2. Manually cut and paste UNIQUEID() values in the internal case ID column of the existing rows, as described here.
  3. Click “Regenerate Structure”, which adds the column to the columns view of the editor.
  4. In the columns view, remove the checkmark from the KEY column of _RowNumber and set the attributes of the internal case ID column as follows:
    TYPE Text
    KEY? :check_box_with_check:
    LABEL? ☐
    FORMULA =_____
    SHOW? ☐
    EDITABLE? :check_box_with_check:
    REQUIRE? :check_box_with_check:
    INITIAL VALUE =UNIQUEID()
    DISPLAY NAME =_____
    DESCRIPTION =_____
    SEARCH? ☐
    SCAN? ☐
    NFC? ☐
    PII? ☐
  5. Save the changes.

When I submitted a form from Google Forms, a row was added to the spreadsheet in which all cells other than the internal case ID were filled in, but the internal case ID remained empty.

I noticed that row 1 of the spreadsheet had a comment of the form AppSheet:{…} in each column except the internal case ID column. I closed the AppSheet editor, went back to the AppSheet add-on in Google Forms, and clicked PREPARE and LAUNCH. The AppSheet editor reopened and the internal case ID column of row 1 in the spreadsheet now had the following comment:

AppSheet:{“IsRequired”:true,“IsEditable”:true,“Type”:“DateTime”,“IsKey”:true,“IsHidden”:true}

I corrected the type from DateTime to Text.

None of this helped. When I submit a form from Google Forms, a row is still added to the spreadsheet in which all cells other than the internal case ID are filled in, but the internal case ID remains empty. What can I do to populate the key?

2

It’s because AppSheet expressions are calculated inside AppSheet, not using any external thing

1 Like
3

Aleksi, from AppSheet support, confirmed that when a spreadsheet is linked to Google Forms, submission of a form fills in only the columns corresponding to the form. Thus adding a column to the spreadsheet generated by Google Forms and specifying a formula for that column has no effect; the formula is never invoked,

I came up with the following workaround, which solves the problem:

  • Create a second spreadsheet, which is identical to the spreadsheet generated from Google Forms, except that there is also a key column.
  • Create a bot that is invoked whenever a row is added to the table for the spreadsheet generated from Google Forms. The bot adds a row to the table for the second spreadsheet, copying the values from the corresponding columns of the table for the Google Forms spreasheet, and using UNIQUEID() as the value for the additional (key) column.
3 Likes
4

Very well done!