Blog

Google Sheets Script to Automatically Retrieve SQL Data

February 14, 2020

Is this tutorial worth your time?

  • WHAT YOU’LL LEARN: Everything you need to know (and nothing you don’t) on how to automatically retrieve data from my SQL database to Google Sheets with a script - with the actual code to make it work (here you’ll see an example with MySQL but you can use the same process for Microsoft SQL Server and PostgreSQL).🤓
  • YOUR TAKE-AWAY: Save hours of doing the exact same process over and over again.⌚
  • WHO IS THIS TUTORIAL FOR? Anyone spending way too much time copy-pasting data from a SQL database into Google Sheets.🤖


How to Automatically Retrieve Data from a SQL Database to Google Sheets with a Script?

There are 2 main steps:

  1. Create a Google Sheets script to import a SQL table
  2. Run a Google Sheets script automatically every minute to retrieve SQL data

Let me show you the steps!

Create a Google Sheets script to import a SQL table (here with MySQL)

If you want to connect your Google spreadsheet to MySQL, here I’ll share with you the code to read data from your SQL database (here with MySQL) and import the whole table in Google Sheets.


1. Create a new Google Sheets by typing sheet.new in your browser toolbar

2. Name your spreadsheet by doubling clicking on the default name Untitled spreadsheet on the top left hand corner and replacing it with the name you want. For instance, I chose to name it Test here.

3. Click on Tools > Script editor, this will open a new tab with a url starting with https://script.google.com/.

4. Name your script by clicking on the default name Untitled project on the top left hand corner. A little window will pop up where you can write the name you choose. For instance, I chose to name it like my spreadsheet: Test. Then, click on the OK button.

5. Apps Script can connect to external databases through the JDBC service. It supports Google Cloud SQL, MySQL, Microsoft SQL Server, and Oracle databases. To update an external database with JDBC, your script must open a connection to the database and then make changes by sending SQL statements. In order to create a database connection using the JDBC service you must whitelist certain IP ranges in your database settings to allow Apps Script to access it. These are the address ranges you'll need to whitelist:

  • ‍64.18.0.0 - 64.18.15.255
  • 64.233.160.0 - 64.233.191.255
  • 66.102.0.0 - 66.102.15.255
  • 66.249.80.0 - 66.249.95.255
  • 72.14.192.0 - 72.14.255.255
  • 74.125.0.0 - 74.125.255.255
  • 173.194.0.0 - 173.194.255.255
  • 207.126.144.0 - 207.126.159.255
  • 209.85.128.0 - 209.85.255.255
  • 216.239.32.0 - 216.239.63.255

6. In the code area, you’ll see some code already written:

function myFunction() {
}

7. Delete this code.

8. Copy and paste the code below in the code area:

var server = '11.11.11.11';
var port = 3306;
var dbName = 'dummy';
var username = 'username';
var password = 'password';
var url = 'jdbc:mysql://'+server+':'+port+'/'+dbName;

function readData() {
  var conn = Jdbc.getConnection(url, username, password);
  var stmt = conn.createStatement();
  var results = stmt.executeQuery('SELECT * FROM dashboard_dummy');
  var metaData=results.getMetaData();
  var numCols = metaData.getColumnCount();
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('Sheet1');
  sheet.clearContents();
  var arr=[];

  for (var col = 0; col < numCols; col++) {
    arr.push(metaData.getColumnName(col + 1));
  }

  sheet.appendRow(arr);

while (results.next()) {
  arr=[];
  for (var col = 0; col < numCols; col++) {
    arr.push(results.getString(col + 1));
  }
  sheet.appendRow(arr);
}

results.close();
stmt.close();
sheet.autoResizeColumns(1, numCols+1);
}



9. Replace the following information with your own in the code:

  • var server = '11.11.11.11'; // Replace 11.11.11.11 with your IP address
  • var port = 3306; // Replace 3306 with your port number. If you use a MySQL database, chances are that it’s the same, since port 3306 is the default port for the MySQL Protocol (port), which is used by the mysql client and MySQL Connectors.
  • var dbName = 'dummy'; // Replace dummy with your database name
  • var username = 'username'; // Replace username with your user name
  • var password = 'password'; // Replace password with your password
  • var results = stmt.executeQuery('SELECT * FROM dashboard_dummy'); // Replace dashboard_dummy with your table name
  • var sheet = spreadsheet.getSheetByName("Sheet1"); // Replace Sheet1 with your Google Sheets tab name

10. Click on the Run button (the arrow pointing to the bug on its right). Or, in the menu, on Run > Run function > readData.

11. A little window will pop up entitled Authorization required. Click on the Review Permissions button.

12. You might need to click in order to choose the Google account you are using to run your script. Click on the Allow button.

13. On top of your script, you’ll see a little message stating Running function readData… (be patient, it might take a while)

14. When the message disappears, go back to your sheet and… VOILA! You’ll have retrieved your SQL data in your spreadsheet :)

How do you feel right now? On my end, I felt so much joy when I managed to retrieve the data from my database! #proud


Run a Google Sheets script automatically every minute to retrieve SQL data (here with MySQL)

Now I’ll show you the code to fetch and refresh your data every minute. Each time, it clears the existing content in the chosen sheet and replace it with the database data while keeping the format you gave to the table in Google Sheets.


1. Just add this piece of code at the end of your current code:

ScriptApp.newTrigger('readData')

  .timeBased()

  .everyMinutes(1)

  .create();


2. Click on the Run button (the arrow pointing to the bug on its right). If a little window will pop up entitled Authorization required, allow it again.


That’s it! Now, you will import and automatically sync your data between your SQL database and Google Sheets. Yes, so simple and yet so powerful…


google spreadsheet to mysql ; google sheets mysql ; connect google sheets to mysql ; google sheet connect to mysql ; google sheets to mysql ; connect mysql to google sheets ; google sheets connect to mysql ; google sheet mysql ; mysql google sheets ; google sheets mysql connector ; google sheets mysql connection ; google spreadsheet mysql ; sync google sheets with mysql ; google sheet to mysql ; connect google sheet to mysql ; connect google spreadsheet to mysql server


Share this article

 

Oh, and if you think this whole thing is a painful process, feel free to check out our product Actiondesk, it's a spreadsheet software that lets you import and refresh data seamlessly from SQL, Stripe, Salesforce, Hubspot and more.

👇 Request for early access to Actiondesk 👇