Script: Connect App



**Credits**

Jibin: Helped in putting together the HTML interface and fetching data from that. <br>
Arya: The emailing code came from her. <br>
Saleeh: Helped with code for notification, CSS & Tamotsu library. <br>
Zach: Helped with embed code.

[Code](https://script.google.com/a/sijokuruvilla.in/d/1P_6oDwCzmTPRsY9AGrD-GdlMMgg2MjbwjJE0ILkP8wr-svSXSikUMu6x/edit)

[Spreadsheet template](https://docs.google.com/spreadsheets/d/1pj1hLLBAH-Mq5gDt0SZg_jw9CwnEcV93TEkfls51g8M/edit#gid=0)

Projects worth pursuing

The decision making framework I use and advocate.

  • Reduces human effort
  • Increases human output
  • Enhances human happiness
  • Reduces human suffering

Any project that satisfies any of these 4 conditions is worth pursuing.

Even if its only for a single person. Even if that single person is you.

Never let anyone tell you otherwise.

Address people on a first name basis

One of the first instances I came across this practice of addressing people by their first names was in my college basketball team. I was in my first year and one of the seniors, Anoop John, who knew I used to play in the school basketball team asked me to come for selection trials for the college team. I did turn up and that was my first run in with Xavier. Before I go on, a note on Xavier. He is one of the most fantastic human beings I have ever come across in life. More on that in another post perhaps.

So Xavier was a final year student and also the captain of our basketball team. A gifted player, he once shared with me his story of how he ended up in engineering college. He never fancied himself doing engineering much less doing engineering from CET apparently. A key trivia Xavier while narrating to me how he ended up in engineering college was that he had made use of the backside of the entrance examination hall ticket for tabulation of the scores of the card game that very night after the entrance. It so happened that he did manage to secure an admit in CET via the sports quota. Xavier was unsure to whether or not to take it up and that’s when his the basketball coach, Antony Sir, that guided him not to turn down the opportunity.

Towards the end of the selection trials, Xavier remarked that it appears that I can play and asked me to start coming for practice sessions along with the team. To which I ended up replying — വരാം Xavier ചേട്ടാ. And that’s when he stopped me right there. “എന്തോന്ന്, Xavier ചേട്ടനോ? ആ പരിപാടി ഒന്നും ഇവിടെ വേണ്ട, Xavier മതി” — എന്നായിരുന്നു Xavier ന്റെ മറുപടി. On the face of it, this might seem trivial. But anyone who is familiar with how ridiculously hierarchical and absurdly rigid some of these social constructs in our colleges are, of which the culture of addressing your seniors as ചേട്ടാ / ചേച്ചി is a deeply ingrained one, would understand that this is pathbreaking stuff.

So as a result of this culture set in motion by Xavier, we all addressed each other by our first names throughout. Few years down the line, when I took over as captain of the college basketball team, this addressing people by first names remained one of the cultural edifices around which our team was continued to be built and one that I carry forward to this day in my personal and professional life. We were all peers working towards a common outcome—contributing in our individual capacity in ways that each of us could. While this was what was happening, it was not Xavier who articulated it that way. That was to happen few more years later.

My second run in with the culture of addressing people by first names happened in Infosys. It was during the induction period with one of the learning facilitators. Unfortunately I do not remember the name of the person. After one her sessions, I walked up to her and ended up addressing her as <name> madam. That’s when she stopped me and guided me to address her by her first name. When I conveyed my discomfort in addressing someone much senior and elder to me by her first name, she helped me understand that it was the professional thing to do and that Infosys was an organisation that abided by that practice. She then went on to say that even if I run in to Nandan or NRN, you address them by names and not with Sir tag. And at that point, what she articulated has stuck with me since.

We are all peers working towards common outcomes, with each person contributing in ways that he or she can.

Script: Dialog box

To add a dialog box to your script

Dialog box

var ui = SpreadsheetApp.getUi();
ui.alert('Notification message');

Script: Text box

To add a text box to your script

Text box 

var ui = SpreadsheetApp.getUi(); // Same variations.

var result = ui.prompt(
'Enter name:',
ui.ButtonSet.OK_CANCEL);
var button = result.getSelectedButton();
var name= result.getResponseText();

Evaluating text box input

if (button == ui.Button.OK) {
// User clicked "OK".
ui.alert('Input received);
} else if (button == ui.Button.CANCEL) {
// User clicked "Cancel".
ui.alert('Clicked cancel');
} else if (button == ui.Button.CLOSE) {
// User clicked X in the title bar.
ui.alert('Closed the dialog.');
}


Script: Triggers

Creating triggers

function createTriggers(){

// Trigger every 6 hours
ScriptApp.newTrigger('myFunction')
.timeBased()
.everyHours(6)
.create();

// Trigger everyday at 10:00 hours.
ScriptApp.newTrigger('dailyCatchup')
.timeBased()
.atHour(10)
.everyDays(1)
.inTimezone("Asia/Kolkata")
.create();

// Trigger every Monday at 09:00 hours
ScriptApp.newTrigger('myFunction')
.timeBased()
.onWeekDay(ScriptApp.WeekDay.MONDAY)
.atHour(9)
.create();

}

Deleting all triggers

function deleteallTriggers() {
// Loop over all triggers.
var allTriggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < allTriggers.length; i++) {
ScriptApp.deleteTrigger(allTriggers[i]);
}
}


Capturing trigger ID

 var trigger = ScriptApp.newTrigger('myFunction')
.timeBased()
.everyHours(6)
.create();

var triggerId = trigger.getUniqueId();

The following code will create a trigger, capture its triggerID and store that triggerID to the B2 column of a spreadsheet

function createTrigger(){

var trigger = ScriptApp.newTrigger('myFunction')
.timeBased()
.everyHours(6)
.create();

var triggerId = trigger.getUniqueId();

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1SCKp4r_3xllf1eZQFvE0LDXffdwCSdFfvEdUWAkaAsg/edit#gid=0");
var sheet = ss.getSheetByName("Values");

var cell = sheet.getRange("B2");
cell.setValue(triggerId); Logger.log(trigger);
Logger.log(triggerId);
Logger.log(cell.getValue());}

Delete specific trigger (using Trigger ID)

function deleteTrigger(triggerId) {

var allTriggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < allTriggers.length; i++) {
// If the current trigger is the correct one, delete it.
if (allTriggers[i].getUniqueId() === triggerId) {
ScriptApp.deleteTrigger(allTriggers[i]);
break;
}
}
}

The following code will read the triggerID value stored in the B2 column of the spreadsheet and delete that trigger

function deleteTrigger() {

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1SCKp4r_3xllf1eZQFvE0LDXffdwCSdFfvEdUWAkaAsg/edit#gid=0");
var sheet = ss.getSheetByName("Values");

var cell = sheet.getRange("B2");
var triggerId = cell.getValue();
Logger.log(triggerId);

var allTriggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < allTriggers.length; i++) {
// If the current trigger is the correct one, delete it.
if (allTriggers[i].getUniqueId() === triggerId) {
ScriptApp.deleteTrigger(allTriggers[i]);
break;
}
}
}


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');




Script: Creating a Slides file

Creates a Google Slides file from script code.  Recall it was one of those board game Sundays during which this was attempted and it was Praveen Sridhar who helped me out with this piece of code.  

This is a fun script. Tried out more as a test. Wish to attempt capturing a deck of one of my talks in this format as a script. Again, more as a fun thing to do at this point.  


Script

var NAME = "Read Me";

var deck = SlidesApp.create(NAME);


function addText() {

  

  var slide;

  var shapes;

  var textRange;

  

  slide = deck.getSlides()[0]

  

  shapes =  slide.getShapes();

  textRange = shapes[0].getText();

  textRange.setText('Mail works best');

  textRange = shapes[1].getText();

  textRange.setText('skg@sijokuruvilla.in');

  

  slide = deck.appendSlide(SlidesApp.PredefinedLayout.SECTION_HEADER);

  shapes =  slide.getShapes();

  textRange = shapes[0].getText();

  textRange.setText('Email: skg@sijokuruvilla.in');

  

  slide = deck.appendSlide(SlidesApp.PredefinedLayout.SECTION_HEADER);

  shapes = slide.getShapes();

  textRange = shapes[0].getText();

  textRange.setText('Twitter: @sijokuruvilla');

  

  Logger.log(textRange.asString())

}


Notes

  • Slides file that was created - Link
  • Reference screenshots provided below
  • Screenshots



    Script: All files in Drive

    A very useful script. Lists out all files in your Google Drive to a Google Spreadsheet

    Script

    function allFilesInDrive() {

    // Log the name of every file in the user's Drive.
    // its type & url as well

    var files = DriveApp.getFiles();
    var ss = SpreadsheetApp.openById('1Hq1eFGYCUzf82bvUC_yM9abG7Bt9sY30xGIX-pmUK2c');
    var sheet = ss.getSheetByName("Sheet1");
    var range = sheet.getDataRange();
    var data = range.getValues();
    var count = 1;


    while (files.hasNext()) {
    var file = files.next();

    Logger.log(data);
    Logger.log(file.getName());
    Logger.log(file.getMimeType());
    Logger.log(file.getUrl());
    sheet.getRange(count, 1).setValue(file.getName());
    sheet.getRange(count, 2).setValue(file.getUrl());
    sheet.getRange(count, 3).setValue(file.getAccess('office@sijokuruvilla.in'));

    count++;


    }

    }

    Note

    • Spreadhseet ID and the sheet name to be copied accurately.
    • Reference: Output that gets captured in the spreadsheet can be viewed from the screenshot below


    The script takes a fair amount of time to execute. So if you wish to try this out quickly you may make use of the following script. In this the output ie list of files in Drive can be viewed in the Logger window as against the Spreadsheet. The following one was also the first script I managed to put together. Took Jibin's help to then bring it together in the above form. 


    function allFilesInDrive() {

        // Log the name of every file in the user's Drive.

        // its type & url as well

      

      var files = DriveApp.getFiles();

      

      while (files.hasNext()) {

      var file = files.next();

      Logger.log(file.getName());

        Logger.log(file.getMimeType());

        Logger.log(file.getUrl());

    }  

    }