How to Access MySQL Database from Google Apps Script with Google Apps Script

Google Apps Script is a powerful tool that allows you to automate tasks and create custom applications using JavaScript. One of the most common use cases for Google Apps Script is to access external data sources, such as MySQL databases. In this tutorial, we will show you how to access a MySQL database from Google Apps Script using the JDBC service.

Step 1: Set up the MySQL Database

Before we can access the MySQL database from Google Apps Script, we need to set up the database. If you already have a MySQL database set up, you can skip this step.

To set up a MySQL database, you will need to have access to a MySQL server. You can either install MySQL on your local machine or use a cloud-based MySQL service, such as Amazon RDS or Google Cloud SQL.

Once you have access to a MySQL server, you can create a new database and table. Here is an example SQL script that creates a new database called “mydatabase” and a new table called “mytable”:

CREATE DATABASE mydatabase;
USE mydatabase;
CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL,
  PRIMARY KEY (id)
);

Step 2: Enable the JDBC Service in Google Apps Script

To access the MySQL database from Google Apps Script, we need to enable the JDBC service. Here’s how to do it:

1. Open your Google Apps Script project.
2. Click on the “Resources” menu and select “Advanced Google services”.
3. Scroll down to “JDBC” and click on the toggle switch to enable it.
4. Click on the “Google Cloud Platform API Dashboard” link.
5. In the API Dashboard, search for “JDBC” and click on the “JDBC Socket Factory API” result.
6. Click on the “Enable” button to enable the API.

Step 3: Connect to the MySQL Database

Now that we have enabled the JDBC service, we can connect to the MySQL database from Google Apps Script. Here’s an example script that connects to the “mydatabase” database and retrieves all the records from the “mytable” table:

function getRecordsFromMySQL() {
  var conn = Jdbc.getConnection("jdbc:mysql://localhost:3306/mydatabase", "username", "password");
  var stmt = conn.createStatement();
  var results = stmt.executeQuery("SELECT * FROM mytable");
  var records = [];
  while (results.next()) {
    var record = {
      id: results.getInt("id"),
      name: results.getString("name"),
      email: results.getString("email")
    };
    records.push(record);
  }
  results.close();
  stmt.close();
  conn.close();
  return records;
}

In this script, we first create a connection to the MySQL database using the Jdbc.getConnection() method. We pass in the JDBC URL, username, and password as parameters.

Next, we create a statement object using the connection.createStatement() method. We then execute a SELECT query to retrieve all the records from the “mytable” table.

We loop through the results using the results.next() method and create a JavaScript object for each record. We then push each object into an array.

Finally, we close the result set, statement, and connection objects and return the array of records.

Step 4: Use the MySQL Data in Google Apps Script

Now that we have retrieved the data from the MySQL database, we can use it in our Google Apps Script project. Here’s an example script that logs the records to the console:

function logRecordsFromMySQL() {
  var records = getRecordsFromMySQL();
  for (var i = 0; i < records.length; i++) {
    var record = records[i];
    Logger.log("Record #" + record.id + ": " + record.name + " (" + record.email + ")");
  }
}

In this script, we first call the getRecordsFromMySQL() function to retrieve the records from the MySQL database. We then loop through the records and log each record to the console using the Logger.log() method.

Conclusion

In this tutorial, we showed you how to access a MySQL database from Google Apps Script using the JDBC service. We first set up a MySQL database and table, then enabled the JDBC service in Google Apps Script, and finally connected to the MySQL database and retrieved the records. We also showed you how to use the MySQL data in your Google Apps Script project. With this knowledge, you can now access external data sources from your Google Apps Script projects.