Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Add public metrics
const BearerTokenKey = 'twitterBearerToken';
function onOpen() {
SpreadsheetApp
.getUi()
.createMenu('Twitter')
.addItem('Set Bearer token', 'helpers.requestBearerToken')
.addItem('Sign out', 'helpers.logout')
.addToUi();
}
const helpers = {};
helpers.bearerToken = () => PropertiesService.getScriptProperties().getProperty(BearerTokenKey);
helpers.checkBearerToken = () => {
if (!helpers.bearerToken()) {
throw new Error('You need to set a Bearer token before using this function. Select "Set Bearer Token" from the Twitter menu to set it.');
}
}
helpers.lookupUser = (tweet) => {
const authorId = tweet.data.author_id;
let author = {};
for (const user of tweet.includes.users) {
if (user.id === authorId) {
author = user;
return user;
}
}
return {};
}
helpers.logout = () => PropertiesService.getScriptProperties().deleteAllProperties();
helpers.tweetIdFromURL = (url) => {
const tweetRegex = url.match(/status\/(\d{1,19})/);
if (!tweetRegex) {
throw new Error('Invalid URL');
}
return tweetRegex[1];
}
helpers.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);
}
}
}
helpers.tweet = (tweetId) => {
helpers.checkBearerToken();
const lookupURL = `https://api.twitter.com/2/tweets/${tweetId}?expansions=author_id&user.fields=description&tweet.fields=public_metrics`;
const response = UrlFetchApp.fetch(
lookupURL, {
headers: {
'Authorization': `Bearer ${helpers.bearerToken()}`
}
});
const tweet = JSON.parse(response.getContentText());
if (tweet.errors && !tweet.data) {
throw new Error(JSON.stringify(tweet.errors));
}
return tweet;
}
function TWEET(url) {
const tweetId = helpers.tweetIdFromURL(url);
const tweet = helpers.tweet(tweetId);
return tweet.data.text;
}
function TWEET_AUTHOR(tweetURL) {
const tweetId = helpers.tweetIdFromURL(tweetURL);
const tweet = helpers.tweet(tweetId);
const user = helpers.lookupUser(tweet);
return user.username || '';
}
function BIO_OF_TWEET_AUTHOR(tweetURL) {
const tweetId = helpers.tweetIdFromURL(tweetURL);
const tweet = helpers.tweet(tweetId);
const user = helpers.lookupUser(tweet);
return user.description || '';
}
function LIKES_OF_TWEET(tweetURL) {
const tweetId = helpers.tweetIdFromURL(tweetURL);
const tweet = helpers.tweet(tweetId);
return tweet.data.public_metrics.like_count || '';
}
function REPLIES_OF_TWEET(tweetURL) {
const tweetId = helpers.tweetIdFromURL(tweetURL);
const tweet = helpers.tweet(tweetId);
return tweet.data.public_metrics.reply_count || '';
}
function RETWEETS_OF_TWEET(tweetURL) {
const tweetId = helpers.tweetIdFromURL(tweetURL);
const tweet = helpers.tweet(tweetId);
return tweet.data.public_metrics.retweet_count || '';
}
function QUOTES_OF_TWEET(tweetURL) {
const tweetId = helpers.tweetIdFromURL(tweetURL);
const tweet = helpers.tweet(tweetId);
return tweet.data.public_metrics.quote_count || '';
}
@tingfangyeh
Copy link

tingfangyeh commented Dec 15, 2020

Hi, I added your gs file into my spreadsheet, and set the bearer Token as well.
But the tweets formula comes out the result as below.

Exception: Request failed for https://api.twitter.com returned code 403. Truncated server response: {"client_id":"17773748","required_enrollment":"Standard Basic","detail":"When authenticating requests to the Twitter API v2 endpoints, you must use... (use muteHttpExceptions option to examine full response)(Row 69)が発生しました。

Could you tell me how to fix it and make the specific tweets show in the Google Spreadsheet with your tweets formula?
Thank you.

@iamdaniele
Copy link
Author

iamdaniele commented Dec 15, 2020

@tingfangyeh that means your app does not have a developer account. You need to apply for a developer account in order to use the v2 Twitter API. You can apply for access on Twitter Developer.

@mhawksey
Copy link

mhawksey commented Feb 11, 2021

@iamdaniele - nice work!

For others interested in this example and wanting to take a little further a couple of small additions are included in this fork https://gist.github.com/mhawksey/ba40a2321a5f984998a01360363d5983

The changes are:

  • \\ @OnlyCurrentDoc - will limit the scope of the script so when you authenticate it limits write access to the Sheet the script is included with
  • autocomplete function/formula - custom functions can be included like built-in functions with autocomplete and inline help using JsDoc notation and using the @customfunction tag. More info on Autocomplete

Happy scripting!

@CarlosDuro
Copy link

CarlosDuro commented Mar 21, 2021

@iamdaniele is there a way to add media_url to the tweet functions?

@iamdaniele
Copy link
Author

iamdaniele commented Mar 21, 2021

@CarlosDuro it should be relatively easy to do so. I'm putting the answer here as this Gist is bound to an existing tutorial.

  1. Add the attachments.media_keys expansion to the lookup request:
const lookupURL = `https://api.twitter.com/2/tweets/${tweetId}?expansions=author_id,attachments.media_keys&user.fields=description&media.fields=url,preview_image_url`;
  1. Add a lookup method to get the media URLs:
helpers.lookupMedia = (tweet) => {
  if (!(tweet.data.attachments && tweet.data.attachments.media_keys)) {
    return;
  }

  if (!(tweet.includes && tweet.includes.media)) {
    return;
  }
  const keys = tweet.data.attachments.media_keys;
  const media = tweet.includes.media.filter(item => keys.includes(item.media_key));
  return media.map(item => item.url || item.preview_image_url);
}
  1. Define the formula function:
function MEDIA_OF_TWEET(tweetURL) {
  const tweetId = helpers.tweetIdFromURL(tweetURL);
  const tweet = helpers.tweet(tweetId);
  return helpers.lookupMedia(tweet);
}

That's it! Call =MEDIA_OF_TWEET("Tweet URL") (replacing Tweet URL with a URL to a Tweet) to get media attachments (when present) from the specified Tweet.

@CarlosDuro
Copy link

CarlosDuro commented Mar 22, 2021

Thanks @iamdaniele it is returning "Error Reference Error: test is not defined (línea 140)." I believe that is something about this:

function MEDIA_OF_TWEET(key) {
return helpers.lookupMedia(test[key]);
}

It had done with a tweet with photo: https://twitter.com/elcancillercom/status/1373966404358144006

@iamdaniele
Copy link
Author

iamdaniele commented Mar 22, 2021

Sorry about that! I just fixed the example.

@CarlosDuro
Copy link

CarlosDuro commented Mar 22, 2021

Thank you @iamdaniele I still have an error I tried to fix it but with no success. The error "ReferenceError: tweetURL is not defined (línea 140)."

function MEDIA_OF_TWEET(key) {
const tweetId = helpers.tweetIdFromURL(tweetURL);
const tweet = helpers.tweet(tweetId);
const media = helpers.lookupMedia(tweet);
return media.item.preview_image_url || '';
}

@iamdaniele
Copy link
Author

iamdaniele commented Mar 22, 2021

Made a small change. Does it work now?

@CarlosDuro
Copy link

CarlosDuro commented Mar 22, 2021

@iamdaniele it worked but did not display any information. The cell went blank

@iamdaniele
Copy link
Author

iamdaniele commented Mar 22, 2021

@CarlosDuro note that you have to change the API request URL to request the media fields:

const lookupURL = `https://api.twitter.com/2/tweets/${tweetId}?expansions=author_id,attachments.media_keys&user.fields=description&media.fields=url,preview_image_url`;

@CarlosDuro
Copy link

CarlosDuro commented Mar 22, 2021

Perfect @iamdaniele, it worked!!! Thanks

@CarlosDuro
Copy link

CarlosDuro commented Mar 24, 2021

Hi @iamdaniele I wonder if you know if it is possible also to retrieve video_url for v2 twitter api

@iamdaniele
Copy link
Author

iamdaniele commented Mar 24, 2021

@CarlosDuro at the moment you can only retrieve the preview_image_url for a video. This is a known limitation of the v2 API and it will be addressed in a future release.

@rseabrook
Copy link

rseabrook commented Jun 3, 2021

Hey @iamdaniele, the helpers.tweet function is defined twice (line 14 + 66-82). It looks like the first instance was from an earlier draft since the results aren't saved to properties anywhere!

@iamdaniele
Copy link
Author

iamdaniele commented Jun 7, 2021

Thank you @rseabrook, good catch! Removed the definition on line 14.

@Trade-Help
Copy link

Trade-Help commented May 2, 2022

Hi, I added your gs file into my spreadsheet, and set the bearer Token as well. But the tweets formula comes out the result as below.

Exception: Request failed for https://api.twitter.com returned code 403. Truncated server response: {"client_id":"17773748","required_enrollment":"Standard Basic","detail":"When authenticating requests to the Twitter API v2 endpoints, you must use... (use muteHttpExceptions option to examine full response)(Row 69)が発生しました。

Could you tell me how to fix it and make the specific tweets show in the Google Spreadsheet with your tweets formula? Thank you.

@iamdaniele
Copy link
Author

iamdaniele commented May 2, 2022

Hi @Trade-Help this means your App ID 17773748 does not belong to a Project. Your app needs to belong to a Project in order to use the v2 Twitter API. You need to create a Project (or use an existing Project) and add your App ID to it. For more details, please refer to our documentation.

@jimorell
Copy link

jimorell commented Jun 5, 2022

Is it possible to extend this further to call the number of likes, retweets and replies a given Tweet has had? I've tried extending it on further by adding a helper function with a different lookupURL and an additional function. It is not erroring on me, but isn't returning anything!
See the gist at: https://gist.github.com/jimorell/f0a4ccaa8528775dcc07617facd556bf

@iamdaniele
Copy link
Author

iamdaniele commented Jun 6, 2022

@jimorell it's definitely possible with these steps:

  1. Add &tweet.fields=public_metrics to line 67 to request public metrics (this returns likes, retweets, replies, and Quote Tweets)
  2. Add a formula function for each public metric. The formula should get the Tweet payload (similar to the other functions), then extract the metric from the public_data object. For example, to get likes, the function should return public_metrics.like_count. Repeat for the other public metrics as needed.

I updated this gist to include these examples. Let us know what you think!

@jimorell
Copy link

jimorell commented Jun 6, 2022

@iamdaniele Awesome, thank you! That opens up a world of ways to analyse how engaging Tweets are.

I presume I could extend it on further with a call to the relevant endpoints to get liking_users and retweeted_by too? My goal really was to find which users had liked, retweeted/quote tweeted and replied to a given tweet and the timestamps at which those actions had occurred. I know that will only be possible on my own account which is totally fine.

@iamdaniele
Copy link
Author

iamdaniele commented Jun 6, 2022

@jimmoffitt correct, and the gist you referenced does that. If you need further help, I suggest that you visit the Twitter Community Forums.

@jimorell
Copy link

jimorell commented Jun 6, 2022

@iamdaniele Thank you. The gist kind of does 😂 It's my take on it so it's not entirely functional! I will head over to the forums and thank you so much for your help and the inspiration.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment