Product News

The Tweet formula for Google Sheets

  By Daniele  28 October  2020



  By Daniele

  28 October 2020



We often see Tweets and screenshots of Tweets posted outside of the Twitter app. And we know a lot of people copy and paste Tweets too. Whether you’re keeping track of your latest favorite meme or taking note of interesting commentary, you may think it’s easy and common enough to simply keep a list of Tweets in a spreadsheet, like Google Sheets. But how can you make sure you selected the entire Tweet? Or what if a Tweet gets deleted? Thanks to the new Twitter API, there is a much better way to get a list of publicly available Tweets in Google Sheets: you can create two lookup formulas to retrieve Tweets and their details, including the username of the author:

      =TWEET(“https://twitter.com/jack/status/20”)

=AUTHOR_OF_TWEET(“https://twitter.com/jack/status/20”)

Copy and paste can get messy at times, and you may end up copying unwanted formatting and other elements from the page. Instead, this Google Sheet formula gives you what you need. More importantly, though we don’t have an edit button, Tweets may change – for example, the author may delete it or choose to make it private. When that happens, your list must reflect these changes, and a formula can automatically refresh your Tweets so you always have the latest information available.

Developers can extend the standard functionality of Google Workspace apps, including Google Sheets, using the underlying Google Apps Script environment and JavaScript. In Google Sheets, you can add menu items, create new formulas, and even make external requests. That’s all you need to get going.

You can use the Tweet lookup endpoint with a Bearer token. Bearer token authentication is easy to implement. You will find a Bearer token in your Developer Portal, under “eys and tokens”. Take note of your Bearer token; you will need it later.

Set up your environment

Let’s start with a new spreadsheet. In case you didn’t know, there’s a shortcut to create a new Google Sheet: just navigate to https://sheets.new and you’ll have a blank slate from which to start. Next, select Script editor from the Tools menu to enter the Google Apps Script environment.

Once the environment loads, in the Run menu, select “Enable new Apps Script runtime powered by Chrome V8” so you can use the latest JavaScript syntax. You are now ready to add some code.

Ask for a Bearer token

It’s never a good idea to store credentials in code. You can store them separately. In Apps Script you can store data in the engine’s storage, and make it available only to your custom script. This is a good way to keep things separate, particularly if you use other add-ons. This is a two-step process. First, we display an input dialog to ask for the bearer token. And then, we store it:

      const BearerTokenKey = 'twitterBearerToken';
function requestBearerToken() {
  // Build the input prompt
  const ui = SpreadsheetApp.getUi(); 
  const result = ui.prompt(
    'Bearer token',
    'A Bearer token is the access token to make requests to the Twitter API.\nYou can find the Bearer token in your Twitter Developer Portal under Keys and Tokens.\n\nPaste the Bearer token here:', 
    ui.ButtonSet.OK);
  
  // Proceed if the user clicked OK
  if (result.getSelectedButton() == ui.Button.OK) {
    const bearerToken = result.getResponseText().trim();
    
    // Do nothing if the user clicked OK without specifying a value
    // (we can always ask for a token later)
    if (bearerToken.length > 0) {
      const properties = PropertiesService.getScriptProperties();
      properties.setProperty(BearerTokenKey, bearerToken);
    }
  }
}

The magic formula

Each JavaScript function you create is going to be a new formula in Google Sheets. It’s easy. Start by creating a function named TWEET(), which will subsequently make available a formula of the same name in your spreadsheet. Now, all you have to do is to make the API request and make the result available through the function/formula:

      function TWEET(url) {
    const lookupURL = `https://api.twitter.com/2/tweets/${tweetId}?expansions=author_id&user.fields=description`;
    const response = UrlFetchApp.fetch(
      lookupURL, {
        headers: {
          'Authorization': `Bearer ${bearerToken()}`
      }
      });
    
    const tweet = JSON.parse(response.getContentText());    
    if (tweet.errors && !tweet.data) {
      throw new Error(JSON.stringify(tweet.errors));
    }
    
    return tweet.data.text;
}

You probably noticed the code requests some additional fields in the Tweet request. Fields allow us to use the same API request to populate information about the user through two separate formulas, TWEET_AUTHOR and BIO_OF_TWEET_AUTHOR:

      function TWEET_AUTHOR(tweetURL) {
  helpers.checkBearerToken();
  const tweetId = helpers.tweetIdFromURL(tweetURL);
  TWEET(tweetURL);
  const tweet = helpers.tweet(tweetId);
  const user = helpers.lookupUser(tweet);
  return user.username || '';
}

function BIO_OF_TWEET_AUTHOR(tweetURL) {
  helpers.checkBearerToken();
  const tweetId = helpers.tweetIdFromURL(tweetURL);
  TWEET(tweetURL);
  const tweet = helpers.tweet(tweetId);
  const user = helpers.lookupUser(tweet);
  return user.description || '';
}

Those two functions simply rely on the TWEET formula we previously created to request a Tweet, including its author’s details, where available. At this point, you should have the Tweets you need flowing into Google Sheets.

Wrapping it up

The full script contains a few nice additions, like a custom Twitter menu that allows you to change the Bearer token or to forget it completely, effectively signing you out and removing the token from the system. You can find it on Github as a Gist.

Remember, Tweets are subject to the content redistribution rules, so always be mindful of that policy. We used several tools and services beyond the Twitter API to make this tutorial, but you may have different needs and requirements and should evaluate whether those tools are right for you.

Back to Blog Home