Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

4 Answers

up vote 6 down vote accepted

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:

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:

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

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

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
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
add comment

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: enter image description here

share|improve this answer
add comment

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

}
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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