Google-sheets – Google script app delay without freezing spreadsheet calculation

With the following Google Apps Script you can grab the LIBOR value and added it to a new sheet. Goto the script editor:

and add this script:

UPDATE 25-01-2013
I've noticed that the trigger occasionally troughs a NA#. Most probably because the GoogleFinance formula takes some time to get its info. Therefore I revised the snippet a bit, to have a sleep function added to it:

NEW

function myCustomCalculation() {
  //set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get both the sheets 
  var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

  // sleep
  Utilities.sleep(6000);

  // get cell value
  var cell = sh0.getRange("D2").getValues()[0];

  // add libor value to other sheet  
  sh1.appendRow([new Date(), cell]);
}

OLD

function myCustomCalculation() {
  //set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // flush the spreadsheet before using it  
  SpreadsheetApp.flush();

  // get both the sheets 
  var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

  // get cell value from sheet[0]
  var cell = sh0.getRange("D2").getValues()[0];

  // add libor value to other sheet  
  sh1.appendRow([new Date(), cell]);
}  

The clue to the story is to create a time based trigger:

and set the trigger to execute at 5-6 pm:

See the example file I´ve created: LIBOR. I've added an extra menu item, so that you can execute the script this way.

This is one of restrictions imposed on custom functions: they cannot perform tasks that require authorization, such as accessing user's files. From the documentation:

Unlike most other types of Apps Scripts, custom functions never ask users to authorize access to personal data. Consequently, they can only call services that do not have access to personal data, specifically the following:

[a list that does not include SpreadsheetApp]

To use a service other than those listed above, create a custom menu that runs an Apps Script function instead of writing a custom function. A function that is triggered from a menu will ask the user for authorization if necessary and can consequently use all Apps Script services.

Another solution, perhaps more suitable for your case, is to use ImportRange in your spreadsheet with the function, and access those cells. A custom function can access read-only methods of Spreadsheet service, such as getSheetByName, getRange, etc.