Apps Script - Programmatically submit answers from Google Sheet to Google Form - ERROR - “Sorry, this response has already been submitted.”

Question

I have a Google Form and a Google Spreadsheet.

timestamp         | name  |   email | revenue | Edit Url
2015-2-2 02:22:22 | David |         |         |
2015-2-2 07:22:22 | Paul  |         |         |
2015-2-2 09:22:22 | Olive |         |         |

What I am trying to accomplish:

• Based on the information in the Spreadsheet (name, email, revenue) I'd like to programmatically iterate through each row, populate the Form based on the information in each row then submit the form and for each form submitted generate an edit URL which will be stored in the Edit Url column.

So far this is my Google app Script:

function myFunction() {
  createSurveyResponses();
}


function createSurveyResponses() {
  
  // Open a form by ID and add a new text item.
  var form = FormApp.openById('form_id');
  
  var response = form.createResponse();
  

  var sheet = SpreadsheetApp.openById('spreadsheet_id');
  
  var getAct = sheet.getActiveSheet();
  
  var data = sheet.getDataRange().getValues();
  // Access the text item as a generic item.
  var items = form.getItems();
  
  var item = items[0];
  
  var urls = [];
  var resultUrls = [];

 for (var j = 1; j < data.length; j++) {
    var dop = data[j][0]

    if (item.getType() == 'TEXT') {
      var textItem = item.asTextItem();
      var itemResponse = textItem.createResponse(data[j][0]);
      var another = response.withItemResponse(itemResponse);
      response.submit();
    } 
 }
   
  // get the responses from the spreadsheet 
   var fresponses = form.getResponses();
  
   for (var i = 0; i < fresponses.length; i++) { 
     var resp = [fresponses[i]];
     urls.push([shortenUrl(fresponses[i].getEditResponseUrl())]);
   }
  
  var getdata = getAct.getRange(2,5,fresponses.length)
  getdata.setValues(urls);
  
  
}

function shortenUrl(longUrl) {
  // google url shortener api key
  var key = "AIzaSyBVG4Q5i1mNI0YAO0XVGZ3suZU8etTvK34";
  
  var serviceUrl="https://www.googleapis.com/urlshortener/v1/url?key="+key;
  
  var options={
    muteHttpExceptions:true,
    method:"post",
    contentType: "application/json",
    payload : JSON.stringify({'longUrl': longUrl })
  };
  
  var response = UrlFetchApp.fetch(serviceUrl, options);
  
  if(response.getResponseCode() == 200) {
    var content = JSON.parse(response.getContentText());
    if ( (content != null) && (content["id"] != null) )
      return content["id"];
  }
  
  return longUrl;
}

However, when I run the code, after the first iteration (first row) I get an error Sorry, this response has already been submitted. (line 34, file "") which is when I'm submitting the response response.submit();.

What am I doing wrong?

My ultimate goal is to generate a unique URL for each row so that my recipients can use that URL to update their responses whenever they want (getEditResponseUrl()).

Answer 1

This answer explains how to submit answers from a Google Sheet to a Google Form. The first thing that you need to know is the difference between a Form Response and an Item Response.

• Form Response - All the answers to all the questions in the Form.
• Item Response - One answer to one question.

To programmatically submit a response to a Google Form, the code must create a Form Response, and then add Item Responses to the Form Response, and then submit the Form Response. A common mistake is to try to submit the Item Response.

In order to add multiple Item Responses to the Form Response, you'll probably use a loop. And if the code is adding multiple Form Responses, then that will probably use a loop. So, you'll need a loop inside of another loop.

There are multiple things that can go wrong. But basically, the code needs to create both a Form Response, and then multiple Item Responses need to be added to the Form Response. If you confuse the Form and Item Responses, then something will go wrong.

In the code example provided, the outer for loop, loops through the number of spreadsheet rows. The inner for loop, loops through the items in a single form response.

The submit method can NOT be used in the inner loop. Each form item (question) must have an answer added to it with createResponse() and then the Item response must be added to Form response. The word response can be used for either the Form response as a whole, or a response (answer) to a single question.

The Item response is added to the Form response with:

newResponse.withItemResponse(itemResponse);

The method withItemResponse may be confusing. You do not need to chain another method to it to add the answer.

Here is code:

function createSurveyResponses(ss_ID) {
  if (ss_ID === undefined) {
    ss_ID = '';
  };

  var ss = SpreadsheetApp.openById(ss_ID);
  var sheet = ss.getSheetByName('Sheet1');
  //Get data starting in row 2, column 2
  var data = sheet.getRange(2, 2, sheet.getLastRow()-1, sheet.getLastColumn()-1).getValues(); 
  var i = 0,
      j = 0,
      form,
      items,
      thisRow,
      Name = "",
      Email = "",
      Revenue,
      FormURL = "",
      formID,
      thisItem,
      itemTypeIs,
      response,
      arraySS_Values = [],
      editURL;
      var arrayItemNames = ['Name','Email','Revenue'];

  for (i=0;i<data.length;i+=1) {
    thisRow = data[i];
    Name = thisRow[0];
    Email = thisRow[1];
    Revenue = thisRow[2];
    FormURL = thisRow[3];

    arraySS_Values = [];
    
    arraySS_Values.push(Name);//Fill an array with the cell values of one row from the spreadsheet
    arraySS_Values.push(Email);
    arraySS_Values.push(Revenue);

    Logger.log('Name: ' + Name);

    if (FormURL === "" || FormURL === undefined) { //If there is no form, create one
      form = FormApp.create(Name);
      formID = form.getId();
      items = addItemsToForm(form, arrayItemNames);
    } else {
      form = FormApp.openByUrl(FormURL);
      items = form.getItems(FormApp.ItemType.TEXT);
      if (items.length === 0) { //If there are no form items, you must add them
        items = addItemsToForm(form, arrayItemNames);
      };
    };
    
    var newResponse = form.createResponse();

    for (j=0;j<items.length;j+=1) {
      thisItem = items[j];
      itemTypeIs = thisItem.getType();

      if (itemTypeIs===FormApp.ItemType.IMAGE || itemTypeIs===FormApp.ItemType.PAGE_BREAK || itemTypeIs===FormApp.ItemType.SECTION_HEADER) {
        continue; //quit this loop, and loop again if the form item is an image, page break or section header
      };

      if (itemTypeIs === FormApp.ItemType.TEXT) {
        var textItem = thisItem.asTextItem();
        var itemResponse = textItem.createResponse(arraySS_Values[j]);
        
        newResponse.withItemResponse(itemResponse);
        Logger.log('itemResponse: ' + itemResponse.getResponse());
      };
    };

    newResponse.submit();
    var preFill_url = newResponse.toPrefilledUrl();
    Logger.log('preFill_url: ' + preFill_url);

    sheet.getRange(i+2, 5).setValue(preFill_url);
  };
};

function addItemsToForm(form, arrayItemNames) {
  var i=0;
  for (i=0;i<arrayItemNames.length;i+=1) {
    form.addTextItem().setTitle(arrayItemNames[i]);
  };
  
  return form.getItems();
};

The code attempts to deal with the situation of whether a form already exists or not. If the spreadsheet does not have a form URL, then a new form is created. I don't know if you can use shortened URL's with this code, because it may need to open the form by using the URL.