Sorting Cells on Google Spreadsheet with Background colors using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a sample script for sorting the cells on Google Spreadsheet with the background colors using Google Apps Script.

Unfortunately, in the current stage, it seems that sort(sortSpecObj) of Class Range cannot directly sort by the background colors of cells. But when Sheets API is used, this goal can be achieved. Here, “SortRangeRequest” of the method of “spreadsheets.batchUpdate” in Sheets API is used.

Flow

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

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”.
  • When this sample script is used, the situation of above demo image can be obtained.

Note

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

  • When when this method is used, the cells can be also sorted with the font color.

References

 Share!