In our previous tutorial, we used a specialized code and set up a private API to access an external database. In this tutorial, we will improve upon the existing code and talk a bit about filtering the SQL data to be imported to Google Sheets.
Why should we filter the SQL data before being imported to Google Sheets? This is to keep us from being overwhelmed with the massive amount of data that we can get from a database. If you followed our previous tutorial, you might find an error where the number of columns is too much for the Google Sheets to handle. This short tutorial contains a good solution to that problem.
Let us go back to our ImportJSON code that we have in our previous tutorial. The URL that we insert in the code can actually support certain commands to the API to filter the data before it is imported to the Google Sheets. As an example, we will access the list of products and their product type in our Shopify store. The URL to this is as follows:
https://your_shopify_store.myshopify.com/admin/api/2020-07/products.json?fields=title,product_type
Here, the products.json points to the portion of the database storing the product offerings and the relevant information about them. ?fields=title,product_type is an additional command to only retrieve the information tagged as title and product_type in the database. Without these additional commands, you will be able to retrieve all the information, even the ones that are probably not relevant to your analysis. To study these commands, you can read the documentation offered by the platform to developers.
If you plug this into the function we designed, it will work as intended.
This simple trick is very useful, especially considering most of the online eCommerce platforms store a huge amount of data and we have to filter them away before we can do any meaningful analysis on them. This process of filtering in Google Sheets will consume a lot of time!
There is a big drawback to this method, however. You have to read pages upon pages of documentation of the platform, which, while available online, will nonetheless consume a lot of time, better spent on mounting a good marketing campaign for the 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!
