CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
Will Laine
Will Laine

Reputation: 61

Creating Form Responses with Google Sheet Data

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]

Upvotes: 1

Views: 561

Answers (1)

Will Laine
Will Laine

Reputation: 61

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

}

Upvotes: 3

Related Questions