Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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.tweet = (id) => JSON.parse(PropertiesService.getScriptProperties().getProperty(`tweet-${id}`));
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`;
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 || '';
}
@tingfangyeh

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link
Owner Author

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

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

@CarlosDuro CarlosDuro commented Mar 21, 2021

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

@iamdaniele

This comment has been minimized.

Copy link
Owner Author

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

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link
Owner Author

@iamdaniele iamdaniele commented Mar 22, 2021

Sorry about that! I just fixed the example.

@CarlosDuro

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link
Owner Author

@iamdaniele iamdaniele commented Mar 22, 2021

Made a small change. Does it work now?

@CarlosDuro

This comment has been minimized.

Copy link

@CarlosDuro CarlosDuro commented Mar 22, 2021

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

@iamdaniele

This comment has been minimized.

Copy link
Owner Author

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

This comment has been minimized.

Copy link

@CarlosDuro CarlosDuro commented Mar 22, 2021

Perfect @iamdaniele, it worked!!! Thanks

@CarlosDuro

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link
Owner Author

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

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