Google-sheets – Daily calculation and record insertion in Google Spreadsheet

google sheetsgoogle-apps-script

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.

Best Answer

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

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:
enter image description here

and set the trigger to execute at 5-6 pm:
enter image description here

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

Formula

=IFERROR(QUERY(A2:C8, "SELECT C WHERE B <=" & E2 & "ORDER BY C ASC LIMIT 1"),"")

Explained

The complete range is set to be the data range. The quasi SELECT statement first filters on items that are less or equal to the selected min qty. Then the items are ordered ascendingly and limited to only one result. The IFERROR catches the error if no value has been entered.

Screenshot

enter image description here

Example

I've prepared an example file for you: price bracket

Google-sheets – Apply formula when other is modified

I do not know how to solve this problem without scripts, but there is a simple work around. When you update prices, you can drag them elsewhere in your sheet (e.g. below, in the form of a price record). The values on the 'Sales' sheet will stay linked to the original price, and the formula you enter for sales will be the same.

If you choose this work around, I would recommend creating a script that automatically moves your prices so that you don't have to manually drag things around and so that the newest prices appear closer to the top. There are many ways to make that type of script, but here's one not so elegant, but simple way:

var ss = SpreadsheetApp.getActiver();

// Many of these values depend on the layout of your document.
// I have indicated where you will need to change values if
// you change the layout.

function onOpen(){
  // This will make a menu appear that allows you to run the scripts
  var ss = SpreadsheetApp.getActive();
  var items = [
    {name: 'Make space for a new price', functionName: 'newprice'},
    {name: 'New sales day', functionName: 'newsales'},
  ];
  ss.addMenu('New Entry', items);
};

function newprice(){
  // This will simply insert a row under your prices.
  // You just have to drag the on you want to change into the new row
  // Your info on the other sheets will stay linked to the correct number
  var sheet = ss.getSheets()[1]; // This number may need editing
  sheet.insertRowBefore(7); // This number may also need attention
};

function newsales(){
  var sheet = ss.getSheets()[0];
  var where = 7;  // Where to add the new lines?
  var how_many = 5;  // How many products to add?
  for (var ind = 0; ind < how_many; ind ++){
    sheet.insertRowBefore(where);
  };
  // Copy the values
  var source = sheet.getRange("A3:G7"); // This would need attention
  var destination = sheet.getRange("A8:G12");  // And this...
  source.copyValuesToRange(destination,1,1,1,1);
};

Using a script may be more trouble than it's worth for this type of problem.

If for some reason (I can't imagine why) you want to have one cell that adds old prices and new prices, then you need a more advanced script. It would be easier to make one cell associated with each price, and then add them in the final cell.

Best Answer

Related Solutions

Google-sheets – Auto bracketed pricing in Google Spreadsheets

Formula

Explained

Screenshot

Example

Google-sheets – Apply formula when other is modified

Related Topic