Custom Auto Numbering on Form Submit

Howdy!

I hope someone can help me because I have been struggling with this for days. I’ve created an app that tracks complaints as they are received via a google form. In order to attach an auto number to each complaint, which will serve as a key for each complaint record in the app, I used this script:

    function addAutoNumber() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Form responses 1");
  // Get the last row that has content.
  var LastRow = sheet.getLastRow();
  // Set the first Auto Number
  var AutoNumberStart="2020-001";
 
  //---- First run ------------
  
  //Check if the first column is Timestamp
  if (sheet.getRange(1, 1).getValue() == "Timestamp") {
    // insert a column to the left and the text "TEMP" in the first row
    sheet.insertColumnBefore(1);
    sheet.getRange(1, 1).setValue("TEMP");
    // Fix for (probably) a bug that formats the new column 
    // with the same format of the column used to insert it,
    // in this case the column gets the date format like the Timestamp column.
    // So we set the format to number
    sheet.getRange("A2:A").setNumberFormat(0); 
    // check if there are already form responses and add numbers for them
    if (LastRow>1) {
      for(var ii=2; ii <= LastRow; ii++) {
        sheet.getRange(ii, 1).setValue(AutoNumberStart);
        AutoNumberStart = "2020-"+ ("00" + (parseInt(AutoNumberStart.substr(5, 3), 10) + 1)).slice(-3);
      }
    }
  }
  
  // ---- Add new Auto Number ----------
  
  // Check if there is a Number in the AutoNumber column 
  // for the last Form Submission
  if (sheet.getRange(LastRow, 1).isBlank()) {
  // Check if it is the first Form submission and set the AutoNumberStart
    if (LastRow == 2) {
      sheet.getRange(LastRow, 1).setValue(AutoNumberStart);
    } else {
      // Get the Last AutoNumber from the previous row
      var LastAutoNumber = sheet.getRange(LastRow-1, 1).getValue();
      // Set the next AutoNumber
      sheet.getRange(LastRow, 1).setValue("2020-"+ ("00" + (parseInt(LastAutoNumber.substr(5, 3), 10) + 1)).slice(-3));
    }
  }
}

This script works fine to create the pre-formatted numbers I need, however Appsheet won’t recognize the autonumber as a editable field, and I keep finding out that primary keys must be editable. How do I select this as the primary key? Is there an error in my script? If you need to see my app to figure out what’s going on, I’ll gladly show you.

Thank you in advance for any help offered! :vulcan_salute:

AppSheet–and only AppSheet–must be able to set the key column value of rows added in the app; you cannot auto-generate key column values from the spreadsheet for rows added from within the app itself.

The reason is that a spreadsheet-generated key value won’t exist until the new row is written to the spreadsheet, but the row itself exists in the app the moment is created. The row’s key value must exist at that moment of creation. If the key column has a spreadsheet formula, AppSheet cannot set the column’s value.

To use spreadsheet-managed key column values, you must pre-create rows and assign them key column values. Then, the app must be structured so that it doesn’t add rows, but instead allocates an available pre-created row.

3 Likes

Thanks for your response, Steve. Let me make sure I’m getting this right… If I just have a column full of keys to the left of where the google form begins pasting responses, this will be picked up in Appsheet as a key?

I can’t speak to the behavior of Google Forms, but if you have a bunch of spreadsheet rows that have key column values already, AppSheet will see those rows.

With this setup–where rows are created outside of AppSheet–you’ll need to configure the table in the app to not allow adds:

2 Likes