Integrating Twilio with Google Sheets

Happy Thursday! I hope you are all doing well. It has been interesting to continue working with Twilio and Google sheets on this project for the hackathon. This week I focus on the issues and learnings I had with using Google Sheets with Twilio.

Quick Recap

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 was created as a way to request a survey for a migraine, quickly fill out the details, and log the data for later use at the doctors appointment.

Link to Code

To clone the code, please visit the GitHub project Migraine Tracking.

Issues and Learnings

After creating functions to work with the Twilio API and Google Sheets, it came time to integrate the two together. This process did not go smoothly at first.

Clearing Results from a List

While running the Flask app, I append the data collected to a list called data_collected and after answering one survey question and starting onto the next, the results would keep appending to the list. This behavior was expected, as, in the end, I wanted to take the answers to all questions and upload to Google sheets. What I did not realize would start to happen was that once a survey ended and the next started, the list continued to append the data.

Having not worked with Flask before, I found this to be an interesting issue that I did not expect to have. I had assumed the data from one survey would be cleared after the survey ended, allowing for the data from the next to start getting added. After realizing this was not the case, I searched on different ways to clear a list of data.

The first thing I had tried was to assign data_collected an empty list after the data was inserted into Google Sheets.

response = MessagingResponse()
sheet.insert_data(data_collected) # Put data into Google sheet
response.message("Thank you for filling out todays migraine... Feel better soon! :)")
data_collected = [] 

Doing so gave me the following error:

UnboundLocalError: local variable 'data_collected' referenced before assignment

After looking into this issue further, I found other ways to clear a list in Python that allowed me to remove old survey data once it had been stored in Google Sheets. The clear() method can be used with lists to remove all the elements from a list.

data_collected.clear() # Clear the list after it has been saved

Utilizing this method worked as expected and allowed the data to be removed after a survey has completed.

What did I learn here? The variable data_collected would continue to collect data as new surveys came in until the variable data was cleared. Using clear() on a list allows you to remove the data from the list and start fresh, meaning each survey would only collect the data needed for that survey, store it in Google Sheets, and clear the data before beginning the next survey.

Data Import has Single Quotes Appended to Numbers / Dates

The other main issue I had when integrating the two pieces was an interesting issue. When inserting data into Google Sheets, the numbers would be appended with one single quote.

For example:

'2415434
'3.0834536
'1

I found this problem to be interesting due to the fact that when I originally worked with the Google Sheets API and was able to pass data using the functions I made, I did not see this problem surface with any data that was passed.

With this, I spent more time looking into how data was being passed to a sheet and what could cause the extra character to be appended to the beginning of a number. When using Google Sheet's API there is an option to specify the value_input_option.

Looking into this option, I found that this issue could be solved by changing the function I was using to enter data into a sheet. I was utilizing insert_row() to add data into the sheet but the other option append_row() allowed for the insertion of the of the value_input_option.

    def insert_data(self, data: list):
        """
        Insert data provided into the sheet.

        :param data: List of data elements to insert into the sheet.
        :param index: Index to insert data into. Defaults to index 2, assuming the first index has headers.
        """
        try:
            self.sheet.append_row(data, value_input_option='USER_ENTERED')
        except:
            print('Cannot append data to sheet.')

After making the quick switch to the new function, I read further into what value to pass with this parameter. When looking more at the documentation for this option, I selected USER_ENTERED as it allows for the data to be parsed as if it were typed into a sheet, meaning numbers will stay as numbers and strings will be interpreted as either string, numbers, dates, etc.

What did I learn here? The issue of a single quote appending to the front of numbers was not coming from the code I had developed to collect the data but the way in which I was utilizing the functions(s) to append new data into the sheet. A simple switch to append_row() allowed the data to be considered as USER_ENTERED meaning when entering numbers or dates, that they would be interpreted as such in the sheet when uploaded.

Development Stack

Thank you for following along! We have all the pieces together to be able to collect data with a SMS survey using Twilio and append the data into a Google Sheet.

Stay tuned for next weeks post where I discuss the application as a whole and how I tied all the pieces together. I look forward to sharing with you the final issues and learnings I have encountered while participating in this hackathon!

Additional Resources

Reference Links

Twilio's Rest API
Automated Survey - Python Flask App
GitHub Sample Automated Survey
Google Sheets and Python Example
Accessing Google Spreadsheets with Python
Clearing Lists
Google Sheets Value Input Option