Creating Form Responses with Google Sheet Data

Question

I have an Google form that is connected to a google Sheet. The problem is that I have existing data in my sheet that wasn't submitted via the form, so I want to be able to grab data from my sheet and submit a form for each row that wasn't filled in from the form. Can I do this using the script editor?

Edit: Here is the code I currently have. Right now, each form is being submitted but the fields say "undefined"

2[Setup]1[Function]

Welcome to Stack Overflow. Yes, you can do this with Apps Script. A good question usually includes some code, and explains what you have tried. — Alan Wells
Does your "Resolved" mean that you now have an answer to the question? Then post it to help others. In case you do not feel it is worth the effort please delete your question. — Yunnosch

Will Laine Will Laine · Accepted Answer · 2017-05-11T18:23:35

Here is the code that correctly submits forms for each row of data in my spreadsheet. First names are in column A, Last names in column B, etc. When you call "sheet.getRange()" on you sheet, you need to make sure you use ".getValues()" before creating a form response with this data. Note: my example code is only submitting forms for rows 3-8.

var ss = SpreadsheetApp.getActive(); 
var sheet = ss.getSheetByName("Data"); // grabs sheet

var formUrl = ss.getFormUrl();             
var form = FormApp.openByUrl(formUrl); // grabs the connected form
var questions = form.getItems();

// Getting the fields of the form questions 
var firstName = questions[0].asTextItem();
var lastName = questions[1].asTextItem();
var emergency = questions[2].asTextItem();
var email = questions[3].asTextItem();
var cell = questions[4].asTextItem();
var specialty = questions[5].asTextItem();


var dataFirstNames = sheet.getRange("A3:A10").getValues();
var dataLastNames = sheet.getRange("B3:B10").getValues();
var dataEmergencyNumbers = sheet.getRange("C3:C10").getValues();
var dataEmails = sheet.getRange("E3:E10").getValues();
var dataCellNumbers = sheet.getRange("F3:F10").getValues();
var dataSpecialties = sheet.getRange("G3:G10").getValues();


  function myFunction() {

  for(i = 0; i < 8; i++) {

      var formResponse = form.createResponse();

      var d1 = dataFirstNames[i];
      var r1 = firstName.createResponse(d1); 

      var d2 = dataLastNames[i];
      var r2 = lastName.createResponse(d2);

      var d3 = dataEmergencyNumbers[i];
      var r3 = emergency.createResponse(d3); 

      var d4 = dataEmails[i];
      var r4 = email.createResponse(d4);

      var d5 = dataCellNumbers[i];
      var r5 = cell.createResponse(d5); 

      var d6 = dataSpecialties[i];
      var r6 = specialty.createResponse(d6);

      formResponse.withItemResponse(r1);
      formResponse.withItemResponse(r2);
      formResponse.withItemResponse(r3);
      formResponse.withItemResponse(r4);
      formResponse.withItemResponse(r5);
      formResponse.withItemResponse(r6);

     formResponse.submit();
 }

}

Creating Form Responses with Google Sheet Data

1 Answers