API Connector Documentation

Print

Import Twitter Data to Google Sheets

Last Updated OnJanuary 10, 2024
byAna
< Knowledge Base

In this guide, we'll pull Twitter (aka "X") 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.

On March 30, 2023, Twitter announced that access to public Twitter data will no longer be provided free of charge.

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/ while logged into Twitter and open the Developer Portal
    twitter-devportal
  2. You'll be asked to select a plan (free, basic, pro, or "contact us"). Note that the free plan is write-only and does not allow for fetching any data. You will need a paid plan to pull Twitter data into Sheets.
    twitter-subscribe
  3. Whichever plan you select, provide some information about how you intend to use the API and click Submit.
    twitter-agreement
  4. You'll now be prompted to enter credit card details, and will be returned to the Twitter API dashboard.
  5. In the Twitter API dashboard, click the Keys and Tokens icon
    twitter-dashboard
  6. You'll now be directed into a screen containing your API keys. Generate and copy down the value listed under Bearer Token. That's it, you now have your Twitter API credentials.
    twitter-generatebearer

Note: Attempting to fetch Twitter data using Twitter's free API will result in the following error message: {"client_id":"11111111","detail":"When authenticating requests to the Twitter API v2 endpoints, you must use keys and tokens from a Twitter developer App that is attached to a Project. You can create a project via the developer portal.","registration_url":"https://developer.twitter.com/en/docs/projects/overview","title":"Client Forbidden","required_enrollment":"Appropriate Level of API Access","reason":"client-not-enrolled","type":"https://api.twitter.com/2/problems/client-forbidden"}

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: Get New Tweets Only

If you're fetching Tweets that match a query, you can avoid duplicates by setting your request to fetch new data only. There are a few approaches you could take; here’s one:

  1. Run an initial request to the /tweets/search/recent endpoint
  2. Use the field editor to select just the fields you want and assign them to specific columns in your report.
  3. The maximum (i.e. latest) Tweet ID is located in a column labeled meta.newest_id. Create a new tab called MaxId, and get the largest meta.newest_id with the function =INDEX(Twitter!L:L,MATCH("zzzz",Twitter!L:L)). (We can't use a simple function like MAX because Twitter's ID is 19 digits, and Sheets only recognizes numbers up to 15 digits).
  4. Now reference that maximum ID in the since_id parameter of your request
    twitter-maxidref
  5. This will ensure that each request starts from the end of the prior data pull. Select Append mode to add each new data pull to the end of your existing dataset.
  6. Set your request to run on a schedule. You won’t need to update your request again.

Part 4: 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 the 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 token. Here's an example request setup:

  • Application: Custom
  • Method: GET
  • Request URL: https://api.twitter.com/2/users/by?usernames=jack,twitterdev,twitterapi,adsapi&user.fields=created_at&tweet.fields=author_id,created_at
  • Headers: 
    • Authorization: Bearer your_token

twitter-results

Part 5: API Documentation

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

30 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 currently support.

      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

  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

  7. 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 by making a 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

    • For now, yes, the method in this article still works for free. However, Twitter says that access to basic tweet information will soon cost $100/month.

      Reply

  10. Hi Ana,

    Would it be possible to have my list of followers Data including followers usernames, names, Twitter ID, their tweet count, followers and following count, account creation date, bios, etc. updated in a daily basis or even better live ?

    ps i have more than 300k followers

    Reply

    • Technically yes as you can set a scheduled request to run automatically each day. However, with 300K followers this will be difficult because Google Sheets isn't really equipped to handle very lage amounts of data, plus the Twitter API only sends back 100 records at a time (so you'd have to loop through 3000+ times). In short, I think you'll be better off with a more powerful tool or one specifically designed for tracking Twitter.

      Reply

  11. Hello Ana,
    Today I received error messages in my twitter requests, however, I do not understand the reason for them, the message is as follows:

    {"client_id":"########","detail":"When authenticating requests to the Twitter API v2 endpoints, you must use keys and tokens from a Twitter developer App that is attached to a Project. You can create a project via the developer portal.","registration_url":"https://developer.twitter.com/en/docs/projects/overview","title":"Client Forbidden","required_enrollment":"Appropriate Level of API Access","reason":"client-not-enrolled","type":"https://api.twitter.com/2/problems/client-forbidden"}

    Until yesterday they were working correctly, and I have not made any changes to the requests.
    I hope you can help me, thank you.

    Reply

    • I just tested and am now getting this error as well. It was working the last time I tested a couple days ago. I see people saying the same thing online, so I believe Twitter is phasing out our free "legacy" accounts and to continue accessing data we need to create a new project and start paying for their API. This might be a good time to also check out the unofficial Twitter APIs at https://rapidapi.com/search/twitter.
      Edit: I updated the article to reflect that this is now a paid-only API.

      Reply

Leave a Comment

Jump To