Google Docs Custom Refresh Script

Question

I'm trying to write a script that allows me to execute commands as soon as Google finishes making calculations (i.e. I'm trying to add to a script to Google docs that imitates some VBA "Calculate" functionalities).

The script is conceived to work by converting a range into a string and looking for the substring "Loading..." (or "#VALUE!" or "#N/A") in that string. The "while" loop is supposed to sleep until the unwanted substrings are no longer found in the string.

I'm using the following spreadsheet as a sandbox, and the code seems to work okay in the sandbox just searching for "Loading...":

https://docs.google.com/spreadsheet/ccc?key=0AkK50_KKCI_pdHJvQXdnTmpiOWM4Rk5PV2k5OUNudVE#gid=0

In other contexts, however, I have cells whose values may return as "#VALUE!" or "#N/A" for reasons other than the fact that Google is still loading/thinking/calculating. What's the way around this?

    function onEdit() {

Refresh();

};

function Refresh () {

     var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
     var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

     // set the range wherever you want to make sure loading is done    

     var range = sheet.getRange('A:A')
     var values = range.getValues();

     var string = values.toString();

     var loading = "Loading";

                do
                  {
                  var randomWait = Math.floor(Math.random()*100+50); 
                  Utilities.sleep(randomWait);
                }
                while (string.search(loading) ==! null);

      range.copyTo(sheet2.getRange('A1'), {contentsOnly:true});

customMsgBox();

};

function customMsgBox() {
  Browser.msgBox("Data refreshed.");
};

To confirm - are you looking to run some code after all calculations are finished? Is that the event you are looking for? — Arun Nagarajan
@Henrique: Thanks for taking the time. I'll keep your advice in mind. — zenhuman
I don't know if your solution will work (not sure if you can get "Loading..." values with range.getValues() -- what I do know is that you're going to have to update the values within the loop. Repeatedly searching over the same non-automatically-updating value array isn't going to yield the result you want. — mzimmerman

The Guy The Guy · Accepted Answer · 2014-03-28T18:29:13

rather than using a while loop to "sleep" you should add an event handler to your document which captures the update/refresh event and then runs whatever math/processing you need.

Here's a good place to start reading about events: https://developers.google.com/apps-script/understanding_events

but if you search the api documents for eventhandler you can get some example code fast...

Google Docs Custom Refresh Script

1 Answers