Mar 5, 2014

Running scripts on google drive documents


I admit I have always looked to "cloud based" document editing (such as google drive, Microsoft Office 365, etc) with certain dislike, mainly due to the misconception that these applications would not allow me to use my precious scripts (after all, any true developer shall use scripts everywhere, right? ;)

Even though I somewhat disliked the idea, I ended up ceding (the convenience of having it available anywhere is so hard to ignore :) and,for some time, I have been using one spreadsheet to store a piece of information I use on a monthly basis.

It happens that last week I decided that one of the tasks involving that spreadsheet should be automated (basically, given some condition I'd like to add some information to a specific cell, which is a perfect job for a script) so I was considering moving the spreadsheet to my box and use LibreOffice (which I highly recommend, by the way) to edit it. 

While I was poking around my google spreadsheet menus, I stumbled in "Tools/Script Editor" menu option and realized that my resistance in using such online solutions had no ground whatsoever, i.e, it was just prejudice from my side :( I just selected that menu option and voila, scripts with debugging support!

That is all I needed! Some time (and a lot of keystrokes) later and I had my first script running; but the best was yet to come: not only I was able to reference my newly created function, but I could also schedule it to run from time to time! (that's exactly what I was looking for).

Following you can see my script:
function checkDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];       
  var range = sheet.getRange("A1");
  var now = new Date();
  if (now.getHours() <= 10 || (now.getHours() >= 15 && now.getMinutes() > 30) || (now.getHours() >= 13 && (now.getHours() <= 14 && now.getMinutes() < 30)) ) {    
  else {
And it worked like a charm.

To my astonishment, today I figured it out that I can even execute WebServices (SOAP) from within my scripts (and of course I put it in action immediately) ! 

Happy Coding!

(Leia, uma versão extendida, deste post em portugues)

No comments: