In this article:

How to Export SQL Data to Google Sheets & Update Export

>Click here to Import a MYSQL Database to a Lido Spreadsheet

SQL? You must have heard about it. It’s the magical standard language of databases that you need to learn to be able to get almost any kind of data from them... But, maybe you don’t want to go through the long process of setting up a program to interact with your database quickly and automatically. 

Google Sheets? You must have also heard about it. Who hasn’t? (I already use it yet Google keeps giving me ads about it while I’m watching videos of wildlife on Youtube!) Google Sheets can be more portable than its offline counterparts especially in terms of being able to backup your data.

Knowing about both SQL and Google Sheets, a question may come to mind: Is it possible to combine the best of both worlds? 

And... the answer is: Yes! We can use Google Sheets as our front-end app, where we can communicate to our database using SQL!

For this tutorial, we will learn how to export SQL data to Google Sheets and update the data automatically.

Authorizing Access to the Database

If you want to import database data from your eCommerce or marketing platform, chances are you have to allow access to the database through the settings of your platform. What you need to look for is the option to establish a private API. (You can read about APIs here

Once you do so, you need to secure (1) the URL to the database, (2) the API key, and (3) the password. We will use them to authenticate access to the database.

Importing Data via ImportJSON

Several databases today store data in the form of JSON files, known as JavaScript Object Notation. 

To import the JSON files from the database to Google Sheets, we will use the bradjasper’s ImportJSON, hosted in his Github. You can access the code here. 

To use the code, open your spreadsheet in Google Sheets. Click Tools, and then click Script editor in the drop-down menu.

Google Sheets. Tools option on the main menu selected. Script editor highlighted on the drop-down menu.

A new tab or window will load, showing the Google Script Editor. 

Google script editor. Untitled project. Default blank code loaded

Delete the default code loaded in the Copy the code from the GitHub link and paste it in the empty field. Save the code by clicking on the Save button in the toolbar.

Google script editor. Code from bradjasper’s ImportJSON at GitHub inserted.

Saving the code will make it available to the spreadsheet it is added on. We can now go back to our spreadsheet. On one cell (preferably A1), type the following:

=ImportJSONBasicAuth("<link to="" the="" database="">","<api key="">","<password>")</password></api>

Replace the placeholders in <> with the actual keys from the private API you entered. When you use this, make sure that the link does not have the API key and password parsed with it.

Take note, however, that some JSON databases might have too many columns that Google Sheets may not be able to process, resulting in an error. If this happens, you can use SQL queries to filter the data prior to importing on Google Sheets.

Adding a function to automatically update the Sheets

By default, the sheet will update every time you open it, or you can type the same code in the same place again to update it. If you want it updated every minute, for example, you can add a small snippet at the end of the code (thanks to Actiondesk):





This will refresh the sheet every minute!

In case you want to bail out early on...

This is just the beginning of our tutorials combining the robustness of SQL with the functionality of Google Sheets. We will be doing more in a short while!

But... if you already find this overwhelming or taking you too much time away from making smart moves for your business, consider trying Lido instead. 

With a few clicks on your laptop, you can now access all the relevant metrics without going through the hassle of accessing the SQL databases of your eCommerce platforms and then coding the formulas to process them. Let our platform do it all for you!

Also, special thanks to Brad Jasper and his fellow contributors for hosting and improving ImportJSON! You can access it here: bradjasper/ImportJSON: Import JSON into Google Sheets, this library adds various ImportJSON functions to your spreadsheet.

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
Creates a one- or multiple-day calendar event with optional attendees
Adds an object to Hubspot
Create a Slack channel, and optionally add a topic or members
Makes any HTTP request
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
Makes any HTTP request and returns the response
Inserts given array below defined values in given worksheet
Sends an email using your Google account
Sends an email using your Microsoft Outlook account
Sends a Slack message
Sends an SMS message using your Twilio account
Updates cells with given values
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started