Daily calculation and record insertion in Google Spreadsheet

Question

I have a Google Spreadsheet that I am using to checkout last prices or levels of certain stock indices's. I have two worksheets in the workbook, one contains the indices's and a calculation I am keeping track of and the other sheet is empty.

I want, on the empty sheet to check the calculation on the other sheet daily at 5PM eastern standard time and take that value and create a new record on the empty sheet going from A2:A

How would I go about this?

Sheet one looks like this:

Index, Price, No. Of Components

^DJI, 12,456, 30

etc.

Calculation

=myCustomFormula

Worksheet two should be =Day 1 at 5PM EST, value of myCustomCalculation

I would also like the value in spreadsheet to, to recalculate work sheet 1 every day at 5PM EST in order to get the value I am looking for.

Answer 1

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.