按单元格颜色直接在Google Apps脚本中对Google工作表值进行排序 [英] Sorting google-sheet values by cells color directly in Apps Script

问题描述

我认为，今年早些时候Google发布了该功能，可以按google-sheet中的颜色进行排序(和过滤).

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

我想在工作表中使用此功能，但是我的所有排序都是在脚本中使用来完成的(使用一个简化示例): SpreadsheetApp.getActive().getSheetByName("Test").getRange("A:I").sort({column 2, ascending: false});(实际上，参数将在排序之外进行计算，但这不是问题所在).

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).

我正在努力寻找文档或示例来填充传递给Range.sort()函数的SortSpecObj，以添加有关单元格颜色的条件.我试过了:color，background，bgcolor，fill，fillcolor，...，但是似乎都没有作用.

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.

我不尝试从工作表中提取值以对其进行排序并将其推回工作表中，并且我不也希望添加其他值列到我的工作表，并使用数值进行排序.我想用代码访问Web应用程序中以图形方式提供的功能.

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.

或者如果我们可以覆盖class Range所使用的sort函数，但是由于代码是本机代码，我认为这是不可能的，或者对性能的影响太大.

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.

非常感谢.

推荐答案

我相信您的目标如下.

• 您要使用Google Apps脚本按单元格的背景颜色对范围进行排序.

不幸的是，在当前阶段，类范围的sort(sortSpecObj)似乎无法按单元格的背景颜色进行排序.但是，当使用Sheets API时，我认为您的目标可以实现.在此答案中，作为一种解决方法，我想建议使用"SortRangeRequest" "spreadsheets.batchUpdate"方法的流程图.在Sheets API中.

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. 从单元格中检索背景颜色.
2. 创建请求主体以使用Sheets API的batchUpdate方法.
3. 使用请求正文请求到Sheets API.

示例脚本:

示例脚本如下.请复制以下脚本并将其粘贴到Spreadsheet的容器​​绑定脚本的脚本编辑器中.并且请在高级Google服务中启用Sheets API .这样，Sheets API可以与Google Apps脚本一起使用.

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

• 在该示例中，在运行背景颜色之前，列"A"被设置为"A".用升序"排序.

使用上述脚本时，可以获得以下结果.

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

• 如果要设置颜色顺序，请像sortSpecs: [{backgroundColor: rgb1}, {backgroundColor: rgb2}, {backgroundColor: rgb3},,,]一样为sortSpecs设置数组.
• If you want to set the order of colors, please set the array for sortSpecs like sortSpecs: [{backgroundColor: rgb1}, {backgroundColor: rgb2}, {backgroundColor: rgb3},,,].
• sort(sortSpecObj)
• 方法:sheetssheets.batchUpdate
• SortRangeRequest
• sort(sortSpecObj)
• Method: spreadsheets.batchUpdate
• SortRangeRequest

这篇关于按单元格颜色直接在Google Apps脚本中对Google工作表值进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！