Google Form data to store in cloud SQL database

Question

I'm new to Google Forms and Google Apps Script. I have ten Google Forms, and on submitting forms they populate a corresponding Google Sheet.

Now, here is what I want, on submitting form I want that information to also be stored in the cloud SQL database. What steps will accomplish that?

Answer 1

The normal data flow for a Google Form that is accepting responses and replicating them to a Spreadsheet looks like this:

You've got two opportunities for a Form Submission Trigger to replicate the form responses in your Cloud SQL database; you can either trigger from the Google Form Form submit event or the Google Sheets Form submit event.

Either way, you will have a script that gets called for every form submission, and an event object that contains the response values. Your trigger function should use the JDBC to connect to the database - that link includes an intro that walks you through the highlights.

Example

Say we have a form asking two questions, "Name" and "Age". That would result in 3 columns in our spreadsheet; "Timestamp" plus one for each question.

To match that, we have a Cloud SQL database set up with the same three columns.

A Google Sheets Form submit trigger function that wrote to a Cloud SQL database would look like this untested code:

// Replace the variables in this block with real values.
var address = 'database_IP_address';
var user = 'user_name';
var userPwd = 'user_password';
var db = 'database_name';

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

// Receive form response and replicate to a row in SQL table
function handleFormSubmit( event ) {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);

  var stmt = conn.prepareStatement('INSERT INTO entries '
      + '(Timestamp, Name, Age) values (?, ?, ?)');
  stmt.setString(1, event.namedValues['Timestamp']);
  stmt.setString(2, event.namedValues['Name']);
  stmt.setString(3, event.namedValues['Age']);
  stmt.execute();
}