Tutorials / Sending a response from the Twitter API to a spreadsheet in Google Sheets
Sending a response from the Twitter API to a spreadsheet in Google Sheets
When working with the Twitter API, there are numerous reasons you may want to transfer a response payload to Google Sheets. Google sheets are great for basic data analysis and sharing results quickly. This can be particularly useful for ad hoc analysis where you are working with stakeholders from different backgrounds. In a previous blog post, we showed you how to use a Tweet formula to help you work more dynamically with Tweets inside of a Google Sheets workbook. This blog post walks you through how to use Python to make a request to the Twitter API v2 and send the response to a Google Sheets spreadsheet.
Setting up
Before you can use the Twitter API v2, you need a developer account. Once you have an approved developer account, you need to create a Project, and an App inside of that Project. For more information about how to get started with the Twitter API, check out this blog post and the getting started section of our documentation.
For this code sample, you’ll use Python 3. You also need to need to connect to the Google Drive API. If you need any help getting set up be sure to check out this helpful resource.
Importing packages
To get set up, install pandas, gspread, requests, and oauth2client. You’ll use pandas to transform the JSON payload response from the Twitter API to a tabular format, gspread to connect to Google Sheets, requests to connect to make an HTTP request to the Twitter API, and oauth2client to help with authentication using service accounts. You’ll also be importing os, but you do not need to install this first since this is included in the standard
import pandas as pd
import gspread
import os
import requests
from oauth2client.service_account import ServiceAccountCredentials
Setting your environment variable for your bearer token
In order to connect to the Twitter API, you must first authenticate to the API. You can do this with your App’s bearer token. You obtain your bearer token in your App’s keys and tokens section inside of the developer portal. To ensure you don’t accidentally share your bearer token you can set an environment variable. To do this, inside of your terminal run the following and be sure to replace where it says your-bearer-token with your own.
export BEARER_TOKEN='your-bearer-token'
Inside of your code editor, create a function called connect_to_twitter where you can get your bearer token and return a header that you will pass into your HTTP request to the Twitter API.
def connect_to_twitter():
bearer_token = os.environ.get('BEARER_TOKEN')
return {"Authorization": "Bearer {}".format(bearer_token)}
Making a request to the Twitter API
You’ll be making a request to the Tweet lookup endpoint which allows you to get back Tweets that match a given ID. By default, this endpoint will return only the fields for idwhich contains the Tweet ID, and text which returns the text of a Tweet. You can also add additional fields to the object that gets returned. For the example below, you’ll want to specify that you want to include fields for author_id, created_at, and lang in the params variable.
Next, make a function called make_request, which passes in the authentication header you just created. Now, the URL will make a request to include the fields you want returned and the Tweet IDs for the Tweets you are looking to get more information about. Using the requests library, make a GET request to the URL variable, pass in your authentication header, and specify that you want the request to return a JSON payload.
def make_request(headers):
url = "https://api.twitter.com/2/tweets"
params = {
"tweet.fields": "author_id,created_at,lang",
"ids": "21,1293593516040269825,1334542969530183683",
}
return requests.request("GET", url, headers=headers, params=params).json()
Parsing your JSON payload
The response that gets returned will be inside of a JSON payload, that looks similar to the following.
{"data":[{"created_at":"2006-03-21T20:51:43.000Z","author_id":"13","text":"just setting up my twttr","id":"21","lang":"en"},{"created_at":"2020-08-12T17:01:42.000Z","author_id":"2244994945","text":"It’s finally here! \uD83E\uDD41 Say hello to the new #TwitterAPI.\n\nWe’re rebuilding the Twitter API v2 from the ground up to better serve our developer community. And today’s launch is only the beginning.\n\nhttps://t.co/32VrwpGaJw https://t.co/KaFSbjWUA8","id":"1293593516040269825","lang":"en"},{"created_at":"2020-12-03T17:00:13.000Z","author_id":"783214","text":"2020 in one word","id":"1334542969530183683","lang":"en"}]}
The information for the Tweets you requested is in an object called data, but it’s not exactly a format that’s friendly for spreadsheets. To get it ready for rows and columns, create a function called make_df which will turn your JSON payload into a pandas data frame, which is similar to a spreadsheet in structure because they are both tabular in nature. If you are using an expansion or an endpoint such as a recent search that has multiple objects, you may need to normalize your JSON before you are ready to convert your object.
def make_df(response):
return pd.DataFrame(response["data"])
Authenticating to the Google Sheets API
You’ll be using a service account to connect to the Google Sheets API. A service account is an account that belongs to your application in a way that is similar to how a user account would interact with a product. To authenticate to the Google Sheets API, you will first need to set the scope you will be working with, and pass in your service account credential information.
def authenticate_to_google():
scope = [
"https://spreadsheets.google.com/feeds"
]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
"/path/to/your/file.json", scope
)
return credentials
Your service account also has an email address. You will need to update your share settings of the spreadsheet you’d like to modify to include the service account, just like you would with a coworker or a friend by adding that email address to where it says “add people or groups” in share settings of a Google Sheets spreadsheet.
Tying it together
Now, create a main function to call all of the functions we’ve written so far. You’ll want to create a variable called headers which is where you call the connect_to_twitter function to set your authentication header. The variable response is where you call the make_request function and pass in the response variable. In the df variable, create the data frame for the JSON response object you get back from the Twitter API. Next, save a variable called credentials and call the authenticate_to_google function to set your service account credentials. The next step is to create a variable called gc that authorizes your credentials.
The variable workbook is where you set the value of the spreadsheet you are working with, by opening it with your spreadsheet key. Let’s say you have a Google Sheet spreadsheet with the following url.
https://docs.google.com/spreadsheets/d/skosdo;gaetgiwoesofgkd45/edit
The spreadsheet key for this example would be skosdo;gaetgiwoesofgkd45. In the code snippet below you’d want to replace spreadsheet_id with skosdo;gaetgiwoesofgkd45.
You will also want to set the sheet you’re working on, for this example it’s Sheet1 but this can be any sheet you have in your spreadsheet. You’ll also need to specify the cell value or range you want to update, in this example you’ll start with A1 which is the first cell. Finally, to add the values of your data frame to update your spreadsheet with data from the Twitter API.
def main():
headers = connect_to_twitter()
response = make_request(headers)
df = make_df(response)
credentials = authenticate_to_google()
gc = gspread.authorize(credentials)
workbook = gc.open_by_key("spreadsheet_id")
sheet = workbook.worksheet("Sheet1")
sheet.update("A1", [df.columns.values.tolist()] + df.values.tolist())
if __name__ == "__main__":
main()
You can check out the full version of the code on our GitHub. You can expand upon this code sample by using Google Script once you have data from the Twitter API sent to a Google Sheet. Hopefully this blog post can serve as a starting point for working with both Twitter Data and the Google Sheets API. Be sure to let us know on the forums if you run into any troubles along the way or Tweet us at @TwitterDev if this inspires you to create anything.
Ready to build your solution?