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.

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: Get a Twitter Access Token

  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.
  5. You should be taken to this screen automatically from the verification email, but if not, go to https://developer.twitter.com/en/portal/register/welcome.
  6. You’ll be asked to create and name an app. Give your app a unique name and click Get keys.
    twitter-getkeys
  7. You’ll now be directed into a screen containing your API keys. Copy down the value listed under Bearer Token. Congrats, you have your Twitter credentials!
    twitter-keys

Part 2: Pull Data from Twitter to Sheets

The easiest way to get started with the Twitter API is through API Connector’s built-in integration.

  1. Select Twitter from the drop-down list of applications
    twitter-application
  2. Under Authorization, enter the bearer token from above with the word Bearer in front.
    twitter-authorization
  3. Select an endpoint. We’ll start with the /users/by endpoint, which returns information about a Twitter user
    twitter-endpoints
  4. Fill out the usernames parameter with the Twitter users you’d like to look up. Optionally use the user.fields parameter to fetch additional data points.
  5. Set a destination sheet, name your request, and click Run to see response data in your sheet.
    twitter-response
  6. Pay attention to the value in the data.id field. That contains the ID for each Twitter user, which you can plug into other requests.

Part 3: Create a Custom API Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the endpoints and parameters shown in Twitter’s API documentation. To create a custom request, add the complete URL into the request URL field and include a header of Key = Authorization, Value = Bearer your tokentwitter-results

To easily convert from a preset request to a custom API URL, tick the Add request URL box before running your preset request (under Output options). This will print out your complete API URL, which you can then copy/paste into the request URL field.

Part 4: 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

22 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, you’re only limited by what the API permits. However, 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, 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
  5. 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
  6. Hi Ana,

    I’m new to APIs and am trying to automate some of my data collection. My goal is to have a daily triggers that imports new tweets from our social media team. It seems this can be done with since_id; however I’m not sure how to set this up so that the newest id is used . To complicate things a bit more, there is no consistency in data output – in one run, data_text is in Column A and then in the next run, it’s in Column D. Can you point me in the right direction.

    Thanks in advance!

    Reply
    • My goal is to have a daily triggers that imports new tweets from our social media team.
      You can do this using a custom request with dynamic start/end times, e.g.
      https://api.twitter.com/2/users/44196397/tweets?tweet.fields=author_id,created_at,id,public_metrics,text&start_time=2022-08-07T00:00:00Z&end_time=2022-08-14T00:01:00Z&max_results=100 Where you see start_time and end_time parameters, substitute in date references from your sheet like =today()-1. You’ll need to use Twitter’s required date format, so after entering a date, convert it like this =text(A1,"yyyy-mm-ddT00:00:00Z"), then reference THAT cell.
      in one run, data_text is in Column A and then in the next run, it’s in Column D
      If you click Edit Fields before running your request, you’ll open the visual field editor where you can map fields to specific columns and lock them there, such that it doesn’t matter if the API sends back data in a different order.

      Please check if that works for you, or feel free to message support if you’d like more detailed assistance!

      Reply
    • I suggest starting with the integration first to set up the request and get your author IDs. Then convert to a custom request and paste the URLs you need one after the other into the Request URL field, e.g.
      https://api.twitter.com/2/users/44196397/tweets?tweet.fields=author_id,created_at,id,public_metrics,text&end_time=2022-09-01T00:01:00Z&expansions=author_id&max_results=100&start_time=2022-08-26T00:00:00Z
      https://api.twitter.com/2/users/2244994945/tweets?tweet.fields=author_id,created_at,id,public_metrics,text&end_time=2022-09-01T00:01:00Z&expansions=author_id&max_results=100&start_time=2022-08-26T00:00:00Z
      https://api.twitter.com/2/users/11348282/tweets?tweet.fields=author_id,created_at,id,public_metrics,text&end_time=2022-09-01T00:01:00Z&expansions=author_id&max_results=100&start_time=2022-08-26T00:00:00Z

      You can add those URLs into your sheet and reference the cells instead of directly listing them out one by one, but the above is the basic idea.

      Reply

Leave a Comment

Table of Contents