Retrieve Twitter Profile & Posts data from API with Google Apps Script and save to Google Sheets

twitterData.gs

/**
 * 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]);
}