Search code examples
google-apps-scriptgoogle-sheetsgoogle-forms

Google Forms - Add unique identifier to submissions - not using an array formula

I previously used an array formula to assign a unique ID to new form submissions; but with 27 columns and over 5000 rows of data, the cost to run the array formula interferes with my onformsubmit to send out a custom email to a subset of people blocking it from sending out emails.

I'm trying to assign the value "Txxxxxx" where xxxxxx is the next number of the submissions in Column 22, i.e. T05000 or T00100

I have no idea on how to write the code to do this, anyone have any ideas? Would I have to trigger it with an onformsubmit? Would that interfere with the onformsubmit I already have to send out emails?

Sample location of column to add unique id

Solution

  • I believe your goal as follows.

    • You want to put a counter value when the Google form is submitted.
    • In your situation, the counter value is put to the column "V" as the format like T001216 and T001217.
      • When the previous counter is T001217, when the Google form is submitted, you want to put the value of T001218 to the column "V" of the submitted row.

    In this case, I would like to propose the following sample script. In this sample script, the method of autoFill in Class Range is used.

    Sample script:

    Please copy and paste the following script to the container-bound script of Google Spreadsheet that the value is submitted from Google form. And please install the OnSubmit trigger to the function of onSubmit. By this, when the Google form is submitted, the value like T01218 is added to the column "V".

    function onSubmit(e) {
  const initialValue = "T001216"; // This is the initial value at the row 2.
  const counterColumn = 22; // 22 is the column "V".

  const range = e.range;
  const sheet = range.getSheet();
  if (range.rowStart == 2) {
    sheet.getRange(2, counterColumn).setValue(initialValue);
    return;
  }
  const previousRange = sheet.getRange(2, counterColumn, range.rowStart - 2);
  previousRange.autoFill(previousRange.offset(0, 0, range.rowStart - 1), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
}
    • In this sample script, when the 1st value is submitted of Google form, the value of initialValue is used as the initial value. In this sample script, the value of T001216 is used because of your sample image.

    Note:

    • This sample script is run by the installable OnSubmite trigger, when the Google form is submitted. So, when you directly run the function of onSubmit at the script editor, an error like TypeError: Cannot read property 'range' of undefined occurs. Please be careful this.

    References: