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.
A new tab or window will load, showing the Google Script Editor.
Delete the default code loaded in the Code.gs. 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.
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):
ScriptApp.newTrigger('ImportJSONBasicAuth')
.timeBased()
.everyMinutes(1)
.create();
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.
Recommended articles
More resources
