Creating Apps Script function that create unique ID for each new Google forms submission - Stack Overflow

admin管理员组

文章数量:1335103

I am trying to write a code that automatically assigns a unique ID to a row for each new Google Forms entry when it gets submitted.

I was going to take the last 4 digits of column 'K' in the sheet and concatenate with a standard 'GS0' prefix and input into column 'B'. Tried piecing two different codes I found from online and by asking Chatgpt but does not seem to be working.

Understand that this is not the ideal code as it runs through the entire sheet and populates all unfilled rows. Will appreciate any help with pointing to past codes or to correct the code for future documentation. I can't seem to find something that meets my needs here.

Thank you very much!

var SHEETNAME = "Memos";
var ID_COLUMN = 1;
var ID_LENGTH = 5;

function generateUniqueID() {
// Get the active sheet and define columns for data and IDs
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const dataRange = sheet.getDataRange();
const data = dataRange.getValues();

// Define the column indices (e.g., data column and ID column)
const dataColumn = 11; // Adjust this to the column with data (1 = A, 2 = B, etc.)
const idColumn = 2;   // Adjust this to the column where IDs should be placed

  // Loop through each row and generate the ID
  for (let i = 1; i < data.length; i++) { // Start from 1 to skip the header
    const value = data[i][dataColumn - 1].toString(); // Convert to string to access last 5 digits
    const lastFiveDigits = value.slice(-5); // Get last 5 digits
    
    // Generate unique ID
    const uniqueID = `GS0${lastFiveDigits}`;

    // Write the unique ID to the ID column
    sheet.getRange(i + 1, idColumn).setValue(uniqueID);
  }
}

function onEdit(evt) {
  var range = evt.range;
  var sheet = range.getSheet();
  if(sheet.getSheetName() !== SHEETNAME) return;

  // getValues()
  // as cells: [[A1,B1,C1],[A2,B2,C2],[A3,B3,C3],[A4,B4,C4],[...]]
  // as locals: [[11,21,31],[12,22,32],[13,23,33],[14,24,34],[...]]
  var rangeValues = range.getValues();

  // Loop over each row of the range and check for data being entered.
  // We don't want to commit a UID value to the ID column if the data
  // in adjacent columns was just deleted. We only want a UID for rows
  // with data in them.
  rangeValues.forEach(function(row,index,arr){
    var conc = row.join("").length; // Where we check for data in the row
    if(conc > 0) { // The current row edited is NOT empty. Proceed.
      var idRange = sheet.getRange( range.getRow() + index, ID_COLUMN ); // This is a 1-dimensional range that contains the ID cell we want to populate
      var idCell = idRange.getCell( 1, 1 ); // This drills down into that single-dimensional range and picks out the cell (yeah, seems redundant but... Google)
      var idValue = idCell.getValue(); // This is the actual value of that ID cell.    If there's already a UID in there, we DO NOT want to change it.
      if (idValue == "") {
        idCell.setValue( generateUID() ); // Ok, everything above checks out. Let's give this row a UID
      }
    }
  });
  
}

I am trying to write a code that automatically assigns a unique ID to a row for each new Google Forms entry when it gets submitted.

I was going to take the last 4 digits of column 'K' in the sheet and concatenate with a standard 'GS0' prefix and input into column 'B'. Tried piecing two different codes I found from online and by asking Chatgpt but does not seem to be working.

Understand that this is not the ideal code as it runs through the entire sheet and populates all unfilled rows. Will appreciate any help with pointing to past codes or to correct the code for future documentation. I can't seem to find something that meets my needs here.

Thank you very much!

var SHEETNAME = "Memos";
var ID_COLUMN = 1;
var ID_LENGTH = 5;

function generateUniqueID() {
// Get the active sheet and define columns for data and IDs
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const dataRange = sheet.getDataRange();
const data = dataRange.getValues();

// Define the column indices (e.g., data column and ID column)
const dataColumn = 11; // Adjust this to the column with data (1 = A, 2 = B, etc.)
const idColumn = 2;   // Adjust this to the column where IDs should be placed

  // Loop through each row and generate the ID
  for (let i = 1; i < data.length; i++) { // Start from 1 to skip the header
    const value = data[i][dataColumn - 1].toString(); // Convert to string to access last 5 digits
    const lastFiveDigits = value.slice(-5); // Get last 5 digits
    
    // Generate unique ID
    const uniqueID = `GS0${lastFiveDigits}`;

    // Write the unique ID to the ID column
    sheet.getRange(i + 1, idColumn).setValue(uniqueID);
  }
}

function onEdit(evt) {
  var range = evt.range;
  var sheet = range.getSheet();
  if(sheet.getSheetName() !== SHEETNAME) return;

  // getValues()
  // as cells: [[A1,B1,C1],[A2,B2,C2],[A3,B3,C3],[A4,B4,C4],[...]]
  // as locals: [[11,21,31],[12,22,32],[13,23,33],[14,24,34],[...]]
  var rangeValues = range.getValues();

  // Loop over each row of the range and check for data being entered.
  // We don't want to commit a UID value to the ID column if the data
  // in adjacent columns was just deleted. We only want a UID for rows
  // with data in them.
  rangeValues.forEach(function(row,index,arr){
    var conc = row.join("").length; // Where we check for data in the row
    if(conc > 0) { // The current row edited is NOT empty. Proceed.
      var idRange = sheet.getRange( range.getRow() + index, ID_COLUMN ); // This is a 1-dimensional range that contains the ID cell we want to populate
      var idCell = idRange.getCell( 1, 1 ); // This drills down into that single-dimensional range and picks out the cell (yeah, seems redundant but... Google)
      var idValue = idCell.getValue(); // This is the actual value of that ID cell.    If there's already a UID in there, we DO NOT want to change it.
      if (idValue == "") {
        idCell.setValue( generateUID() ); // Ok, everything above checks out. Let's give this row a UID
      }
    }
  });
  
}

• google-sheets
• google-apps-script
• google-forms
• google-workspace

Share Improve this question Follow asked Nov 20, 2024 at 4:01 Daryl GohDaryl Goh 1 2

• Hi Welcome! Would you be able to provide your sample sheet, with your initial output, and also your expected output so that we can further help you. You may use this anonymous sheet maker to create your reproducible example without your email being exposed. – Patsytalk Commented Nov 20, 2024 at 6:48
• 1 I am trying to write a code that automatically assigns a unique ID to a row for each new Google Forms entry when it gets submitted. Would you please elaborate on why you want to assign a unique ID to each response. This will assist users to understand how you propose to use the ID. FWIW, the Timestamp is unique to every row. – Tedinoz Commented Nov 20, 2024 at 13:03

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You want to create a unique ID for each Form Submission passed to a linked Spreadsheet. The ID should be the last four digits of the value in Column K (question#10) prefixed "GS0". For example, GS01234.

Consider this answer:

• it is a script bound to the linked Spreadsheet
• the function (updateID(e)) is run by an installable onFormSubmit trigger - it runs as each new Form Submission is received.
• Event Objects are used to identify the row number for each new Submission on the spreadsheet as well as the value in Column K.
• the "Forms Responses" sheet is untouched; a second sheet (named "Master" in this answer) is used to build a duplicate of the Form responses including the unique ID.
• slice is used to get the last 4 characters of Column K
• toSpliced is used to insert the UniqueID into an array.
• the updated output data is copied to the "Master"

---

function updateID(e) {

  // Logger.log(JSON.stringify(e))  

  // identifythe input and output sheets
  var inputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1")
  var outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master")

  // get the Event Objects
  var editedRow = e.range.rowStart
  var values = e.values
  var colK = values[10]

  // create the Unique ID
  var uniqueID = "GS0"+colK.slice(-4)
  // Logger.log("DEBUG: the unique ID = "+uniqueID)

  // get the Form submission from the Responses sheet
  var inputData = inputSheet.getRange(editedRow,1,1,11).getValues()
  // Logger.log(inputData) // DEBUG

  // insert the Id into the form response
  var outputData = inputData[0].toSpliced(1, 0, uniqueID)
  // Logger.log(outputData) // DEBUG

  // update to Master Sheet; leave Form Responses intact
  // appendRow not used because some fields could be blank
  outputSheet.getRange(editedRow,1,1,12).setValues([outputData])
}

---

SAMPLE

本文标签： Creating Apps Script function that create unique ID for each new Google forms submissionStack Overflow