Sending a response from the Twitter API to a spreadsheet in Google Sheets
Tags: twitter, showdev, python, beginners
Published: 2020-12-22 22:24:46

---
title: Sending a response from the Twitter API to a spreadsheet in Google Sheets
published: true
description: A tutorial that walks you through to send a payload from the Twitter API to Google Sheets.
tags: twitter, showdev, python, beginners
canonical_url: https://developer.twitter.com/en/docs/tutorials/twitter-api-google-sheets
//cover_image: https://direct_url_to_image.jpg
---
When working with the Twitter API, there are numerous reasons you may want to transfer a response payload to [Google Sheets](https://www.google.com/sheets/about/). 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.

Before you can use the Twitter API v2, you need an [a developer account](https://developer.twitter.com/en/portal/petition/essential/basic-info). Once you have an a 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](https://dev.to/twitterdev/step-by-step-guide-to-making-your-first-request-to-the-new-twitter-api-v2-42mi) and the [getting started](https://developer.twitter.com/en/docs/getting-started) section of our documentation. 

For this code sample, you’ll use [Python 3](https://www.python.org/downloads/). You also need to need to connect to the [Google Drive API](https://developers.google.com/drive). If you need any help getting set up be sure to check out this [helpful resource.](https://levelup.gitconnected.com/python-pandas-google-spreadsheet-476bd6a77f2b)

To get set up, install [pandas](https://pandas.pydata.org/), [gspread](https://gspread.readthedocs.io/en/latest/), [requests](https://requests.readthedocs.io/en/master/), and [oauth2client](https://pypi.org/project/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](https://docs.python.org/3/library/os.html), but you do not need to install this first since this is included in the standard library of Python.

```python
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](https://developer.twitter.com/en/portal/dashboard). 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.

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

```python
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](https://developer.twitter.com/en/docs/twitter-api/tweets/lookup/introduction) endpoint which allows you to get back Tweets that match a given ID. By default, this endpoint will return only the fields for `id ` which contains the Tweet ID, and `text` which returns the text of a Tweet. You can also add additional [fields](https://developer.twitter.com/en/docs/twitter-api/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. 

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

```json
{"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. 

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

```python
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](https://docs.google.com/spreadsheets/d/spreedsheet_id/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. 

```python
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](https://github.com/twitterdev/Twitter-API-to-Google-Sheets/blob/main/twitter_google_sheets.py). You can expand upon this code sample by using [Google Script](https://www.google.com/script/start/) 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](http://twittercommunity.com/) if you run into any troubles along the way or Tweet us at [@TwitterDev](https://twitter.com/TwitterDev) if this inspires you to create anything.