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:AHow would I go about this?Sheet one looks like this:Index, Price, No. Of Components^DJI, 12,456, 30etc.Calculation=myCustomFormulaWorksheet two should be=Day 1 at 5PM EST, value of myCustomCalculationI 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.
Daily calculation and record insertion in Google Spreadsheet
google spreadsheets;google apps script
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-2013I'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 Ive created: LIBOR. I've added an extra menu item, so that you can execute the script this way.