在Google表格中查找所有复选框 [英] 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表格中找到复选框，以免在处理其他单元格时避免意外修改其他单元格？

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中实现作为特定类型的数据验证的表格应用，和可以具有用户指定的已检查和未检查 值-不仅是 true 和 false 。因此，要正确找到仅 个复选框，我们必须检查应用于每个单元格的数据验证类型。可以通过两种方式在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.

该脚本与问题中的脚本非常相似，不同之处在于我们必须迭代2D数据验证规则数组，而不是值数组。 （如果不需要复选框的当前值，则可以跳过获取 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;
}

Sheets API

要使用Sheets API，必须首先在应用程序的Google Cloud Platform项目中启用它。对于Apps Script项目，将自动创建其中之一，并可以通过Apps Script编辑器中的资源菜单进行访问。如果不确定如何操作，请查看高级服务指南。激活 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 ，当字段部分响应掩码包括 sheets / data / rowData / values / dataValidation 。通常，仅此特定字段是没有用的-了解相关工作表的标题，ID以及复选框的值也很有用，因此，一个更有用的 fields 规范是 sheets（data（rowData（values（dataValidation，effectiveValue / boolValue））），properties（sheetId，title））。 （您可以在 Google API Explorer ）

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.va lues.batchUpdate 可以达到类似的结果（是的，R1C1表示法对于Sheets API ValueRange 规范），尽管使用了一些样板来构造请求。 API方法能够发出单个请求，而Spreadsheet Service只能实例化每个工作表单个 RangeList （并且您需要创建1个每种复选框类型> RangeList ，以避免写入错误的值（例如，当未经检查的值应为时， false 否。

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表格中查找所有复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！