Create Unique ID Google Form

Question

I have a Google Form which populates a Responses spreadsheet. I have basic understanding of scripts and how to run them. I'm not quite sure where to start on this though..

A user submits a form and a new line is created on row (example) 452 in the Responses spreadsheet. The responses fill columns A to D. I would like column E on that new row to be populated with 'YES'. Could someone help me get start on this please?

This is my cude attempt at the code:

function InsertID() {
  //this script is triggered to run 'From Spreadsheet' on 'Form Submit'
var ss = SpreadsheetApp.getActiveSpreadsheet();            
var sheet = ss.getActiveSheet();                             
var LatestID = sheet.getRange(1, 6).getValue() //cell F1 contains the latest ID number given out  

  row = newform.row 
  cells(row,5) = LastestID + 1 //put the next ID number in column 5 of the new row

  sheet.getRange(1, 6).Value = LatestID + 1 'Increment the LatestID number by 1 

}

Welcome. To start on this, please follow the tour checkout How to Ask and the tags excerpts and wikis as some of them include helpful info including links and tips. Also add a brief description about your search/research efforts. — Rubén
Hi Rubén, thanks for the reply. I would like an ID number to populate next to the new response. I will have a dedicated cell in another part of the sheet with the latest ID number. So new response comes in and lands on row 15, I would like the script to look at the latest ID number (lets say it is 20 and then increments it to 21). So cell E15 will show '21'. I can't simply use the number of responses as the ID number in case someone deletes a few forms (and deletes the lines on the spreadsheet). — John Cassell
Should the ID match the number of submitted responses? On form submit you can create your ID with var id = FormApp.getActiveForm().getResponses().length — k4k4sh1
Basically, the first form completed should get ID number 1. The second form completed should be ID number 2. If form 2 was deleted then the next form should be given the number 3 (i.e it knows there was a number 2 but it doesn't give it the same ID as a previous form. — John Cassell
I have amended my original question to include the code I am trying (I know its wrong but wanted to show that I was trying) — John Cassell

Dawoud Dawoud · Accepted Answer · 2018-02-22T14:55:49

As I understand; you need a column on the responds sheet which automatically generates ID# on each row whenever a new submission occurs?

I believe you need no codes, just a basic formula on the 1st row after the row header: =arrayformula( if( len(B2:B), (row(B2:B) - row(2) + 1), iferror(1/0) ) )

Create Unique ID Google Form

1 Answers