Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active November 19, 2022 06:08
Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save tanaikech/8e531fdb4125c843b58cf7bef6165786 to your computer and use it in GitHub Desktop.
Save tanaikech/8e531fdb4125c843b58cf7bef6165786 to your computer and use it in GitHub Desktop.
Sorting Cells on Google Spreadsheet with Background colors using Google Apps Script

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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment