How to Import data from MS SQL Server to Google Sheets using Google Apps Script

with detailed code explanation

Photo created by the author with the icons by Freepik, Pixel perfect and phatplus on Flaticon

There are many ways to push data from SQL Server to Google Sheets, including using Python or Google Apps Script. While Python is great for a heavy SQL query, I find it a bit of a hassle when it comes to scheduling. Meanwhile, Google Apps Script works perfectly well for middle-size SQL queries and the triggers make it very simple to schedule, there is also no need to use any API.

In this article, I will share with you how to push data from MS SQL Server to Google Sheets using Google Apps Script and the tips I learned when implementing the solution.

The steps to import data from MS SQL Server to Google Sheets include:

1. Create a user that has access to the SQL Server (optional)
2. Whitelist Google IP Address
3. Create a function to read the data from SQL
4. Create a function to push data to Google Sheets
5. Put them all together

1. Create a user that has access to the SQL Server

Suppose you have been working with your SQL Server already, this is not a mandatory step. You can use your own account as well.

However, when connecting to SQL Server using Google Apps Script, you need to explicitly expose your username and password which is not optimal for using your regular account. It is thus recommended that you create a SQL user specifically for Google Apps Script and this user can only have read-access to a set of tables.

If you have an Infrastructure team who manage the access to different IT tools in your company, they could help you with this task.

2. Whitelist Google IP Address

Only after the IP addresses are whitelisted can the Google JDBC service create a database connection.

In practice, that means you need to whitelist this list of IPs (this is the official doc that Google refers to from the JDBC document). This can also be done by the Infrastructure team.

3. Function to Read data from SQL

First, you open a blank SpreadSheet, then open Tools > Script Editor. You can paste the following code into your Script Editor.

This function implements the following logic:

• Connect to SQL server
• Query the database
• Save the result of the SQL query as a multi-dimensional array

Function input:

• The name of the SQL database you want to connect to
• The SQL query to retrieve your data

Function output:

A multi-dimensional array with:

• the array length = number of rows returned by the SQL query, and
• the length of each array element = number of columns returned by the SQL query

My tips:

• Many tutorials tell you to put your server name to the server variable above. If you tried to do that and got this error,

Exception: Unexpected error while getting the method or property getConnection on object Jdbc.

try with your database public IP address (which can be found in Azure Portal) instead.

• The JDBC service lets you connect to MySQL and Oracle databases as well, read more about it here.

4. Function to Push data to Google Sheets

The following function can be used to append any multi-dimensional array to the Google Sheets.

• It first finds the last row with data of the sheet
• Then, at the bottom of the existing data, get a sheet range that is equal to the size of the array
• Then set the values of that range to be equal to the values of the array
• Finally, it removes any duplicates if exist.

Function input:

• The data you want to push. This data should be a multi-dimensional array
• The name of the sheet you want to push the data to

Function output:

• None, the data gets pushed after the function is executed.

My tips:

• If you want to replace all the existing data instead of appending to them, clear the content of the sheet before adding the data. You do it by adding this line above line 7.

sheet.clearContents();

• If you want to clear only a part of the existing content, e.g clear everything but the column headers, use:

sheet.getRange('A2:H').clearContent();

• If you have a one-dimension array instead, modify line 7 to:

sheet.getRange(lastRow+1, 1, 1, data.length).setValues(data)

(Here you basically get a range that has only 1 row and number of columns = the length of the array)

5. Putting it all together

Now you just need a function to call the readData function and pass its result to the pushDataToGoogleSheet function.

The whole code will look like this:

My tips:

• If you have multiple databases that you want to query for the data, you can get a list of databases within the same connection, then call the functions for every database that meets your criteria.

• If you have a long SQL query, put it on multiple lines and connect the lines by using concatenation (“+” symbol) OR use an online SQL minifier to remove all the line breaks.
• Use double quote “ ” to open and close the SQL query, as SQL use single quote, this will help you avoid unnecessary trouble.
• The SQL query cannot be longer than 250 characters. If you have a big query, it would be better to use Python to push the data instead.
• If you have a timeout problem with the query, disable Chrome V8 runtime by going to Project Settings > Uncheck Enable Chrome V8 runtime
• If you want to monitor your data regularly, go to Triggers (the clock symbol on the left-hand side of your Script Editor), choose the function to be scheduled as “main”, the event source as “Time-driven” and the time interval as you wish.

Conclusion

Google Sheets is a great online tool to do simple visualization and create shared reports, while MS SQL Server is great for storing data. A combination of Google Sheets and MS SQL Server would be very powerful and will enable you to do many analyses, especially when you want to monitor your data regularly.

I hope this article provides you with useful insights into how you can import data from SQL Server to Google Sheets. The above functions can also be used in different ways according to your needs. If you have any questions or suggestions, please feel free to let me know in the comment :D

Thank you for reading ❤