How to Run this code for the active Sheet Instead of Single Sheet-CodePudding

I am trying to make this code run for active Sheet but the problem is when i changed the activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1') to activeSheet = SpreadsheetApp.getActiveSpreadsheet() it is not working.

So what i did i created 4 codes for each sheet separatly. I hope someone can help me to make it Run for the active sheet.

you rhelp will be much appreciated.

Error is TypeError: activeSheet.getMaxRows is not a function

function numberColors() {
  
  var colorCol = 15,     
      numberCol = 39,    
      firstDataRow = 2, 

      palette = [
        "#ffffff",  // Default 
        "#ff9900"  // Orange 8
        "#00b0f0", // Light Blue 1
        "#ffff00", // yellow 4 
        "#93c47d", // Green 7
        "#00ff00", // Light Green 5
        "#9900ff", // Purple 6
        "#ff0000", // Red 3
        "#5f5f5f", // Grey 2     
      ];

  var numbers = [],
      activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')

      dataNumRows = activeSheet.getMaxRows()-(firstDataRow-1),
      colorColColors = activeSheet.getRange(2, colorCol, dataNumRows, 1).getBackgrounds();

  for (var rowIndex in colorColColors) {
    numbers[rowIndex] = [];

    var rowColor = colorColColors[rowIndex].toString(),
        colorIndex = palette.indexOf(rowColor);

    if (colorIndex == -1) {
      numbers[rowIndex].push(rowColor); 

    } else if (colorIndex == 0) {
      numbers[rowIndex].push(""); 

    } else {
      numbers[rowIndex].push(colorIndex);
    }
  }
  activeSheet.getRange(2, numberCol, dataNumRows, 1).setValues(numbers);

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).sort(39);

}

CodePudding user response：

Well, your issue here is that after your modification, with SpreadsheetApp.getActiveSpreadsheet() you are retreiving a Spreadsheet and not a single Sheet, which is what was getting retrieved via SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'). Then, the program is trying to read the maxRows of the Spreadsheet, which is a property that only Sheets have.

To get the active Sheet of the active Spreadsheet, use the following:

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

You can read more about getActiveSheet() here.