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