Integrating Twilio with Google Sheets
Tags: twiliohackathon, twilio, python, flask
Published: 2020-04-24 00:03:29

---
title: Integrating Twilio with Google Sheets
published: True
description: My submission to the Twilio x DEV Hackathon!
series: Tracking Migraines with SMS
tags: twiliohackathon, Twilio, python, flask
---

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](https://github.com/rosejcday/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. 

```python 
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: 

```bash 
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.

```python
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`. 

```python
    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. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/la7zchv71vf9rdwe5em7.png)

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](https://www.twilio.com/docs/usage/api)
[Automated Survey - Python Flask App](https://www.twilio.com/docs/voice/tutorials/automated-survey-python-flask)
[GitHub Sample Automated Survey](https://github.com/TwilioDevEd/automated-survey-flask)
[Google Sheets and Python Example](https://www.youtube.com/watch?v=vISRn5qFrkM&list=PLqrz4nXepkz60FNw4ORm1iLTn_R5o9fBb)
[Accessing Google Spreadsheets with Python](https://towardsdatascience.com/accessing-google-spreadsheet-data-using-python-90a5bc214fd2)
[Clearing Lists](https://www.geeksforgeeks.org/different-ways-to-clear-a-list-in-python/)
[Google Sheets Value Input Option](https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption)