google apps script html service web page

Question

In google apps script using HTML Service how to create a custom web page interface to capture basic user information and load data to a MySQL database. I am doing this in a google sheet using the below code

function getHtml() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      html = HtmlService.createHtmlOutputFromFile('index');
  ss.show(html);
}


function getData(form){
  var firstName = form.firstName,
      lastName = form.lastName,
      dob = form.dob,
      email = form.email,
      phone = form.phone,
      sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.appendRow([firstName, lastName, dob, email, phone]);
}

//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 Record" /> <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>

I have a problem. I don't know how to add records to a mysql table. — ITHelpGuy

iJay iJay · Accepted Answer · 2016-08-27T04:52:56

Before you start, read this document well. You have some step to follow, you need to white-list some range of IP addresses.

// 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;

Now you need to call below writeRecord(...) function from getData(form) function,

function getData(form){
  var firstName = form.firstName,
      lastName = form.lastName,
      dob = form.dob,
      email = form.email,
      phone = form.phone,
      sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.appendRow([firstName, lastName, dob, email, phone]);// this will write data line on your spreadsheet
  writeRecord(firstName, lastName, dob, email, phone)// this is where you call the database insert function
}

This is the DB insert function,

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

  var stmt = conn.prepareStatement('INSERT INTO myTable'
      + '(f-name,l_name,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();
}

google apps script html service web page

1 Answers