Setting up Python to Connect to Google Sheets

#twiliohackathon #python #api #learning

Happy Thursday! I hope you are all doing well. It has been exciting to continue working with Twilio and Google sheets on this project. This week I will focus on the issues and learnings I had with using Google's APIs for the first time.

Quick Recap

At the moment, I spend time logging migraines for the doctors office but don't remember the format she wanted, or the details she found important. This application is being created as a way to request a survey for a migraine through SMS, quickly fill out the details, and log the data for later use at the doctors appointment.

Link to Code

To clone or view the code, please visit my GitHub project here. This project is a current work in progress and is not fully functional at this time.

Issues and Learnings

API Errors due to Scope

The first issue I ran into when trying to implement code to interact with Google Sheets through API was related to scope issues. I first assumed I had setup the configuration file wrong but later realized it was due to version changes on the API and referencing older tutorials. The issue I was seeing was as follows:

gspread.exceptions.APIError: {'errors': [{'domain': 'global', 'reason': 'insufficientPermissions', 'message': 'Insufficient Permission: Request had insufficient authentication scopes.'}], 'code': 403, 'message': 'Insufficient Permission: Request had insufficient authentication scopes.'}

After spending time researching this error, I came across a post that showed the scopes needed were the two shown below:

# Create a spread client authorizing it using those credentials.
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

What did I learn here? The reason this issue was occurring was due to an API update. gspread was upgraded and is based on API v4. The change allowed the API to be faster than previous versions but also required scopes to be updated in order to work properly.

Path Issues with PyTest

The next issue I ran across was when working with PyTest. The constructor of the class I setup to work with spreadsheets requires two inputs (1) filename of the JSON containing the credentials for the API which includes the path in the input, and (2) the name of the sheet to connect to.

When working with this constructor in the executor.py file, I provided the path seen below, which worked as expected. (Note: This code will later move to the app.py file when the Twilio API is introduced.)

sheet1 = spreadsheet('../client_secret.json', "migraine_tracker")

Utilizing the same line, I placed the code into a test file and came back with the warning WARNING: Failed to generate report: No data to report. meaning that no data could be read.

After seeing this, I installed pytest-cov to analyze the test coverage and see what lines were being hit and what lines were being missed. At this point, I realized the test coverage was stopping at an important line in the constructor:

credentials = ServiceAccountCredentials.from_json_keyfile_name(json, scope)

Therefore, the credentials were not being created due to this line failing and the sheet was not able to be called.

When looking back at the test for the line that provides the JSON file, I worked to determine why the path was not working correctly. To look further at the path issue, I created a small test that would assert if the path existed. I ran this test with both paths ../client_secret.json and client_secret.json, at which point I found out the path needed to be the latter to work properly in the test file.

import os.path
import pytest
import pprint

def test_path():
    """
    Determine if the path specified is correct.
    """
    assert path.exists('client_secret.json') # Produces True and pass 
    assert path.exists('../client_secret.json') # Produces False and fail

Therefore, I changed the input into the constructor which in turn fixed my issue and made it so I could run my tests successfully.

sheet1 = spreadsheet('client_secret.json', "migraine_tracker")

What did I learn here? Check the root directory. Pytest was running from rootdir: /../../../twilio_application and not the folder I was expecting it to run from. Seems like a trivial mistake when looking back at it, but it is something that can easily be overlooked.

Development Stack

So far, I have worked on the code needed to interact with Google Sheets API...

Stay tuned for next weeks post where I discuss how I utilize the Twilio API to develop a Python Flask app that generates a survey for migraines. I look forward to sharing with you the issues and learnings I have encountered while learning to use Flask!