Script: Post to Twitter

This script helps you post to Twitter. Relied on a script of Amit Agarwal of similar nature to put this together. Made additions to the script thereafter to suit the use case I was looking for. 

This script reads from a spreadsheet and posts the data to Twitter. One item gets picked up and published to Twitter in each run. Status column gets marked as published for the item that gets picked up and the count increments by 1.  If there is no item to be picked up, the count does not get updated and remains the same. 

Use Case

I use a Telegram bot to log my notes. From among the list of notes that have been logged to publish, I wanted a script to publish to Twitter. The objectives being to a) automate the steps involved in publishing and b) separating production from distribution. 

Revisions wishlist

  • Add the Menu to the spreadsheet for being able to run the script from the sheet itself
  • Add Timer for the script to auto run on a schedule
  • If the status column of the item picked up reads published, count gets updated but it does not get published. 

Project Properties

You will need to generate and set the following project properties for this script. 

  1. CONSUMER_KEY
  2. CONSUMER_SECRET
  3. ACCESS_TOKEN
  4. ACCESS_SECRET

Reference Screenshots

Script

function readingfromSheet(){

  //spreadsheet

//  var ss = SpreadsheetApp.openById("1J-rID8K6DNt2jeMZ7DWOH5YvMKvA7baYKTiWl1pJ3iE");

  var ss = SpreadsheetApp.openById('1p5A1jNWGPO96kQpuT1bC41vC4j5K3GWnwHuowMkibZA');

  Logger.log(ss.getName());

  

  //sheet

  var sheet = ss.getSheetByName('SKG Articles')

  if (sheet != null) {

    Logger.log(sheet.getIndex());

  }

  

  //range & values

  // This represents ALL the data

  var range = sheet.getDataRange();

  var data = range.getValues();

  Logger.log(JSON.stringify(data));

  

  //get count, tweet, set status, increment count, set count

  

  var count = sheet.getRange('b2').getValue(); 

  var tweet = sheet.getRange(count, 1).getValue(); 

  Logger.log(tweet);

  

  if (tweet){

    postTweet(tweet);

    sheet.getRange(count, 2).setValue('Published');

    ++count; Logger.log(count);

    sheet.getRange('b2').setValue(count); 

    

  }

  else {

    Logger.log("tweet is blank");

  } 

}

 

function postTweet(message) {

 Logger.log(message);

  //var message = "If you love me also I love you if you";

  var method = "POST";

  var baseUrl = "https://api.twitter.com/1.1/statuses/update.json";

  var props = PropertiesService.getScriptProperties();


  var oauthParameters = {

    oauth_consumer_key: props.getProperty('CONSUMER_KEY'),

    oauth_token: props.getProperty('ACCESS_TOKEN'),

    oauth_timestamp: (Math.floor((new Date()).getTime() / 1000)).toString(),

    oauth_signature_method: "HMAC-SHA1",

    oauth_version: "1.0"

  };


  oauthParameters.oauth_nonce = oauthParameters.oauth_timestamp + Math.floor(Math.random() * 100000000);


  var payload = {

    status: message

  };


  var queryKeys = Object.keys(oauthParameters).concat(Object.keys(payload)).sort();


  var baseString = queryKeys.reduce(function(acc, key, idx) {

    if (idx) acc += encodeURIComponent("&");

    if (oauthParameters.hasOwnProperty(key))

      acc += encode(key + "=" + oauthParameters[key]);

    else if (payload.hasOwnProperty(key))

      acc += encode(key + "=" + encode(payload[key]));

    return acc;

  }, method.toUpperCase() + '&' + encode(baseUrl) + '&');


  oauthParameters.oauth_signature = Utilities.base64Encode(

    Utilities.computeHmacSignature(

      Utilities.MacAlgorithm.HMAC_SHA_1,

      baseString,

      props.getProperty('CONSUMER_SECRET') + '&' + props.getProperty('ACCESS_SECRET')

    )

  );


  var options = {

    method: method,

    headers: {

      authorization: "OAuth " + Object.keys(oauthParameters).sort().reduce(function(acc, key) {

        acc.push(key + '="' + encode(oauthParameters[key]) + '"');

        return acc;

      }, []).join(', ')

    },

    payload: Object.keys(payload).reduce(function(acc, key) {

      acc.push(key + '=' + encode(payload[key]));

      return acc;

    }, []).join('&'),

    muteHttpExceptions: true

  }


  var response = UrlFetchApp.fetch(baseUrl, options);

  var responseHeader = response.getHeaders();

  var responseText = response.getContentText();

  Logger.log(responseText);

}


function encode(string) {

  return encodeURIComponent(string)

    .replace('!', '%21')

    .replace('*', '%2A')

    .replace('(', '%28')

    .replace(')', '%29')

    .replace("'", '%27');