"Failure to submit to convention precedes success due to innovation"

Code Snippet (Write to MySQL with Google Apps Script)

Posted by Jeremy D. Stakich on January 14, 2016

The following describes how to write to MySQL with Google Apps Script.

This is an example of using an Apps Script that writes questions and responses to a MySQL database.

Step-by-step guide

1. Click Tools.

2. Click Script Editor...

3. Paste the following code in.

4. Also Note that when keying your questions on the forms , be sure not to include special characters such as “ ' ”.

5. Remember to call a function you must have a trigger.

6. To assign a trigger....click Resources , click Current Project Triggers.

function WriteToDb(e) { //************************* Application variables to change ********************************** var Survey_Name = "Google Quiz"; var EmailAddressOf_Maint = "jeremystakich2@gmail.com"; //********************************************************************************************* var form1 = FormApp.getActiveForm(); var GetResponses = e.response; //************************* Reads the first ten responses on the form ************************* var Email = GetResponses.getItemResponses()[0].getResponse(); var Answer_One = GetResponses.getItemResponses()[1].getResponse(); var Answer_Two = GetResponses.getItemResponses()[2].getResponse(); var Answer_Three = GetResponses.getItemResponses()[3].getResponse(); var Answer_Four = GetResponses.getItemResponses()[4].getResponse(); var Answer_Five = GetResponses.getItemResponses()[5].getResponse(); var Answer_Six = GetResponses.getItemResponses()[6].getResponse(); var Answer_Seven = GetResponses.getItemResponses()[7].getResponse(); var Answer_Eight = GetResponses.getItemResponses()[8].getResponse(); var Answer_Nine = GetResponses.getItemResponses()[9].getResponse(); var Answer_Ten = GetResponses.getItemResponses()[10].getResponse(); //********************************************************************************************* //************************* Reads the first ten questions on the form ************************* var Question_One = GetResponses.getItemResponses()[1].getItem().getTitle(); var Question_Two = GetResponses.getItemResponses()[2].getItem().getTitle(); var Question_Three = GetResponses.getItemResponses()[3].getItem().getTitle(); var Question_Four = GetResponses.getItemResponses()[4].getItem().getTitle(); var Question_Five = GetResponses.getItemResponses()[5].getItem().getTitle(); var Question_Six = GetResponses.getItemResponses()[6].getItem().getTitle(); var Question_Seven = GetResponses.getItemResponses()[7].getItem().getTitle(); var Question_Eight = GetResponses.getItemResponses()[8].getItem().getTitle(); var Question_Nine = GetResponses.getItemResponses()[9].getItem().getTitle(); var Question_Ten = GetResponses.getItemResponses()[10].getItem().getTitle(); //********************************************************************************************* //************************* Build Insert Statement ************************* var sql = "INSERT INTO SurveyQuestions (Survey_Name, Email, Question_One, Answer_One, Question_Two, Answer_Two, Question_Three, Answer_Three, Question_Four, Answer_Four, Question_Five, Answer_Five, Question_Six, Answer_Six, Question_Seven, Answer_Seven, Question_Eight, Answer_Eight, Question_Nine, Answer_Nine, Question_Ten, Answer_Ten) VALUES (" sql = sql + "'" sql = sql + Survey_Name sql = sql + "'," sql = sql + "'" sql = sql + Email sql = sql + "'," sql = sql + "'" sql = sql + Question_One sql = sql + "'," sql = sql + "'" sql = sql + Answer_One sql = sql + "'," sql = sql + "'" sql = sql + Question_Two sql = sql + "'," sql = sql + "'" sql = sql + Answer_Two sql = sql + "'," sql = sql + "'" sql = sql + Question_Three sql = sql + "'," sql = sql + "'" sql = sql + Answer_Three sql = sql + "'," sql = sql + "'" sql = sql + Question_Four sql = sql + "'," sql = sql + "'" sql = sql + Answer_Four sql = sql + "'," sql = sql + "'" sql = sql + Question_Five sql = sql + "'," sql = sql + "'" sql = sql + Answer_Five sql = sql + "'," sql = sql + "'" sql = sql + Question_Six sql = sql + "'," sql = sql + "'" sql = sql + Answer_Six sql = sql + "'," sql = sql + "'" sql = sql + Question_Seven sql = sql + "'," sql = sql + "'" sql = sql + Answer_Seven sql = sql + "'," sql = sql + "'" sql = sql + Question_Eight sql = sql + "'," sql = sql + "'" sql = sql + Answer_Eight sql = sql + "'," sql = sql + "'" sql = sql + Question_Nine sql = sql + "'," sql = sql + "'" sql = sql + Answer_Nine sql = sql + "'," sql = sql + "'" sql = sql + Question_Ten sql = sql + "'," sql = sql + "'" sql = sql + Answer_Ten sql = sql + "')" //*************************************************************************** //************************* Connect to Database and Execute Statement ************************* var conn = Jdbc.getConnection("jdbc:mysql://sql3.freemysqlhosting.net/sql373898", "sql373898", "gF3!bF1!"); var stmt = conn.createStatement(); var count = stmt.executeUpdate(sql,1) stmt.close(); conn.close(); //********************************************************************************************* }

Thats all there is to it.

Photographs and ScreenShots by Jeremy Stakich.