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:
Now that you have your credentials.json file let’s view the directory structure on how you should place your files.
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:
Now let’s set our 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.
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).
Now we’ll read data from the 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.
Now we’ll generate 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.
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.
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!! ✌️ 😊