Search API Connector Documentation

Print

Import Twitter Data to Google Sheets

New: Updated for Twitter’s V2 API.

In this guide, we’ll pull Twitter data into Sheets using the API Connector add-on for Sheets.

In this article, we will only be pulling public Twitter data like tweets, not advertising or analytics data that you have to be logged in to view or manage.

Contents

Before You Begin

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

Part 1: Create a Twitter Developer Account

  1. Navigate to https://developer.twitter.com/ and click Sign up
    twitter-signup
  2. You’ll be asked to provide some basic info about yourself. Click Next.
    twitter-basicinfo
  3. Read and accept the developer agreement
    twitter-developeragreement
  4. You’ll be asked to verify your email address. Click the verification email to finalize your developer account.

Part 2: Create a Twitter App

  1. Once your developer account is approved, you’ll enter the app creation process. You should be taken to this screen automatically from the verification email, but if not, it’s located at https://developer.twitter.com/en/portal/register/welcome.
  2. Give your app a unique name and click Get keys.
    twitter-getkeys
  3. You’ll now be directed into a screen containing your API keys. Copy down your bearer token as we’ll use that next. Congrats, you have your Twitter app and credentials!
    twitter-keys

Part 3: Create a Twitter API Request URL

We’ll first look up the user IDs and metadata for some Twitter accounts. You can substitute in any usernames you like.

  • API root: https://api.twitter.com
  • Endpoint: /2/users/by
  • Query Strings: ?usernames=jack,twitterdev,twitterapi,adsapi&user.fields=created_at&tweet.fields=author_id,created_at

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

https://api.twitter.com/2/users/by?usernames=jack,twitterdev,twitterapi,adsapi&user.fields=created_at&tweet.fields=author_id,created_at

Part 4: Pull Twitter API Data into Sheets

Now let’s copy that request URL into API Connector.

  1. Back in the Create Request interface, enter the Request URL we just created.
    twitter-url
  2. We don’t need OAuth, so just leave that set to None.
  3. Under Headers, enter Key = Authorization, Value = Bearer your_bearer_token.
    twitter-headers
  4. Create a new tab and click Set current to use that tab as your data destination.
  5. Name your request and click Run. A moment later you’ll see information about your users in your Google Sheet.
    twitter-results

Part 5: More Example Request URLs

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.

  • Get tweets from a specific user ID (6253282 in this example)
    https://api.twitter.com/2/users/6253282/tweets?tweet.fields=created_at,author_id&max_results=100 
  • Search tweets for a keyword
    https://api.twitter.com/2/tweets/search/recent?query=cats&max_results=100
  • Get public metrics (retweets, replies, likes) for a specific tweet ID
    https://api.twitter.com/2/tweets/1204084171334832128?tweet.fields=public_metrics&expansions=attachments.media_keys&media.fields=public_metrics

Part 6: API Documentation

Official API documentation: https://developer.twitter.com/en/docs/api-reference-index

Interactive request generator: https://developer.twitter.com/apitools/api

Previous Import Trello Data to Google Sheets
Next Import Udemy Data to Google Sheets

23 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. You can get most of the metrics you’ve listed, but some metrics are only available using Twitter’s OAuth 1.0a User Context authentication (Twitter is going through an API migration, so certain fields are only available in certain versions).

      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
  5. Hello, I would like to add multiple accounts on one command. How do i do that?

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

    would it be
    https://api.twitter.com/1.1/users/show.json?screen_name=jack,screen_name=brenda OR

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

    Reply
  6. Hello API Connector Team,

    First off, thank you so much for the amazing documentation you guys have provided! This has helped me so much.

    I’m working on creating a custom API request that collects Twitter video views by month. Here is my API URL path:

    https://api.twitter.com/2/users/2298681199/tweets?exclude=retweets&expansions=attachments.media_keys&media.fields=public_metrics&max_results=100&start_time=2021-09-01T00:00:00Z&end_time=2021-09-30T00:00:00Z

    and I’m using this JMES Path to filter the view counts:

    {"":includes.media[].public_metrics.view_count}

    The issue I’m having is when I run this over a month that requires pagination (more than 100 tweets) & includes more than four videos in one month, my output values are listed along four consecutive cells (ex. A10:D10) in my Google Sheet, and when a fifth view count value is outputted, it adds that value in the cell above the first value. (ex. A9)

    This makes it difficult for me to collect view counts for just one month when each month can take up a different amount of space in my Sheet depending on the amount of video content I tweeted that month. I’d prefer for all view counts from the month to be in the same column or row.

    Hope that all makes sense! Any suggestions on how to fix this?

    Reply
    • Hey Henry, I tried to run this but couldn’t replicate it. I suspect you can resolve this by changing the report style or your JMESPath query (or by making a second sheet that pulls in just the data you need), but can’t really say without seeing it. Let me contact you via email to discuss.

      Reply
    • Twitter Ads still uses the old OAuth 1.0a standard, so we need to set up something custom for that. Should be ready within the next couple of months!

      Reply

Leave a Comment

Table of Contents