如何通过Google Sheets API自动导入所有命名范围并按块分类？

实现思路 #

1. 批量获取表格内所有命名范围
2. 按blockX_nameY的命名格式拆分块名与子名称
3. 动态构建嵌套对象存储对应数据

完整代码示例 #

function importNamedRangesIntoBlocks() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  const allNamedRanges = activeSheet.getNamedRanges();
  const blockData = {};

  allNamedRanges.forEach(rangeItem => {
    const rangeName = rangeItem.getName();
    // 拆分命名为块名和子项名称，仅处理符合"blockX_nameY"格式的命名
    const nameParts = rangeName.split('_');
    if (nameParts.length !== 2) return;

    const [blockKey, subKey] = nameParts;
    const rangeValues = rangeItem.getRange().getValues();

    // 初始化块对象，不存在则新建
    if (!blockData[blockKey]) {
      blockData[blockKey] = {};
    }
    // 将数据存入对应块的子属性
    blockData[blockKey][subKey] = rangeValues;
  });

  // 可根据需求使用blockData对象，比如打印日志或后续业务处理
  console.log(blockData);
  return blockData;
}

关键代码说明 #

• getNamedRanges()：一次性获取当前表格所有命名范围集合，彻底替代手动逐个输入范围名称的操作
• split('_')：按下划线拆分命名，自动分离blockX和nameY两部分，同时过滤不符合格式的命名范围
• 嵌套对象构建：通过判断块名是否存在，动态创建层级结构，最终形成blockData.block1.name1这样的可直接调用的分块对象
• getValues()：获取命名范围的原始数据（二维数组），如果需要显示文本可替换为getDisplayValues()，需要公式则用getFormulas()

使用方式 #

1. 打开目标Google表格，点击「扩展程序」→「Apps脚本」
2. 将上述代码粘贴到脚本编辑器中
3. 运行importNamedRangesIntoBlocks函数，完成授权后即可生成分块对象
4. 可在脚本编辑器的「日志」中查看输出结果，或在代码中添加逻辑将数据用于其他业务场景

内容的提问来源于stack exchange，提问作者Co0n