Return to Answer

/**
* 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.1, written by --Hyde, 3 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.getLastColumn() + 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);
}

/**
* 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);
}

/**
* 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.0, written by --Hyde, 2 June 2022
  //  - see https://webapps.stackexchange.com/q/164893/269219
  if (!e || !e.range) {
    return
  };

  ////////////////////////////////
  // [START modifiable parameters]
  const sheetsToWatch = /^(Form Responses 1)$/i;
  const uniqueIdCell = e.range.offset(0, 1, 1, 1);
  const _formatNumber = (number) => 'JBC' + right_('000000' + String(number), 6);
  // [END modifiable parameters]
  ////////////////////////////////
 
  const sheet = e.range.getSheet();
  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);
}

/**
* 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.1, written by --Hyde, 3 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.getLastColumn() + 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);
}

/**
* 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.0, written by --Hyde, 2 June 2022
  //  - see https://webapps.stackexchange.com/q/164893/269219
  if (!e || !e.range) {
    return
  };

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

  const sheet = e.range.getSheet();
  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);
}

/**
* 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.0, written by --Hyde, 2 June 2022
  //  - see https://webapps.stackexchange.com/q/164893/269219
  if (!e || !e.range) {
    return
  };

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

  const sheet = e.range.getSheet();
  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);
}