Auto color percentage changes.

[ghost]

I thought it would be nice if the changes in percentage(%) would be auto colored(if it went down red, green if it went up and white if it stayed the same.) So I made a function for it myself. I call this method in the getData method.

function color_percentage(){
   var rates = SpreadsheetApp.getActive().getSheetByName('Rates');
   var lastRowNumber = rates.getLastRow();

  //Get all percentage changes(1 hour, 24 hour and 7 days).
   var one_hour =  rates.getRange('L2:L' + lastRowNumber).getValues();
   var tf_hour = rates.getRange('M2:M' + lastRowNumber).getValues();
   var z_day = rates.getRange('N2:N' + lastRowNumber).getValues();

  //Add all into a list.
  var list = [one_hour, tf_hour, z_day];

  //This counter is needed to get the specific column(12, 13 and 14 these represent 1 hour, 24 hour and 7 days)
    var counter = 12;
  for(j=0; j< list.length; j++){
     for(i = 0; i< lastRowNumber -1; i++){
     if(list[j][i][0] < 0){
       rates.getRange(2+i, counter).setBackground("Red");
     }else if(list[j][i][0] === 0){
        rates.getRange(2+i, counter).setBackground("White");
     }else{
        rates.getRange(2+i, counter).setBackground("Green");
     }

   }
    counter++;

  }
}

Feel free to use it or dismiss this issue.

[LesterCovax]

There's conditional formatting built into Google Sheets if you weren't aware. Pretty cool the way you did it though. I haven't shared my actual template, but the work so far was just meant to populate a sheet to use in another sheet. I'll definitely keep it in mind though.

[ddarbs]

I have mine set up for conditional formatting as well. Don't think it would be a good idea to try to make the sheet from the script as saitei said.

[ghost]

Thank you for the feedback I will also switch to the conditional formatting in GS.

[LesterCovax]

I think it would be a good option when I get around to auto generating the presentation layer. It could be a toggle. I'd want to interface directly with the conditional formatting though instead of manually setting backgrounds.

Looks like you can do it via Sheets.newConditionValue() and Sheets.newCellFormat() https://developers.google.com/sheets/api/guides/conditional-format