Need a Unique ID to be populated in Google sheets on Google Form submission

Question

Looking for a dynamic unique ID for Google Forms, to populate into Google Sheets Response form. I don't want to use external addons.

Below is a code I found online but can't get it to work - I'm a beginner to app script. So maybe I'm missing something. The problem seems to be with the "slice" (however that's more of a guess).

• The script was connected to the Google response sheet.
• The trigger was added (to the google sheet) on OnFormSumbit
• No errors occurred with the trigger, when I submitted the form (checked the log afterwards).
• However no unique ID were presented on the response sheet either.
• I'm guessing from my limited experience that the slice function seems to be a problem

Any help or guidance on this would be greatly appreciated.

Ultimately I want to create a form, which when populated gives me a unique JobCard number which is populated in my google sheet Reponses. Something with a set number of Characters (say 6) with a prefix (say JBC) and if this could be in numerical order that would be create but not a deal breaker.

I found the below code however it doesn't seem to work:

/**
* This function extracts the relevant properties from the event handler,
* then uses them to get the uniqueID and record the response
* @param {Object} e The event parameter for form submission to a spreadsheet;
*     e has the following properties values, range, namedValues
*/

function onFormSubmit(e) {
  var uniqueID = getUniqueID(e.values);
  recordResponseID(e.range, uniqueID);
}

/**
* Records the unique ID for this response to the correct cell.
* @param  {Object} eventRange Range in which the response is written
* @param  {Integer} uniqueID   Unique id for this range
*/

function recordResponseID(eventRange, uniqueID) {
  var row = eventRange.getRow();
  var column = eventRange.getLastColumn() + 1;
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(row, column).setValue(uniqueID);

}
/**
* A shortcut function to get the form that is connected to this spreadsheet
* @return {Form}          The form associated with this spreadsheet.
**/

function getConnectedForm() {
  var formUrl = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
  var form =  FormApp.openByUrl(formUrl);
  return form;
}

/**
* Returns a unique ID for the response, by finding the actual Response that
* has the same properties.
* @param  {Array} eventValues Array of: Timestamp_string, form_response_value...
* @return {Integer}             The unique id (by 1 based array position) of the Response
*/

function getUniqueID(eventValues) {
  var isMatch = false;
  var eventItems = eventValues.slice(1);

  var responses = getConnectedForm().getResponses();
  //loop backwards through responses (latest is most likely)
  for (var i = responses.length - 1; i > -1; i--) {
    var responseItems = responses[i].getItemResponses();
    //check each value matches

    for (var j = 0; j < responseItems.length; j++) {
      if (responseItems[j].getResponse() !== eventItems[j]) {
        break;
      }
      isMatch = true;
    }
    if (isMatch) {
      return i + 1;
    }
  }
}

function testOnSubmit() {
  var answers = [
    ["Sue", "39", "Okay I suppose"],
    ["John", "22", "Great"],
    ["Jane", "45", "yeah no"],
    ["Bob", "33", "Super"]
  ];

  var form = getConnectedForm();
  var items = form.getItems();
  for (var i = 0; i < answers.length; i++) {
    var formResponse = form.createResponse();
    for (var j = 0; j < items.length; j++) {
      var item = items[j];
      var itemResponse = item.asTextItem().createResponse(answers[i][j]);
      formResponse.withItemResponse(itemResponse);
    }
    formResponse.submit();
    Utilities.sleep(500);
  }

}

Answer 1

If you never delete any form responses, you can use this formula in row 1 of a free column in the form responses sheet:

=arrayformula( 
  ifs( 
    row(A1:A) = 1, "Unique ID", 
    isblank(A1:A), iferror(1/0), 
    true, "JBC" & text(row(A1:A) - 1, "000000") 
  ) 
)

If you need to be able to delete rows from the form responses sheet, use the code below. Create an installable "on form submit" trigger on the spreadsheet to make it run automatically when new responses are submitted.

/**
* Inserts a unique ID after the last column of the form response sheet
* when a new form response is submitted.
*
* @param {Object} e The "on form submit" event object.
*/
function insertUniqueIdOnFormSubmit(e) {
  // version 1.2, written by --Hyde, 6 June 2022
  //  - see https://webapps.stackexchange.com/q/164893/269219
  if (!e || !e.range) {
    return
  };
  const sheet = e.range.getSheet();

  ////////////////////////////////
  // [START modifiable parameters]
  const sheetsToWatch = /^(Form Responses 1)$/i;
  const uniqueIdCell = sheet.getRange(e.range.rowStart, e.range.columnEnd + 1);
  const _formatNumber = (number) => 'JBC' + right_('000000' + String(number), 6);
  // [END modifiable parameters]
  ////////////////////////////////

  if (!sheet.getName().match(sheetsToWatch)) {
    return;
  }
  const uniqueIdRange = sheet.getRange(1, uniqueIdCell.getColumn(), sheet.getLastRow(), 1);
  const existingUniqueIds = uniqueIdRange.getValues().flat();
  const newId = getUniqueId_(existingUniqueIds, _formatNumber);
  uniqueIdCell.setValue(newId);
}


/**
* Gets a unique ID.
*
* @param {String[]} existingUniqueIds The existing IDs to check to verify that the new ID is unique.
* @param {Function} formatNumber A function to format the number.
* @return {String|Number} A unique ID.
*/
function getUniqueId_(existingUniqueIds, formatNumber) {
  let number = 1;
  let id;
  do {
    id = formatNumber(number++);
  } while (existingUniqueIds.indexOf(id) !== -1);
  return id;
}


/**
* Extracts characters from the end of a text string.
*
* @param {String} text The text string where to extract rightmost characters.
* @param {Number} numCharacters The number of characters to extract.
* @return {String} The extracted characters.
*/
function right_(text, numCharacters) {
  return text.slice(-numCharacters);
}