CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jon
Jon

Reputation: 247

Auto updating formula

So I have a list of people along with scores that are auto-updated based on the contents of other pages. I have written a script to automatically change the background color of the highest value green. Unfortunately this appears to take effect when manually activated.

My question is this: Is there a way to have a function automatically refresh or run when its not directly in a cell?

I apologize for the possibly poorly posed question - let me attempt to clarify:

I have the code below

    function HighestScore(){

  var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("Total_Points");
  var data = range.getValues();
  var high = 0;
  var loc = 0;

  //set all backgrounds to white
  range.setBackgroundColor("#FFFFFF"); 

  for (var i = 0; i<7;i++){
    if (data[i] > high){
      high = data[i];
      loc = i
    }
    else{
      high = high;
    }
  }

//set the background color of the highest value green
      var lead = SpreadsheetApp.getActiveSpreadsheet().getRange("Totals!B"+(2+loc)).setBackgroundRGB(00,255,0)
    }

When ran manually from the script window, it works great. When run from a cell, I get a permissions error. Is there any way to make it so that when I update scores on the document, that this function automatically updates the background color of the highest score?

Upvotes: 1

Views: 1368

Answers (1)

eddyparkinson
eddyparkinson

Reputation: 3700

onEdit: use the onEdit trigger, see: https://developers.google.com/apps-script/understanding_triggers Then the code should run everytime a user changes the spreadsheet.

Upvotes: 1

Related Questions