Sorting google-sheet values by cells color directly in Apps Script

user_14413965 0
Viewed 1348

Early this year (i think) Google released the functionality to sort (and filter) by colors in google-sheet.

I wanna use this feature in my sheets, but all my sorts are done programmatically within my scripts using (for a simplified example) : SpreadsheetApp.getActive().getSheetByName("Test").getRange("A:I").sort({column 2, ascending: false}); (as in reality the parameters will be calculated outside the sort, but that's not the issue here).

I'm struggling to find documentation or examples to fill the SortSpecObj passed to the Range.sort() function to add conditions on cell color. I've tried: color, background, bgcolor, fill, fillcolor, ... but none of them seems to work.

I'm not trying to extract the values from the sheet to sort them and push them back into the sheet, and I'm also not looking to add another column to my sheet with and numerical value to do the sorting. I want to access with code to the functionality available graphically in the web application.

Or if we can override the sort function used by the class Range, but as the code is native, I don't think it will be possible, or the performances will be way too much impacted.

Thanks a lot in advance.

google-apps-script google-sheets sorting
1 Answers

I believe your goal as follows.

  • You want to sort the range by the background color of the cells using Google Apps Script.

Issue and workaround:

Unfortunately, in the current stage, it seems that sort(sortSpecObj) of Class Range cannot sort by the background colors of cells. But when Sheets API is used, I thought that your goal can be achieved. In this answer, as a workaround, I would like to propose to use "SortRangeRequest" of the method of "spreadsheets.batchUpdate" in Sheets API.

The flow of this sample script is as follows.

  1. Retrieve the background colors from the cells.
  2. Create the request body for using the batchUpdate method of Sheets API.
  3. Request to Sheets API using the request body.

Sample script:

The sample script is as follows. Please copy and paste the following script to the script editor of the container-bound script of Spreadsheet. And please enable Sheets API at Advanced Google services. By this, Sheets API can be used with Google Apps Script.

function myFunction() {
  const sheetName = "Sheet1";  // Please set the sheet name.
  const a1Notation = "A1:C10"; // 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);
  const backgrounds = range.getBackgroundObjects();
  
  // 2. Create the request body for using the batchUpdate method of Sheets API.
  const backgroundColors = Object.values(
    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 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());
}
  • In this sample, before the sort of background color is run, the column "A" is sorted with "ASCENDING".

Result:

When above script is used, the following result can be obtained.

enter image description here

Note:

  • If you want to set the order of colors, please set the array for sortSpecs like sortSpecs: [{backgroundColor: rgb1}, {backgroundColor: rgb2}, {backgroundColor: rgb3},,,].

References:

user_71086530
Related