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());
}

CodePudding user response：

From your showing script, I thought that you might have used my answers

After color assignment:

After color sort: