CODEWITHSUNDEEP
google-apps-script
ITHelpGuy
ITHelpGuy

Reputation: 1037

save google forms response to a mysql database

I have this html file for the google form and would like to store form response in a mysql database. As of now I am storing my form response to a google sheet and copying this data to a mysql table. Now I want my results stored directly stored in the database.

//index.html

<!DOCTYPE html>
<html>
    <b>Add Record</b><br />

    <form>
    First name: <input id="firstname" name="firstName" type="text" /> <br />

    Last name: <input id="lastname" name="lastName" type="text" /> <br />

    DOB: <input id="dob" name="dob" type="date" /> <br />

    Email: <input id="email" name="email type="text" /> <br />

    Phone: <input id="phone" name="phone" type="text" /> <br />

   <input onclick="formSubmit()" type="button" value="Add Row" /> <br />

   <input onclick="google.script.host.close()" type="button" value="Exit" />
   </form>
  <script type="text/javascript">
        function formSubmit() {
            google.script.run.getValuesFromForm(document.forms[0]);
        }
    </script>
</html>


    function getData(){
   var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz');
 var formResponses = form.getResponses();
 for (var i = 0; i < formResponses.length; i++) {
   var formResponse = formResponses[i];
   var itemResponses = formResponse.getItemResponses();
   for (var j = 0; j < itemResponses.length; j++) {
     var itemResponse = itemResponses[j];
      var firstName = itemResponse.getResponse(),
      lastName = itemResponse.getResponse(),
      dob = itemResponse.getResponse(),
      email = itemResponse.getResponse(),
      phone = itemResponse.getResponse(),
      insertFormData = copyDataToTable(firstName, lastName, dob, email, phone);
}


 function copyDataToTable(firstName, lastName, dob, email, phone) 
 {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);

  var stmt = conn.prepareStatement('INSERT INTO myTable (firstName, lastName, dob, email, phone) values (?, ?, ?, ?, ?)');
  stmt.setString(1, firstName);
  stmt.setString(2, lastName);
  stmt.setString(3, dob);
  stmt.setString(4, email);
  stmt.setString(5, phone);
  stmt.execute();
}

Upvotes: 0

Views: 4930

Answers (1)

Mr.Rebot
Mr.Rebot

Reputation: 6791

This is possible using Google Apps Script's Forms Service and JDBC.

Form Service

This service allows scripts to create, access, and modify Google Forms.

JDBC

Apps Script can connect to external databases through the JDBC service, a wrapper around the standard Java Database Connectivity technology. In Apps Script, the JDBC service supports Google Cloud SQL, MySQL, Microsoft SQL Server, and Oracle databases.

Using Form service you can use the getItemResponses()method to get all item responses.

Gets all item responses contained in this form response, in the same order as the items appear in the form. If the form response does not contain a response for a given TextItem, DateItem, TimeItem, or ParagraphTextItem, the ItemResponse returned for that item will have an empty string as the response.If the form response omits a response for any other item type, this method will exclude that item from its returned array.

Then use those responses as data to be entered in the database.

Sample Code

// Write one row of data to a table.
function writeOneRecord() {
var conn = Jdbc.getConnection(dbUrl, user, userPwd);

var stmt = conn.prepareStatement('INSERT INTO entries '
+ '(guestName, content) values (?, ?)');
stmt.setString(1, 'First Guest');
stmt.setString(2, 'Hello, world');
stmt.execute();
}

Hope it helps!

Upvotes: 2

Related Questions