Is it possible in Google Forms to give a unique value to each row it inserts into the spreadsheet as well as the timestamp?

  • AFAIK timestamp is automatically added when you create a Form. When you'd look inside the spreadsheet columns, the first one is usually timestamp (unless you deleted it for some reason and I don't really know what happens if you do that) – Robert Koritnik Feb 23 '12 at 8:38
  • I'm guessing that something has changed since this answer was given because after creating the trigger and adding the code to the script, nothing happens when the form is submitted. Does anyone have any advice for getting the above solutions to work on the new version of Google Sheeth – user3255228 Mar 10 '15 at 19:25

You can do this by adding a script trigger.

Supposing your current Form has two columns Timestamp and the answer to one question. So you currently have columns A and B populating with data. Lets assume you want column C to have you auto increment number.

You need to first go to Tools > Script Editor

In the Script Editor window enter the following script:

function onFormSubmit(e) {

var sheet = SpreadsheetApp.getActiveSheet();
var row =  SpreadsheetApp.getActiveSheet().getLastRow();



Save the script and then go to the Triggers menu and select Current script's triggers

Populate the drop downs as follows:

Google Script Triggers

Click Save

Then Save and close the Google App Script window.

Now when your form is submitted it will populate the row number in column C along with the data that has been submitted via your form.

In you want to change the column that the row number gets saved to you need to change this line of the script:


and change the value 3 to the corresponding column index number.

  • Is it possible to use this script but I need a unique number so that if I ever delete or add a row it wont screw it up example: I have ID or row 1, 2, 3, 4, 5, 6 but I manualy delete 5 after a form submit ID 6 will shift to line 5 so the next ID on form submit will be 6 again I will have aduplicate. So is it possible to use the time stamp and make a unique auto-incrementing number out of it? – user17492 Feb 19 '12 at 20:02
  • Even better, I now use this to make a get request to a url I control with all the data and put it straight into my app – Toby Allen Mar 16 '12 at 17:24

Further to the excellent answer by Barry, if you want to be able to remove rows, and still keep a unique ID you can have a static cell that maintains a count. You can then use this number and increment it on every new entry to the table.

So the modification would be to keep a number somewhere on your spreadsheet ('M1' in the code below) and modify the code to look like this:

function onFormSubmit(e) 
   var sheet = SpreadsheetApp.getActiveSheet();
   var row =  SpreadsheetApp.getActiveSheet().getLastRow();

   var bugCount = sheet.getRange("M1").getValue();


Again, change the second last line to change where your ID is placed.

  • I have applied this but this is not successful in the case of deleting the row in between data rows. so It should update the hidden value whenever deleting row occur – user21264 Jun 19 '12 at 8:12
  • The number does not need updating on deletion if unique IDs is all that is required (as per the original question). If you decrease the value on deletion you will get duplicate IDs. – Danny Parker Jun 22 '12 at 14:00

Building on both previous answers (from Barry and Danny):

Assuming the ID column is column A. Pick a "Next ID" cell and set it to the following formula (assuming it is in "P1"):


Create script using the script editor under the "Tools" menu, and paste in the following:

function onFormSubmit(e) {
  // Get the active sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // Get the active row
  var row = sheet.getActiveCell().getRowIndex();
  // Get the next ID value.  NOTE: This cell should be set to: =MAX(A:A)+1
  var id = sheet.getRange("P1").getValue();
  // Check of ID column is empty
  if (sheet.getRange(row, 1).getValue() == "") {
    // Set new ID value
    sheet.getRange(row, 1).setValue(id);

Add a script trigger using the "Triggers" menu in the script editor: enter image description here


Further to the above answers - This solution doesn't require an extra spreadsheet cell.

You can use the inbuilt event handlers for submission of the form to get a unique id. Because the spreadsheet is just the destination for the form, deleting a row doesn't actually delete the response. With that in mind...

EDIT: removed need for ID and dealt with date formatting issues.

* 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]) {
      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]);

  • I don't get this. How does this get called when the form is submitted? – CodyBugstein Sep 11 '15 at 18:00
  • @Imray: I think this must be a script in the spreadsheet. You can set up a trigger in the spreadsheet to run a function when the form is submitted. I will add that fact to this answer if it works (I need to check it). – Gary S. Jan 28 '16 at 13:20
  • 1
    The getUniqueID function can be greatly simplified by simply returning the length of the responses to date (that is what the logic of this function described above is doing in a rather convoluted fashion). Basically a single line in the function: return getConnectedForm().getResponses().length; – Abid H. Mujtaba Feb 6 '16 at 16:33

This is a derivative of the other answers but it might be useful for future users.

function onEdit(e) 
   var sheet = SpreadsheetApp.getActiveSheet();
   var row =  SpreadsheetApp.getActiveSheet().getActiveCell().getRow();

   var bugCount = sheet.getRange("M2").getValue();

   if (sheet.getRange(row, 1).getValue() == "") {


The main difference is it will update column 1 in the active row when that row is edited but only if there is not already a value specified.

You should set the trigger as mentioned in other answers to on edit.

set trigger to on edit


For "Is it possible in Google Form to give a unique value to each row it inserts into the spreadsheet as well as the timestamp?" while also allowing for deletion of a row in the Form responses sheet before the addition of a further Response without duplicating values, this should work:


Not the answer you're looking for? Browse other questions tagged or ask your own question.