如何在Apps Script中按列名读取Google Sheets导出的JSON数据

我正在尝试把Google Sheets导出的JSON作为Apps Script的数据库使用，数据获取URL格式为：https://docs.google.com/spreadsheets/d/DOCUMENTID/gviz/tq?tqx=out:json&gid=SHEETID。现有函数只能按列索引（比如C列，索引2）获取数据，返回结果为[Data 1-3, Data 2-3]，但我需要把它改成可以传入databaseUrl和headerName（比如'Emails'）的通用函数，这样就算列顺序变动，也能返回对应列的正确数据。

示例JSON结构 #

{
    "reqId": "0",
    "sig": "000",
    "status": "ok",
    "table": {
        "cols": [
            {
                "id": "A",
                "label": "",
                "type": "string"
            },
            {
                "id": "B",
                "label": "",
                "type": "string"
            },
            {
                "id": "C",
                "label": "",
                "type": "string"
            }
        ],
        "parsedNumHeaders": 0,
        "rows": [
            {
                "c": [
                    {
                        "v": "Data 1-1"
                    },
                    {
                        "v": "Data 1-2"
                    },
                    {
                        "v": "Data 1-3"
                    }
                ]
            },
            {
                "c": [
                    {
                        "v": "Data 2-1"
                    },
                    {
                        "v": "Data 2-2"
                    },
                    {
                        "v": "Data 2-3"
                    }
                ]
            },
            {
                "c": [
                    {
                        "v": "Emails"
                    },
                    {
                        "v": "Ids"
                    },
                    {
                        "v": "Names"
                    }
                ]
            }
        ]
    },
    "version": "0.6"
}

现有函数（按列索引获取） #

function getRolePermission(databaseUrl) {

  let databaseParsed = JSON.parse(UrlFetchApp.fetch(databaseUrl).getContentText().match(/(?<=.*\().*(?=\);)/s)[0]);
  let tableLength = Object.keys(databaseParsed.table.rows).length;

  let dataArray = [];
  for (let i = 0; i < tableLength; i++) {
    dataArray.push(databaseParsed.table.rows[i].c[2].v)
  }

  return dataArray;

}

通用函数实现（按表头名称获取） #

核心逻辑：先定位表头行找到目标列的索引，再提取对应列的数据，彻底摆脱固定列顺序的限制。

function getRolePermission(databaseUrl, headerName) {
  // 获取并解析Google Sheets返回的特殊格式JSON
  const response = UrlFetchApp.fetch(databaseUrl).getContentText();
  const jsonStr = response.match(/(?<=.*\().*(?=\);)/s)[0];
  const databaseParsed = JSON.parse(jsonStr);
  
  const rows = databaseParsed.table.rows;
  if (!rows || rows.length === 0) return [];
  
  // 定位表头行（示例中为最后一行，若你的表头位置不同，可调整索引值）
  const headerRow = rows[rows.length - 1].c;
  // 找到目标表头对应的列索引
  const targetColIndex = headerRow.findIndex(col => col.v === headerName);
  
  if (targetColIndex === -1) return []; // 未找到对应表头时返回空数组
  
  // 提取数据行（排除表头行）的对应列值
  const dataArray = rows.slice(0, -1).map(row => row.c[targetColIndex].v);
  
  return dataArray;
}

// 调用示例
getRolePermission('https://docs.google.com/spreadsheets/d/1lc...', 'Emails');

关键说明 #

• 处理Google Sheets返回的特殊JSON格式：需要通过正则去掉前后的包装字符才能正确解析
• 表头位置可调整：如果你的表头不是最后一行，只需修改rows.length - 1为对应行的索引即可
• 容错处理：未找到目标表头或数据为空时，返回空数组避免报错

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