Import Twitter Data to Google Sheets

In this guide, we’ll pull Twitter data into Sheets using the API Connector add-on for Sheets. We will only be pulling public Twitter data, not data that you have to be logged in to view or manage. Twitter’s API holds all kinds of interesting metadata about users and the contents of their tweets, but is quite complicated to navigate or even get started with, so I will attempt to simplify the process so you can follow along without getting lost in their documentation.

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 > Start.
  3. In the Create screen, 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

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 finally import Twitter API data into Google Sheets!

  1. Back 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 and click ‘Set current’ to use that tab as your data destination.
  4. Name your request and click Run. 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

2 thoughts on “Import Twitter Data to Google Sheets”

  1. Hello API Connector Team,

    I’d like to know few things about Twitter API integration before deciding to purchase a license:

    1. is it possible to use API Connector for following and unfollowing (friendship create/destroy)
    2. is it possible to use API Connector for sending a direct message (DM create)
    3. is it possible to trigger API Connector from the spreadsheet
    4. is it possible for API Connector to update the spreadsheet once a specific API call was made

    Reply
    • Hi there, in regards to your first two questions, API Connector doesn’t currently offer any specific Twitter API integration. It is just a universal API connection tool that connects to any API, so you’re only limited by what the API permits. This article is about getting public Twitter data, so I’m not totally sure what their API allows in terms of following/ unfollowing / messaging and other private actions.

      As for your other questions, yes, you can trigger API Connector from the spreadsheet, and yes, API Connector will update the spreadsheet with whatever the response is from your API call.

      Reply

Leave a Comment