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

Question

I use a Google Form to retrieve feedback from our customers. I then download this info into our offline system.

Is it possible in Google Form 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: