CODEWITHSUNDEEP
google-apps-scriptgoogle-cloud-storagegoogle-forms
James Franco
James Franco

Reputation: 63

Exporting Google Form Responses into Google Cloud SQL

I have a google form setup to submit responses to a Google SQL Database upon submission. I can't seem to figure out how to get the responses off of the form and put them into the strings. Take a look:

var address = 'address';
var user = 'sysImporter';
var userPwd = 'password';
var db = 'db';


var dbUrl = 'jdbc:mysql://' + address + '/' + db;


function handleFormSubmit() {
  
 
  var form = FormApp.getActiveForm();
  var formResponses = form.getResponses();
  
  var formResponse0 = formResponses[0];
  var formResponse1 = formResponses[1];
  var formResponse2 = formResponses[2];
  var formResponse3 = formResponses[3];
  var formResponse4 = formResponses[4];
  var formResponse5 = formResponses[5];
  
  var itemResponses0 = formResponse0.getItemResponses();
  var itemResponses1 = formResponse1.getItemResponses();
  var itemResponses2 = formResponse2.getItemResponses();
  var itemResponses3 = formResponse3.getItemResponses();
  var itemResponses4 = formResponse4.getItemResponses();
  var itemResponses5 = formResponse5.getItemResponses();
  
  var itemResponse0 = itemResponses0[0];
  var itemResponse1 = itemResponses1[0];
  var itemResponse2 = itemResponses2[0];
  var itemResponse3 = itemResponses3[0];
  var itemResponse4 = itemResponses4[0];
  var itemResponse5 = itemResponses5[0];
  
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  
  var stmt = conn.prepareStatement('INSERT INTO Volunteer '
      + '(FirstName, LastName, PrimaryPhone, SecondaryPhone, VolunteerEmail, Volunteerstatus) values (?, ?, ?, ?, ?, ? )');
  stmt.setString(1, itemResponse1.getResponse());
  stmt.setString(2, itemResponse2.getResponse());
  stmt.setString(3, itemResponse3.getResponse());
  stmt.setString(4, itemResponse4.getResponse());
  stmt.setString(5, itemResponse0.getResponse());
  stmt.setString(6, itemResponse5.getResponse());
  stmt.execute();
 
  
}

The current error I get is:

TypeError: Cannot read property 'getItemResponses' of undefined
at handleFormSubmit(Code:24:38)

Upvotes: 0

Views: 487

Answers (1)

James Franco
James Franco

Reputation: 63

getResponse() retrieves all of the responses for a form, from there you must choose which response you want (in my case the most recent) and dissect it for the answers to the questions, here's what I ended up with:

function handleFormSubmit() {

  //Grab form response list
  var form = FormApp.getActiveForm();
  var formResponses = form.getResponses();
  
  //choose most recent response
  var formLastResponse = formResponses.length - 1;
  //grab items from response
  var response = formResponses[formLastResponse];
  var itemResponses = response.getItemResponses();
  
  //set items to local variables
  var email = response.getRespondentEmail();
  var fname = itemResponses[0].getResponse();
  var lname = itemResponses[1].getResponse();
  var primaryphone = itemResponses[2].getResponse();
  var secondaryphone = itemResponses[3].getResponse();
  var staus = itemResponses[4].getResponse();
  
  //connect to DB
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);

  //send SQL Statment to DB
  var stmt = conn.prepareStatement('INSERT INTO Volunteer '
      + '(FirstName, LastName, PrimaryPhone, SecondaryPhone, VolunteerEmail, Volunteerstatus) values (?, ?, ?, ?, ?, ? )');
  

  stmt.setString(1, fname);
  stmt.setString(2, lname);
  stmt.setString(3, primaryphone);
  stmt.setString(4, secondaryphone);
  stmt.setString(5, email);
  stmt.setString(6, staus);

  stmt.execute();
  
}

Upvotes: 1

Related Questions