查找 Google Sheet 中的所有复选框 [英] Finding all checkboxes in a Google Sheet

问题描述

自从 2018 年将复选框添加到原生 Google Sheets UI 以来，开发人员一直希望以编程方式读取它们或以某种方式对待它们，例如将它们视为单选按钮"、将它们重置为未选中"或设置他们要检查".

Ever since checkboxes were added to the native Google Sheets UI in 2018, developers have wanted to programmatically read them or treat them in certain manners, such as treating them as "radio buttons", resetting them to "unchecked", or setting them to "checked".

我们如何才能最好地找到给定 Google Sheet 中的复选框，从而避免在操作其他单元格的状态时意外修改它们?

How can we best find checkboxes in a given Google Sheet, so that we avoid accidentally modifying other cells when manipulating their state?

一种方法是检查工作表上的值并将任何 true/false 值视为复选框:

One method is to inspect the values on a worksheet and treat any true/false values as checkboxes:

function getAllCheckboxes() {
  const wb = SpreadsheetApp.getActive();
  const checkboxes = [];

  wb.getSheets().forEach(function (sheet) {
    var rg = sheet.getDataRange();
    var values = rg.getValues();
    var sheetCheckBoxes = [];

    values.forEach(function (row, r) {
      row.forEach(function (val, c) {
        // Checkbox values are stored as `false` (unchecked) and `true` (checked)
        if (val === false || val === true) {
          sheetCheckBoxes.push({
            rowIndex: r,
            colIndex: c,
            value: val,
            r1c1: "R" + (r+1) + "C" + (c+1)
          });
        }
      });
    });
    if (sheetCheckBoxes.length) {
      checkboxes.push({
        name: sheet.getName(),
        sheetId: sheet.getSheetId(),
        boxes: sheetCheckBoxes
      });
    }
  });

  return checkboxes; // An array of objects describing a sheet and its checkboxes.
}

但是，这不适用于所有用例:单元格可能显示为文字 TRUE 或 FALSE，而不是复选框.上面的代码会把它当作一个来对待，因为它共享相同的值.

However, this won't work in all use cases: the cell might be displayed as the literal TRUE or FALSE, and not as a checkbox. The above code will treat it as though it is one, because it shares the same value.

推荐答案

复选框在 Google 表格应用程序中实现为 数据验证的特定类型，并且可以具有用户指定的已检查"和未检查"值--不仅仅是<代码>真和<代码>假.因此，要正确查找仅复选框的单元格，我们必须检查应用于每个单元格的数据验证类型.这可以通过两种方式在 Google Apps 脚本中完成:使用电子表格服务，或使用 Google Sheets API (v4).

Checkboxes are implemented in the Google Sheets application as a specific type of Data Validation, and can have user-specified values for "checked" and "unchecked"--not just true and false. Thus, to properly find only cells which are checkboxes, we must check the data validation type that is applied to each cell. This can be done in Google Apps Script in two manners: with the Spreadsheet Service, or with the Google Sheets API (v4).

电子表格服务方法不需要您在 Google Cloud Platform 中启用任何额外的符号标识符或启用任何 API.但是，在某些情况下，它可能不如 Sheets API 快.

The spreadsheet service method does not require you to enable any additional symbol identifiers or enable any APIs in the Google Cloud Platform. However, it may not be as fast in some cases as the Sheets API.

该脚本与问题中的脚本非常相似，不同之处在于我们必须迭代二维数据验证规则数组，而不是值数组.(如果我们不需要复选框的当前值，我们可以跳过获取values数组.)

The script is very similar to that in the question, with the difference that we must iterate the 2D data validation rule array, and not the value array. (If we don't need the current value of the checkbox, we can skip acquiring the values array.)

function getAllCheckboxesViaService() {
  const wb = SpreadsheetApp.getActive();
  const checkboxes = [];
  // The specific type of Data Validation that demarcates a UI checkbox.
  const CB = SpreadsheetApp.DataValidationCriteria.CHECKBOX;

  wb.getSheets().forEach(function (sheet) {
    var rg = sheet.getDataRange();
    var values = rg.getValues();
    var sheetCheckBoxes = [];

    var dvRules = rg.getDataValidations();
    dvRules.forEach(function (row, r) { // iterate data validations instead of values
      row.forEach(function (rule, c) {
        if (rule && rule.getCriteriaType() === CB) {
          sheetCheckBoxes.push({
            rowIndex: r,
            colIndex: c,
            r1c1: "R" + (r+1) + "C" + (c+1),
            choices: (rule.getCriteriaValues().length ? rule.getCriteriaValues() : [true, false]),
            value: values[r][c],
          });
        }
      });
    });
    if (sheetCheckBoxes.length) {
      checkboxes.push({
        name: sheet.getName(),
        sheetId: sheet.getSheetId(),
        boxes: sheetCheckBoxes
      });
    }
  });

  return checkboxes;
}

表格 API

要使用 Sheets API，必须先在应用的 Google Cloud Platform 项目中启用它.对于 Apps 脚本项目，其中之一是自动创建的，可从 Apps 脚本编辑器中的资源"菜单访问.如果您不确定如何操作，请查看高级服务指南激活 Sheets 符号 &表格 REST API.

Sheets API

To use the Sheets API, it must first be enabled in the application's Google Cloud Platform project. For Apps Script projects, one of these is automatically created and accessible from the "Resources" menu in the Apps Script Editor. Review the Advanced Services guide if you are unsure of how to activate the Sheets symbol & the Sheets REST API.

通过端点检索数据验证spreadsheets.get，当 fields 部分响应掩码包含 sheets/data/rowData/values/dataValidation 时.通常只是这个特定的字段没有用——知道相关工作表的标题、ID 以及复选框的值也很有用，所以更有用的 fields 规范是 sheets(数据(rowData(values(dataValidation,effectiveValue/boolValue))),properties(sheetId,title)).(您可以在 API/a>)

Data Validation is retrieved via the endpoint spreadsheets.get, when the fields partial response mask includes sheets/data/rowData/values/dataValidation. Generally just this specific field is not useful--it is also useful to know the associated sheet's title, ID, and perhaps the value of the checkbox, so a more useful fields specification is sheets(data(rowData(values(dataValidation,effectiveValue/boolValue))),properties(sheetId,title)). (You can experiment with valid field masks in the Google APIs Explorer)

Sheets API 中的相关数据验证类型是 BOOLEAN.我们可以通过 API 查询一次我们想要的电子表格，然后在本地检查生成的响应数据，以确定哪些单元格有复选框，哪些没有:

The relevant Data Validation type in the Sheets API is BOOLEAN. We can query our desired spreadsheet once via the API, and then locally inspect the resulting response data to determine which cells have checkboxes and which do not:

function getAllCheckboxesViaAPI() {
  const wbId = SpreadsheetApp.getActive().getId();
  const fields = "sheets(data/rowData/values("
        + "dataValidation(condition(type,values/userEnteredValue)),"
        + "effectiveValue(boolValue,numberValue,stringValue)),"
      + "properties(sheetId,title))";
  const resp = Sheets.Spreadsheets.get(wbId, {fields: fields}); // Enable before use...
  if (!resp.sheets || !resp.sheets.length)
    return [];

  const checkboxes = [];
  resp.sheets.forEach(function (sheetObj) {
    if (!sheetObj.data || !sheetObj.data.length)
      return;
    var sheetCheckBoxes = [];
    sheetObj.data.forEach(function (gridRange) {
      gridRange.rowData.forEach(function (row, r) {
        row.values.forEach(function (cell, c) {
          if (cell.dataValidation && cell.dataValidation.condition
              // Require the cell to be displayed as a Checkbox.
              && cell.dataValidation.condition.type === "BOOLEAN")
          {
            sheetCheckBoxes.push({
              rowIndex: r,
              colIndex: c,
              r1c1: "R" + (r+1) + "C" + (c+1),
              choices: (cell.dataValidation.condition.values ?
                  cell.dataValidation.condition.values : [true, false]),
              value: cell.effectiveValue // object, e.g. {booleanValue: false} or {stringValue: "Yes"}
            });
          }
        });
      });
    });
    checkboxes.push({
      name: sheetObj.properties.title,
      sheetId: sheetObj.properties.sheetId,
      boxes: sheetCheckBoxes
    });
  });

  return checkboxes;
}

有效地使用复选框位置

一旦知道电子表格中的哪些单元格对应于复选框 - 以及哪些值显示为已选中"与未选中" - 很自然地想要读取或修改它们.盲目地编写 true 或 <复选框单元格的代码>false 仅对默认(布尔值)复选框有效.要处理所有可能的用户创建的复选框，您必须编写适当的值，表示已选中"或未选中".(上述脚本将这些值存储在 choices 属性中.)

Using the checkbox locations efficiently

Once one knows which cells in a Spreadsheet correspond to checkboxes - and which values display as "checked" vs "unchecked - it is natural to want to read or modify them. Blindly writing true or false to the checkbox cell is only valid for default (boolean) checkboxes. To handle all possible user-created checkboxes, you must write the appropriate value that means "checked" or "unchecked." (The above scripts store these values in the choices property.)

使用 RangeList 类，虽然 Sheets API 端点 spreadsheets.values.batchUpdate 可以达到类似的结果(是的，R1C1 表示法对于 Sheets API ValueRange 规范)，尽管使用了一些样板来构建请求.API 方法能够发出单个请求，而电子表格服务只能实例化每个工作表的单个 RangeList(并且您需要为每种类型创建 1 个 RangeList复选框，以避免写入不正确的值(例如 false 当未选中"值应为 No")).

Resetting values is most easily done in Apps Script with the RangeList class, though the Sheets API endpoint spreadsheets.values.batchUpdate can achieve similar results (yes, R1C1 notation is acceptable for the Sheets API ValueRange specification), albeit with some boilerplate to construct the request. The API approach is able to issue a single request, while the Spreadsheet Service can only instantiate a single RangeList per sheet (and you'll need to create 1 RangeList per type of checkbox, to avoid writing incorrect values (e.g. false when the "unchecked" value should be "No")).

function getSpecificCBType(checkboxData, checkedVal, uncheckedVal) {
  const desiredCBs = checkboxData.filter(function (sheetObj) {
    return sheetObj.boxes.some(function (checkbox) {
      return checkbox.choices[0] === checkedVal && checkbox.choices[1] === uncheckedVal;
    });
  }).reduce(function (acc, sheetObj) {
    var desiredSheetCBs = sheetObj.boxes.filter(function (checkbox) {
      return checkbox.choices[0] === checkedVal && checkbox.choices[1] === uncheckedVal;
    });
    if (desiredSheetCBs.length) {
      acc.push({
        name: sheetObj.name,
        sheetId: sheetObj.sheetId,
        boxes: desiredSheetCBs
      });
    }
    return acc;
  }, []);
  return desiredCBs;
}


function resetSomeCBsViaService() {
  const allCBs = /* method from above */;
  const checkedValue = true;
  const uncheckedValue = false;
  const someCBs = getSpecificCBType(allCBs, checkedValue, uncheckedValue);    
  const wb = SpreadsheetApp.getActive();

  // Set to checked, using a RangeList (could use Sheets API values#batchUpdate).
  someCBs.forEach(function (sheetObj) {
    wb.getSheetByName(sheetObj.name)
      .getRangeList(sheetObj.boxes.map(function (checkbox) { return checkbox.r1c1; }))
      .setValue(checkedValue);
  });
}

要从 someCBs 构建 API 请求，这样的事情就足够了:

To build the API request from someCBs, something like this would suffice:

function resetSomeCBsViaAPI() {
  const allCBs = /* method from above */;
  const checkedValue = true;
  const uncheckedValue = false;
  const someCBs = getSpecificCBType(allCBs, checkedValue, uncheckedValue);    
  const wbId = SpreadsheetApp.getActive().getId();

  const rq = someCBs.reduce(function (rqb, sheetObj) {
    var valueRanges = sheetObj.boxes.map(function (checkbox) {
      return {
        range: "'" + sheetObj.name + "'!" + checkbox.r1c1,
        values: [ [checkedValue] ]
      };
    });
    Array.prototype.push.apply(rqb.data, valueRanges);
    return rqb;
  }, {valueInputOption: "USER_ENTERED", data: []});

  Sheets.Spreadsheets.Values.batchUpdate(rq, wbId);
}

这篇关于查找 Google Sheet 中的所有复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！