Google App Script: Now with Time-based Triggers.

Google App Script is continuing to develop. You can interact with not only the spreadsheet that hosts the script but also email, Google Sites, Calendars, Forms, Documents and the DocList, Web pages and services, XML, Maps and Contacts. Check out the documentation. What I was not aware of is that you can now incorporate time-based triggers into your scripts, which makes them a lot more useful. I have resolved today to publish a simple site about my upcoming wedding to receive RSVPs and publish wedding information - no doubt using Sites and Google App Script.

Using Google App Script to return a GRC Password

On the weekend a did some experimenting with Google App Script. I first looked at Google App Script  during its brief pre-release phase but aside from following the tutorials I never did something useful with it of my own.

Of course there are lots of useful things Google App Script can help you out with, especially now that Google has added additional APIs for accessing more services. Workplaces using Google Apps (like my company) should especially find a lot of use for the script for automating common tasks.

To get my feet wet again with Google App Script I chose to work on something very simple (single function simple) and marginally useful to me. I wrote a Google Spreadsheet function that would pull a new 64 character high-quality random password from grc.com whenever it runs. Aside: GRC stands for Gibson Research Corporation; I listen to Steve Gibson and the Security Now netcast each week.

See below for the script itself. It is very simple. To try it, open a google spreadsheet, open its script editor and paste in the script. In a spreadsheet cell, use the formula '=getGRCPassword()'. Whenever the cell recalculates, a new password will be retrieved.


var sPasswordPageAddr = 'https://www.grc.com/passwords.htm';
var sPreambleText = 'preceding fragment from grc. Unfortunately '
  + 'blogger could not deal with it';

//This is a function which will return a new alphamnumeric only 
//password from GRC.com.
function getGrcPassword() {

  //get content of password page
  try {
    var response = UrlFetchApp.fetch(sPasswordPageAddr);
  } catch(e) { return e.message; }

  //Examine response and extract password
  //check to see if page received successfully:
  if(response.getResponseCode() == 200) {

    //if received successfully, examine the content and look for our preamble    
    //marker
    responseText = response.getContentText();
    iPasswordPreambleStart = responseText.indexOf(sPreambleText);

    //check to see if preamble marker was found
    if(iPasswordPreambleStart > 0) {

    //preamble marker was found, extract the password
    iPasswordStart = iPasswordPreambleStart + sPreambleText.length;
    sPassword = responseText.substr(iPasswordStart, 63);

    //display the password for now
    return sPassword;

  } else {

    //We could not find the password preamble. Maybe the page has changed?
    return 'Could not find password string. Page structure might have changed';
}

} else {

    //bad response from webserver. Maybe our address is wrong.
    //Maybe their webserver is down, etc
    return 'Problem accessing ' + sPasswordPageAddr
    + ': Response code ' + response.getResponseCode();
  }
}


Note that I pull the password out using basic text searching. A more robust approach would be to use forgiving HTML parser. As it stands now the approach below is easily broken by a minor page update. I did not use a HTML parser since I could not find an appropriate one to use given the environment. I will keep looking.

Learning Google App Script

I was lucky enough this morning to receive in my inbox an acceptance from Google into the beta program for Google App Script.

Google App Script was announced at this year's Google I/O. Google App Script (to be abbreviated GAS?) is a javascript scripting capability that allows users to program server side scripts to automate Google services. GAS is available initially only within Google spreadsheets and from the documentation I have seen to date there is still a lot of the API (for the various Google services) to fill out.

I hope to try a few different projects with GAS in the coming days and weeks. Stay tuned.

If you want to know more you can read the Google blog post or even better watch the video of the Google App Script session of I/O. You can also find a Google Apps overview, API documentation and some tutorials on the Google App Script documentation pages.