Google Form data to store in cloud SQL database

问题:

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?

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

回答2:

If you don't want to roll your own solution, SeekWell lets you automatically send data from SQL to Sheets and can also sync changes from Sheets back to a database. Apps Script can do pieces of this, but I found it buggy and limited for our use case, so I built something else. There are both free and paid plans.

Disclaimer: I built this.

回答3:

Posing an example of a solution I have working. I modified Mogsdad's script to use the most up to date parameters & connection function, and fixed the syntax errors.

//Event is automatially passed on form submission when executed as a trigger. 
//It contains objects that the user submitted
function writeToCloudSQL(event) {
//Note we get subname from the 'Instance Connection Name' of the Overview tab in the CloudSQL portal
var subname = 'see image';
var user = 'user';
var userPwd = 'pwd';
var db = 'db_name';

var dbUrl = 'jdbc:google:mysql://' + subname + '/' + db;

var conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);

var stmt = conn.prepareStatement('INSERT INTO tbl_users (Timestamp, DL_ID, DOB, Last4SSN, HTMLGathered) values (NOW(), ?, ?, ?, \'No\');');
stmt.setString(1, event.namedValues['DL_ID']);
stmt.setString(2, event.namedValues['DOB']);
stmt.setString(3, event.namedValues['Last4SSN']);

stmt.execute();

}

Locating the subname: Locating the subname

Read more about the Event parameter here:

https://developers.google.com/apps-script/guides/triggers/events

Read more about the connection string here:

https://developers.google.com/apps-script/reference/jdbc/jdbc#getCloudSqlConnection(String,String,String)

Also be sure you've allowed network access to google scripts

https://developers.google.com/apps-script/guides/jdbc?hl=en#using_jdbcgetconnectionurl