Search API Connector Documentation

Print

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

BEFORE YOU BEGIN

Click here to install the API Connector add-on from the Google Marketplace.

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
Previous Import Trello Data to Google Sheets
Next Import Vimeo Data to Google Sheets

12 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 an 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
  2. Hi, Thanks for all you did.
    Metrics aren’t enough, I would like :
    – number of engagements
    – number of impressions
    – number of retweet
    – Number of clicks
    These data are just for Twitter Ads?
    Is it possible to get these data?

    Reply
    • Hi Maxime, thanks for the message. I’m not really familiar with that part of Twitter’s API but I just checked their docs and it looks like engagement metrics are limited to people with an enterprise account: (“To use this API, you must first set up an account with our enterprise sales team.”)

      And I suppose if you have an enterprise account you probably have better tools than Google Sheets to access their API…
      Sorry, but I hope that helps clarify.

      As for your question about Twitter ads, the above article is for getting regular Twitter data, it’s not for Twitter ad data. I looked into the Twitter Ads API before and it was using OAuth1, which API Connector doesn’t support. They seem to be upgrading to OAuth2 so I’ll write up some guides once that’s available.

      Reply
  3. Hi guys,

    At Step #3 I get that message :

    Completed with errors
    – Server responded with an error (403) {“errors”:[{“code”:99,”message”:”Unable to verify your credentials”,”label”:”authenticity_token_error”}]}

    Any clue?

    Reply
  4. Hello,

    Thanks for your detailed tutorial. However, after doing all steps, I received below error. Where shall I check?

    Thanks

    Status
    Completed with errors
    – Server responded with an error (400) show response{“errors”:[{“code”:215,”message”:”Bad Authentication data.”}]}

    Reply
    • Twitter’s troubleshooting documentation says this error “Corresponds with HTTP 400. The method requires authentication but it was not presented or was wholly invalid.” So it sounds like you need to doublecheck your access token and make sure you correctly encoded your api key and secret key.

      Reply

Leave a Comment