Skip to content

Instantly share code, notes, and snippets.

@apocratus
Last active September 11, 2020 16:56
Show Gist options
  • Save apocratus/649840c4109ca153718e2dfed81dea62 to your computer and use it in GitHub Desktop.
Save apocratus/649840c4109ca153718e2dfed81dea62 to your computer and use it in GitHub Desktop.
Retrieve Twitter Profile & Posts data from API with Google Apps Script and save to Google Sheets
/**
* This function retrieves all your relevant profile data from the Twitter API and saves it to a Google Sheets spreadsheet. It
* checks the last inserted column and if the dates are the same it replaces the data. (Run trigger hourly for up-to-date data).
*
* You need to register a new app at https://apps.twitter.com/ add the consumer and secret key below and approve the app by
* running makeInitialAuthorizationRequest().
*
* The code below uses the OAuth1 library (Resources -> Libraries... -> Add a Library
* (use "1CXDCY5sqT9ph64fFwSzVtXnbjpSfWdRymafDrtIZ7Z_hwysTY7IIhi7s" as the identifying key)
*
* The data can be used to build a Google Data Studio report of your Twitter data.
*/
// the name of the sheets within your document
var sheetNameHistorical = "Sheet1";
var sheetNamePosts = "Sheet2";
// the username of the Twitter account you want the twitter data for
var username = "twitter_username";
// the Twitter app credentials to use to access the API
var consumer_key = 'twitter_app_consumer_key';
var secret_key = 'twitter_app_secret_key';
function getTwitterService() {
// Create a new service with the given name. The name will be used when
// persisting the authorized token, so ensure it is unique within the
// scope of the property store.
return OAuth1.createService('twitter')
// Set the endpoint URLs.
.setAccessTokenUrl('https://api.twitter.com/oauth/access_token')
.setRequestTokenUrl('https://api.twitter.com/oauth/request_token')
.setAuthorizationUrl('https://api.twitter.com/oauth/authorize')
// Set the consumer key and secret.
.setConsumerKey(this.consumer_key)
.setConsumerSecret(this.consumer_secret)
// Set the name of the callback function in the script referenced
// above that should be invoked to complete the OAuth flow.
.setCallbackFunction('authCallback')
// Set the property store where authorized tokens should be persisted.
.setPropertyStore(PropertiesService.getUserProperties());
}
function authCallback(request) {
var twitterService = getTwitterService();
var isAuthorized = twitterService.handleCallback(request);
if (isAuthorized) {
return Logger.log('Success! You can close this tab.');
} else {
return Logger.log('Denied. You can close this tab');
}
}
function makeInitialAuthorizationRequest() {
// For first run only, retrieve the url from the log and use to authorize the app
var twitterService = getTwitterService();
var authorizationUrl = twitterService.authorize();
Logger.log(authorizationUrl);
}
function makeTweetsRequest() {
var twitterService = getTwitterService();
var response = twitterService.fetch('https://api.twitter.com/1.1/statuses/user_timeline.json');
var json = JSON.parse(response);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(this.sheetNamePosts);
sheet.clear();
sheet.appendRow(["Date", "Url", "Text", "Likes", "Retweets"]);
var resultdata = [];
for (i=0; i< json.length; i++){
var c = json[i];
var date = Utilities.formatDate(new Date(c.created_at), "GMT", "yyyy-MM-dd");
var url = c.entities.urls[0].url;
var text = c.text;
var likes = c.favorite_count;
var retweets = c.retweet_count;
var replyuser = c.in_reply_to_user_id;
var replypost = c.in_reply_to_status_id;
if(replyuser == null && replypost == null) {
resultdata.push([date, url, text, likes, retweets]);
}
}
for(i=0; i<resultdata.length; i++) {
sheet.appendRow(resultdata[i]);
}
}
// get user profile data
function makeProfileRequest() {
var twitterService = getTwitterService();
var response = twitterService.fetch('https://api.twitter.com/1.1/users/show.json?screen_name='+this.username);
var json = JSON.parse(response);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(this.sheetNameHistorical);
// new data
var followers = parseInt(json.followers_count);
var following = parseInt(json.friends_count);
var tweets = parseInt(json.statuses_count);
var listed = parseInt(json.listed_count);
var favourites = parseInt(json.favourites_count);
// current data
var data = sheet.getDataRange().getValues();
var lastRow = sheet.getLastRow();
// if the sheet is empty, add header row
if(lastRow == 0) {
sheet.appendRow(['Date', 'Followers', 'Following', 'Posts', 'Listed', 'Favourites']);
}
else {
var lastDate = new Date(sheet.getRange(lastRow, 1).getValues());
lastDate.setHours(lastDate.getHours() + 4); // fix lack of hours
var checkToday = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");
var checklastDate = Utilities.formatDate(lastDate, "GMT", "yyyy-MM-dd");
// if the date already exists, remove and replace
if(checkToday === checklastDate ) {
sheet.deleteRow(lastRow);
}
}
sheet.appendRow([Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd"), followers, following, tweets, listed, favourites]);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment