Skip to content

Instantly share code, notes, and snippets.

@iamdaniele
Last active February 12, 2023 05:40
Show Gist options
  • Star 13 You must be signed in to star a gist
  • Fork 4 You must be signed in to fork a gist
  • Save iamdaniele/b0132e54692e53442c87f4694d56807c to your computer and use it in GitHub Desktop.
Save iamdaniele/b0132e54692e53442c87f4694d56807c to your computer and use it in GitHub Desktop.
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 || '';
}
@iamdaniele
Copy link
Author

Made a small change. Does it work now?

@CarlosDuro
Copy link

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

@iamdaniele
Copy link
Author

@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

Perfect @iamdaniele, it worked!!! Thanks

@CarlosDuro
Copy link

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

@iamdaniele
Copy link
Author

@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

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

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

@Trade-Help
Copy link

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

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

@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

@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.

@CarlosDuro
Copy link

Hi @iamdaniele me again here. Does it possible now to retrieve video_url for v2 twitter api?

@itannu
Copy link

itannu commented Feb 12, 2023

Is it possible to retrieve the view count of tweet that is showing now below each tweet?

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