Can I add an autoincrement field to a spreadsheet based on a form in google docs?

Question

I use a google docs form to retrieve feedback from our customers. I then download this info into our offline system. Is it possible for google docs 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.