Import Twitter Data to Google Sheets

As an extremely well-known micro-blogging platform, Twitter needs no introduction. Their API holds all kinds of interesting meta data about users and the contents of their tweets, but is quite complicated to navigate or even get started with. In this article, I will attempt to simplify the process of using the Twitter API, so you can follow along step by step without getting lost in their documentation. We’ll pull Twitter data into Sheets using the API Connector add-on for Sheets. For this tutorial, we will only be pulling public Twitter data, not data that you have to be logged in to view or manage.

CONTENTS

STEP 1: GET A TWITTER DEVELOPER ACCOUNT

  1. As part of Twitter’s effort to reduce spam, malicious usage, and violations of privacy, all requests for access to Twitter’s API must go through an application process.

    To get started, click the “Apply for a developer account” button at https://developer.twitter.com/en/apply-for-access.
    twitter-img1

  2. You’ll be prompted to log in with your Twitter account and password, and then to select information about why you’re using the Twitter API. I’ve selected “Exploring the API”. Click Next.
    twitter-img2
  3. Confirm your account, phone number, email address, and country information. If any of this information is missing, you’ll be asked to provide it at this time. Click Next.
    twitter-img3
  4. You will now be asked to describe how you plan to use the Twitter data and/or APIs. You’ll also be asked the following yes/no questions:
    • Are you planning to analyze Twitter data?
    • Will your app use Tweet Retweet, like, follow, or Direct Message functionality?
    • Do you plan to display Tweets or aggregate data about Twitter content outside of Twitter?
    • Will your product, service, or analysis make Twitter content or derived information available to a government entity?

    It will likely be more difficult to be approved if you intend to use Twitter data for any of the above use cases.
    twitter-img4

  5. Review your answers and click Next.
    twitter-img5
  6. You’ll see a confirmation screen and be prompted to verify your email address.
    twitter-img6
  7. Verify your email address, and wait to be approved. Apparently this may take days or weeks, but for me it was immediate.

STEP 2: CREATE A TWITTER APP

  1. Once your developer account is approved, navigate to https://developer.twitter.com/en/apps and click Create an App.
    twitter-img7
  2. You’ll be asked to provide information about your app. Since this “app” is running through Google Sheets, many of the fields don’t apply, but fill out everything that’s required. You can use any website URL. Leave the callback URL blank. Click Create.
    twitter-img8
  3. You’ll now be directed into a screen containing your API key and API secret key. Copy these down. Congrats, you have your Twitter app and initial credentials.
    twitter-img9

STEP 3: GET YOUR TWITTER API CREDENTIALS

  1. Twitter is highly concerned with security, so getting access requires a few more steps. First, because the Twitter API requires Basic Authentication, we need to encode the API keys to base 64. You can do this by entering your your credentials in the format YOUR_API_KEY:YOUR_API_SECRET_KEY into this form (i.e. your API key, then a colon, and then your API secret key). Your encoded credentials will appear underneath. Copy that down and keep it handy, we’ll need it shortly.

    The encoding script runs in your browser, and none of your credentials are seen or stored by this site.

     

     

    (If you have any problems with the above, please check this post for some alternate methods of encoding your credentials).

  2. Next, we need to exchange this information for a bearer token. Since this requires POSTing information to Twitter, we’ll do this in the API Connector add-on. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
  3. In the Create Request interface, choose POST and enter the following values:
    • API URL path:https://api.twitter.com/oauth2/token
    • Headers: (Note that ‘Basic ENCODED_CREDENTIALS’ refers to the base64-encoded value retrieved just above in step 1)
      AuthorizationBasic ENCODED_CREDENTIALS
    • Post Body:
      {
      "grant_type":"client_credentials"
      }

    All together, it will look like this:
    twitter-img10

  4. Create a new sheet called Access Token. Click Set to set this sheet to receive the output from your request.
  5. Name and save your request. Click Run and you’ll see your Twitter access token printed onto your sheet.twitter-img11

    Congrats, you now have your Access Token!

STEP 4: CREATE A TWITTER API REQUEST URL

We’ll follow the documentation to access information about a user.

  • API root: https://api.twitter.com/1.1
  • Endpoint: /users/show.json
  • Query Strings: ?screen_name=SCREEN_NAME
    Query Strings Example: ?screen_name=jack

Putting it together, we get the full API Request URL:

https://api.twitter.com/1.1/users/show.json?screen_name=jack

STEP 5: PULL TWITTER API DATA INTO SHEETS

We can now enter our values into API Connector and import Twitter API data into Google Sheets.

  1. In the Create Request interface, enter the Request URL we just created.
    twitter-img12
  2. Under Headers, enter Authorization as your Key. For convenience, instead of typing in our bearer token, we’ll reference it in its cell, so enter bearer +++Access Token!B2+++ into the Value.
    Authorizationbearer +++Access Token!B2+++

    twitter-img13

  3. Create a new tab. Here we’ll call it ‘User’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  4. Name your request. Again we’ll call it ‘User’
  5. Click Run and a moment later you’ll see information about your user in your Google Sheet.
    twitter-img14

 

STEP 6: GET MORE TWITTER DATA

 

You can check the documentation for the full list of available API requests,  but if you just want to jump in, you can play around with the URLs you enter in the API URL path field. Try the following (one at a time):

https://api.twitter.com/1.1/friends/list.json?screen_name=jack
https://api.twitter.com/1.1/followers/list.json?screen_name=jack
https://api.twitter.com/1.1/lists/list.json?screen_name=jack
https://api.twitter.com/1.1/statuses/lookup.json?id=20,1050118621198921728
https://api.twitter.com/1.1/trends/place.json?id=1

Leave a Comment