使用脚本在Google表格的下拉菜单中打印所有选项 [英] Print all options in Drop Down in Google Sheet using Script

问题描述

有没有人可以使用Google表格中的应用脚本在下拉菜单中打印所有选项的任何脚本?

Does any one have any script where I can print all the options in Drop Down using App Script in Google Sheet?

推荐答案

我相信您的目标如下.

• 您要使用Google Apps脚本从下拉列表类型的DataValidation中检索选项值.

在这种情况下，我认为 getDataValidation()和 getCriteriaValues()可以用于实现您的目标.

In this case, I think that getDataValidation() and getCriteriaValues() can be used for achieving your goal.

在此示例脚本中，从"A1"单元格中的DataValidation中检索选项值.

In this sample script, the option values are retrieved from the DataValidation in the cell "A1".

const sheet = SpreadsheetApp.getActiveSheet();
const dataValidation = sheet.getRange("A1").getDataValidation();
const check = [SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE, SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST];
if (dataValidation && check.includes(dataValidation.getCriteriaType())) {
  const [criteria] = dataValidation.getCriteriaValues();
  const res = criteria.toString() == "Range" ? criteria.getValues() : criteria;
  console.log(res)
}

注意:

• 如果要从范围内的多个DataValidation中检索选项值，则还可以使用以下脚本.

  Note:

  • If you want to retrieve the option values from multiple DataValidations in the range, you can also use the following script.

      const sheet = SpreadsheetApp.getActiveSheet();
      const dataValidations = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).getDataValidations();
      const check = [SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE, SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST];
      const res = dataValidations.reduce((ar, r, i) => {
        r.forEach((c, j) => {
          if (c && check.includes(c.getCriteriaType())) {
            const [criteria] = c.getCriteriaValues();
            ar.push({row: i + 1, column: j + 1, values: criteria.toString() == "Range" ? criteria.getValues() : criteria});
          }
        });
        return ar;
      }, []);
      console.log(res)
    

  • 对于 getDataValidations()，当下拉列表具有空值时， getDataRange()无法检索范围.因此，在此示例中，使用了 getMaxRows()和 getMaxColumns().这样，上面的第二个脚本可以检索工作表中下拉列表类型的所有DataValidation.

  • In the case of getDataValidations(), when the drop down list has empty value, getDataRange() cannot retrieve the range. So in this sample, getMaxRows() and getMaxColumns() are used. By this, above 2nd script can retrieves all DataValidations of the dropdown list type in a sheet.

    • getDataValidation()
    • 数据验证类
    • getDataValidations()

    这篇关于使用脚本在Google表格的下拉菜单中打印所有选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！