Working with Google Sheets API to create an interactive Pen Refill-Finder app — Part 1

Hi There! 👋 In this tutorial I’ll show you how to read data from multiple sheets in a Google Spreadsheet and dump that data to a json file. Then we’ll interpret the file by creating a Pen Refill-Finder app.

I’m sure you’ve already used Google Sheets for various purposes(be it at work or for your own). They indeed have a slick UI and great features but here we’re not going to navigate through their UI but fetch the sample spreadsheet via their API programmatically and use it to create an interactive refill finder app. So let’s get started!! 🙌

First you need to get your API credentials, visit this link and then hit this button:

Get API credentials

Now that you have your credentials.json file let’s view the directory structure on how you should place your files.

Folder Structure

The refill_finder.py is our main file which will make the API request with credentials.json and generate token.pickle file upon successful authentication flow then dump the spreadsheet data into json files namely products.json and brands.json. What I’ve done is modified the sample file quickstart.py to make it work as per our requirements.

Main Python script (refill_finder.py)

We need JSON library to dump the fetched data to json format, so lets import it into our main file:

Import JSON library

Now let’s set our global variables:

Set global variables

Here the variable SPREADSHEET_ID holds the spreadsheet id of our sample spreadsheet. PRODUCTS_SHEET_RANGE_NAME selects the range of values to read from Products sheet skipping the title row from column A to F. Similarly BRANDS_SHEET_RANGE_NAME selects the range of values to read from another sheet i.e. Brands from column A to D and skips the title row. Variable BASE_DIR gets the absolute path of this running script.

Let’s create a function write_file() that accepts two parameters i.e. “data” and “file_name”, which then parses and writes the data to a json file.

This function writes file to json format.

Now let’s work on our main() function: Here in order to make our file paths organized, I’ve replaced the relative paths of credentials.json and token.pickle with absolute paths, in case we want to execute the script from other location with its absolute path or schedule to run via cron (which we will later).

Fix relative paths with absolute paths

Now we’ll read data from the spreadsheet.

Read data from sheets in a spreadsheet

Here variables products_sheet_data and brands_sheet_data fetches data from Products and Brands sheet respectively.

Now we’ll check if both sheets have data and is not empty.

Check if both sheets have data

Now we’ll generate products.json file from Products sheet.

Generates products.json file from Products sheet

What this code snippet does is create a dictionary data with nested { key : value } pairs. Each loop sets the data level-wise based on key and value, then passes the final dictionary data i.e. product_coll and filename ‘products.json’ as parameters to the function write_file() which generates a json file.

Now we’ll write code to generate brands.json file from Brands sheet.

Generates brands.json file from Brands sheet

This code snippet works similarly as the one used to create products.json file. Here, each loop sets the data level-wise based on { key : value } pairs, then passes brand_coll and filename ‘brands.json’ as parameters to function write_file().

Now that we’ve created our script, let’s execute it for the first time on our local machine, so that it can authenticate via credentials.json and generate token.pickle file. But first let’s install our project dependencies:

$ pip3 install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

You can find complete working code from this link: Refill-Finder

Then..

$ python3 refill_finder.py

This command will echo an https url and open a browser window. Sign-in with your gmail credentials and then approve your app consent. Upon successful authentication flow the script will generate a binary file token.pickle and then generate products.json and brands.json on the same path.

We can execute the script manually to pull fresh data from our sample spreadsheet or configure it to run automatically as cronjob. Let’s assume the spreadsheet is maintained daily and we need to update our refill-finder app to show latest data from our webserver. For that, we just need to upload these files to our document root: refill_finder.py, index.html and token.pickle.

So let’s setup a cronjob that runs everyday at 5 pm:

$ crontab -e

Then append the following code at the end of the crontab file. Make sure to replace [path_to_python_executable] and [path_to_refill_finder_script] with actual paths.

Setup cronjob

So that’s it, we have successfully created a script that runs automatically and fetches data from Google Sheets then writes the data as json files.

👉 👉 Follow this link for Part 2…. 👈 👈

If you like the content that I share then don’t forget to give me👏 👏. Any suggestions and comments are very much welcomed. I had fun doing this. Hope I’m able to help. Peace!! ✌️ 😊