Why I cannot use any methods in Google Apps Script?

I'm creating a function that checks if certain scores are equal to max score. If all of them meet this condition I want the entire range of cells to change it's background color.

function ColorsForTable(selected_scores,max_score,table_range) {
  var isMax=true;
  for(var  i=0;i<selected_scores.length;i++){
    if(selected_scores[i][0]!==max_score){
      isMax=false;
      break;
    }
  }
  if(isMax){
    table_range.setBackgroundObject('#D4AF37');
  }
}

When I run this code on my spreadsheet I get
TypeError: table_range.setBackgroundObject is not a function

I'm clearly missing something but I don't know what. Any help is highly appreciated!

From your following reply,

> I run it like this: =ColorsForTable(K35:P35 , 20 , K15:P34) (added spaces for visibility) In K35:P35 there are only numbers and in K15:P34 there are some text variables.

I understood that you are using your function ColorsForTable(selected_scores,max_score,table_range) as a custom function. In this case, there are several issues.

Modification points:

• In the case of =ColorsForTable(K35:P35,20,K15:P34), K15:P34 is the cell values. By this, table_range is not Class Range. This is the reason for your current issue of TypeError: table_range.setBackgroundObject is not a function.
• And, the argument of setBackgroundObject is SpreadsheetApp.Color object. But, you use a string #D4AF37. It is required to be setBackground('#D4AF37').
• And, in the current stage, setBackgroundObject and setBackground cannot be used with the custom function. Even when table_range is modified to Class Range object and setBackground('#D4AF37') is used, another error like Exception: You do not have permission to call setBackground occurs.
• And, in the case of the value of K35:P35, it is [[1, 2, 3,,,]]. In this case, selected_scores[i][0] is [1, 2, 3,,,]. In this case, only the cell "K35" is compared.

I thought that these points might be an answer to Why I cannot use any methods in Google Apps Script?. If you want to use this script, as a sample modification, how about running your script with the script editor? Of course, this script can be executed by the custom menu, a button on the Spreadsheet, a sidebar, and a dialog.

Modified script:

Please copy and paste the following script to the script editor of Spreadsheet and set the sheet name and save the script. The values of selected_scores, max_score, table_range are from your reply. When you use this script, please run the function sample() with the script editor. By this, the script is run.

function ColorsForTable(selected_scores, max_score, table_range) {
  var isMax = true;
  for (var i = 0; i < selected_scores.length; i++) {
    if (selected_scores[i] !== max_score) {
      isMax = false;
      break;
    }
  }
  if (isMax) {
    table_range.setBackground('#D4AF37');
  }
}

// Please run this function with the script editor.
function sample() {
  var sheetName = "Sheet1"; // Please set your sheet name.
  var selected_scores = "K35:P35";
  var max_score = 20;
  var table_range = "K15:P34";

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  ColorsForTable(
    sheet.getRange(selected_scores).getValues()[0],
    max_score,
    sheet.getRange(table_range)
  );
}

• From If all of them meet this condition I want the entire range of cells to change it's background color., in this modified script, when this script is run, when all values of "K35:P35" are the same value with 20, the background color of "K15:P34" is changed to "#D4AF37".

Note:

• This is a simple modified script for explaining the reason for your current issue. So, please modify this to your actual situation.

• As another sample script, when a sidebar is used, the sample script is as follows. In this case, please run the function main with the script editor.

  function ColorsForTable(selected_scores, max_score, table_range) {
    var isMax = true;
    for (var i = 0; i < selected_scores.length; i++) {
      if (selected_scores[i] !== max_score) {
        isMax = false;
        break;
      }
    }
    if (isMax) {
      table_range.setBackground('#D4AF37');
    }
  }
  
  function main(e) {
    if (!e) {
      var html = `<input id="selected_scores" value="K35:P35" placeholder="Please input selected_scores."> <input type="number" id="max_score" value="20" placeholder="Please input max_score."> <input id="table_range" value="K15:P34" placeholder="Please input table_range."> <input type="button" value="Run script" onclick="main()"><script>function main() {
    const values = ["selected_scores","max_score", "table_range"].map(e => document.getElementById(e).value);
    console.log(values)
    google.script.run.withSuccessHandler(_ => console.log("Done.")).main(values);
  }</script>`;
      SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput(html));
      return;
    }
    var [selected_scores, max_score, table_range] = e;
    var sheet = SpreadsheetApp.getActiveSheet();
    ColorsForTable(
      sheet.getRange(selected_scores).getValues()[0],
      Number(max_score),
      sheet.getRange(table_range)
    );
  }
  

  • When the function main is run, a sidebar is opened to the Spreadsheet. In this sample, the values of your sample have already been set. When you click a button, the script is run. And, ColorsForTable is run with valid values.

• For example, if you want to set other color when isMax is false, please modify ColorsForTable as follows.

  • From

    if (isMax) {
      table_range.setBackground('#D4AF37');
    }
    

  • To

    table_range.setBackground(isMax ? '#D4AF37' : '#ffffff'); // Please set '#ffffff' to your expected color.
    

References:

• Custom Functions in Google Sheets
• getRange(a1Notation) of Class Sheet
• Class Range