How to sort sheet by cell’s background color – have the code, but don’t understand the error

I’m trying to finish this project but the last part is holding me up for 2 days now. I have a Google Sheet, very simple, and I just need it to auto-sort by cell’s background color. I have 7 colors that I need in this order, and I have tried using the Sort Range Plus extension, which does work, but I can’t figure out how to call it with predecided parameters whenever the sheet is edited. So then I found an Apps Script that is supposed to do exactly what I need, and I think I enabled the Sheets API first, like the forums said to do, and I had to do this in two places, I believe, but still when I run the following code, I keep getting errors on Lines 25/26:

Error: Exception: Object is not of type RgbColor.
(anonymous) @ SortByColor.gs:26
(SortByColor) @ SortByColor.gs:25

I’m not sure how to fix this, as it runs and gets colors and then errors out. I’ve not used javascript before, so I’m hoping someone more familiar can help me. Maybe the issue is I enabled it incorrectly or something? If other people have no issue with the same code, I’m thinking I may have done it wrong. It’s also my first time using Google Apps Script.

Here is the code I’ve been trying. Please excuse the commented out parts – I’m using code I found in 2 different threads.

function SortByColor() {
    const sheetName = "Patient Progress"; // Please set the sheet name.
    //const a1Notation = "A1:A1099"; // Please set the sort range as a1Notation.

    // 1. Retrieve the background colors from the cells.
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(sheetName);
    //const range = sheet.getRange(a1Notation);
    var range = sheet.getRange(2, 1, ss.getLastRow(), ss.getLastColumn());
    const backgrounds = range.getBackgroundObjects();

    // 2. Create the request body for using the batchUpdate method of Sheets API.
    /** 
    const startRow = range.getRow() - 1;
    const startColumn = range.getColumn() - 1;
    const srange = {
    sheetId: sheet.getSheetId(),
    startRowIndex: startRow,
    endRowIndex: startRow + range.getNumRows(),
    startColumnIndex: startColumn,
    endColumnIndex: startColumn + range.getNumColumns(),
    };**/

    const sortColorOrder = ['#ea9999', '#f9cb9c', '#fff2cc', 'd9ead3', '#cfe2f3', '#d9d2e9', 
    '#fffff' ]; // This means that "red", "orange", "yellow", "green", "blue", "purple", 
    "white" in order.
    const backgroundColorObj = backgrounds.reduce((o, [a]) => {
      const rgb = a.asRgbColor()
      return Object.assign(o, { [rgb.asHexString()]: { red: rgb.getRed() / 255, green: 
        rgb.getGreen() / 255, blue: rgb.getBlue() / 255 } })
      }, {});
    const backgroundColors = sortColorOrder.map(e => backgroundColorObj[e]);

    const requests = [
      {
    sortRange: {
      range: srange,
      sortSpecs: [{ dimensionIndex: 0, sortOrder: "ASCENDING" }],
      },
    },
    {
    sortRange: {
      range: srange,
       //sortSpecs: [{backgroundColor: '#d9d2e9'}, {backgroundColor: '#d9ead3'}, 
      {backgroundColor: '#fff2cc'}]
      sortSpecs: backgroundColors.map((rgb) => ({ backgroundColor: rgb })),
    },
    },
    ];

    // 3. Request to Sheets API using the request body.
    Sheets.Spreadsheets.batchUpdate({ requests: requests }, ss.getId());
}

Answer

From your showing script, I thought that you might have used my answers https://stackoverflow.com/a/64273384 and https://stackoverflow.com/a/71554246, how about the following modification?

Modification points:

• In your script, srange is not declared.
• The lines of "white" in order. and {backgroundColor: '#fff2cc'}] are used as a line of a script instead of a comment.
• You set the hex color of '#fffff'. In this case, I think that it’s '#ffffff'. If '#fffff' is used, the white background is not sorted. Please be careful this. About var range = sheet.getRange(2, 1, ss.getLastRow(), ss.getLastColumn());, this has already been mentioned by Rubén’s comment. Ref

When these points are refected to the sample script, it becomes as follows.

I cannot know the background colors of cells in your actual Spreadsheet. So in this modification, I supposes that you are using the background colors of ['#ea9999', '#f9cb9c', '#fff2cc', 'd9ead3', '#cfe2f3', '#d9d2e9', '#ffffff'] at the column “A”. Please be careful this.

Modified script:

Please confirm whether Sheets API has already been enabled, again.

function myFunction() {
  const sheetName = "Patient Progress"; // Please set the sheet name.

  // 1. Retrieve the background colors from the cells.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const range = sheet.getRange(2, 1, ss.getLastRow() - 1, ss.getLastColumn());
  const backgrounds = range.getBackgroundObjects();

  // 2. Create the request body for using the batchUpdate method of Sheets API.
  const sortColorOrder = ['#ea9999', '#f9cb9c', '#fff2cc', 'd9ead3', '#cfe2f3', '#d9d2e9', '#ffffff']; // This means that "red", "orange", "yellow", "green", "blue", "purple", "white" in order.
  const backgroundColorObj = backgrounds.reduce((o, [a]) => {
    const rgb = a.asRgbColor();
    const hex = rgb.asHexString();
    return Object.assign(o, { [hex == "#ffffffff" ? "#ffffff" : hex]: { red: rgb.getRed() / 255, green: rgb.getGreen() / 255, blue: rgb.getBlue() / 255 } });
  }, {});
  const backgroundColors = sortColorOrder.map(e => backgroundColorObj[e] || "").filter(String);
  if (backgroundColors.length == 0) return;

  const startRow = range.getRow() - 1;
  const startColumn = range.getColumn() - 1;
  const srange = {
    sheetId: sheet.getSheetId(),
    startRowIndex: startRow,
    endRowIndex: startRow + range.getNumRows(),
    startColumnIndex: startColumn,
    endColumnIndex: startColumn + range.getNumColumns()
  };
  const requests = [
    { sortRange: { range: srange, sortSpecs: [{ dimensionIndex: 0, sortOrder: "ASCENDING" }] } },
    { sortRange: { range: srange, sortSpecs: backgroundColors.map(rgb => ({ backgroundColor: rgb })) } }
  ];

  // 3. Request to Sheets API using the request body.
  Sheets.Spreadsheets.batchUpdate({ requests: requests }, ss.getId());
}

Note:

• Unfortunately, I cannot know the background colors in your actual Spreadsheet. So in your actual Spreadsheet, when the background colors except for the colors of ['#ea9999', '#f9cb9c', '#fff2cc', 'd9ead3', '#cfe2f3', '#d9d2e9', '#ffffff'] are used, those colors are ignored. Please be careful this.

• As an important point, when getBackgroundObjects() is used, I confirmed that the hex value of white background is #ffffffff. I’m not sure whether this is a bug or the current specification. So in this case, #ffffffff is modified to #ffffff.

References:

• Related thread.
  • Sorting google-sheet values by cells color directly in Apps Script
  • Sort values by color in Google Sheets by using google APP Script