Change the colour (green or red) of the cell depending if stock price is up today

Question

I am creating a google sheets stock tracker and the problem i have is that i create a command that change the colour of the cell that has the stock price depending if its up or down today.

For example if Amazon stock is up on Monday to make the cell green, and if its down on Monday to make the cell red. I want the cell to become green or red not the numbers inside that cell.

i use the =GOOGLEFINANCE('Ticker';"price") for the custom cell.

Can anyone help me?

Answer 1

You can make use of Apps Script's time-based triggers.

So depending on how often you'd like to check the value from the custom cell, you can create a time-based trigger which runs every day at 8AM for instance. Then, after checking the retrieved value from the GOOGLEFINANCE function, you can color the cell accordingly.

Code

function checkValue() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange('A1');
  var gfval = range.getValue();
  if (gfval < VALUE) 
    range.setBackground('RED');
  else
    range.setBackground('GREEN');
}


function createTrigger() {
  ScriptApp.newTrigger('checkValue')
      .timeBased()
      .atHour(8)
      .everyDays(1) 
      .create();
}

The script above is composed of two functions:

• checkValue which is used to check the value returned from the GOOGLEFINANCE function and based on it it colors the cell accordingly;

• createTrigge which is used to create a time-based trigger which runs everyday at 8AM;

Reference

• Apps Script Installable Triggers.