Can I add an autoincrement field to a Google Spreadsheet based on a Google Form?

Question

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

Answer 1

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

sheet.getRange(row,3).setValue(row);


}

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

Populate the drop downs as follows:

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:

sheet.getRange(row,3).setValue(row);

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

Answer 2

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();
   bugCount++;

   sheet.getRange(row,1).setValue(bugCount);
   sheet.getRange("M1").setValue(bugCount);
}

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

Answer 3

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"):

=MAX(A:A)+1

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:

Answer 4

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]) {
        break;
      }
      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]);
      formResponse.withItemResponse(itemResponse);
    }
    formResponse.submit();
    Utilities.sleep(500);
  }

}

Answer 5

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();
   bugCount++;

   if (sheet.getRange(row, 1).getValue() == "") {
      sheet.getRange(row,1).setValue(bugCount);
      sheet.getRange("M2").setValue(bugCount);    
   }

}

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.

Answer 6

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:

=iferror(ArrayFormula(match(A1:A,A:A,0)),"")

Answer 7

Although pnuts already described this technique in 2014, somehow it was unnoticed (I figured it out independently, and when I went to write this answer I noticed his answer as the lowest voted answer). So let me just explain the technique:

When Google Forms fills out a spreadsheet it empties the row it fills out, but it leaves intact all the other lines. This means that it's possible to put something on the very first row without it ever getting deleted.

Additionally some functions (functions that return arrays) are able to return multiple cells. You can see this if you enter the following into an empty cell for example:

={{1,1};{1,1}}

This thus allows us to create a function that will fill out the ID without getting it erased. The basic idea is:

=arrayformula(
  if(
    row(A1:A) = 1,
    "ID",
    if(
      not(isblank(A1:A)),
      "ID GENERATING CODE",
      ""
    )
  )
)

The simplest code to replace the "ID GENERATING CODE" with is row(A1:A)-1, but this will break if you delete a row (not an issue in my case, but can be an issue in yours). If it is an issue I would suggest adding a custom hash function and filling the id as hash(timestamp & name) or something along those lines.