GAS下Google Visualization Query查询大表的解析报错解决与结果转二维数组实现

架构老林

2026-11-2

报错根因

手动通过indexOf("{")、lastIndexOf("}")截取响应文本的逻辑存在固有缺陷：当响应内容中嵌套出现其他大括号字符（比如单元格存储的值本身包含大括号，或接口返回权限/查询错误时的报错文本），会截取到不完整、语法非法的JSON片段，最终触发解析时报出Unexpected token的语法错误。
Google Visualization Query（gviz）接口返回的是标准JSONP格式内容，外层固定包裹google.visualization.Query.setResponse(前缀和);后缀，无法靠盲找大括号的方式稳定提取内部JSON。
原请求未携带鉴权凭证，访问非公开表格时会返回权限相关的错误响应，进一步破坏截取的JSON结构完整性。
原SQL语句中硬编码返回常量'google'时未按gviz语法要求添加列别名，本身也存在查询解析异常的风险。

修正后的可运行实现

// 基础配置
const clientFleetId = "18SEdE9o9xWK0U8Y0qnleTJ2Qk565tpNdKJWKlBuBgDU"
const clientPermitTypeSheet = "PERMIT TYPES"
const rangeA1 = "A5:F10"

function getAllTrailerPermits (){
  // 获取目标工作表ID
  const targetSheet = SpreadsheetApp.openById(clientFleetId).getSheetByName(clientPermitTypeSheet)
  const sheetGid = targetSheet.getSheetId()

  // 修正查询语句：给硬编码常量添加别名，符合gviz SQL语法要求
  const sqlText = "select A, C, F, 'google' as custom_col where E = true"

  // 拼接gviz查询地址
  const requestUrl = `https://docs.google.com/spreadsheets/d/${clientFleetId}/gviz/tq?gid=${sheetGid}&range=${rangeA1}&tq=${encodeURIComponent(sqlText)}&headers=1`

  // 发送带鉴权的请求，禁用异常中断，保证拿到完整响应内容
  const fetchResp = UrlFetchApp.fetch(requestUrl, {
    headers: {
      Authorization: `Bearer ${ScriptApp.getOAuthToken()}`
    },
    muteHttpExceptions: true
  })
  const rawRespText = fetchResp.getContentText()

  // 直接在原始响应文本上替换JSONP包裹层，提取完整合法JSON
  const jsonString = rawRespText.replace(/^google\.visualization\.Query\.setResponse\(/, '').replace(/\);$/, '')
  const parsedResp = JSON.parse(jsonString)

  // 校验查询执行状态
  if (parsedResp.status !== 'ok') {
    throw new Error(`查询执行失败：${parsedResp.errors.map(e => e.message).join('; ')}`)
  }

  // 转换为和getValues()一致的二维数组结构，空值统一替换为空字符串
  const resultList = parsedResp.table.rows.map(row => {
    return row.c.map(cell => cell?.v ?? '')
  })

  console.log(resultList)
  return resultList
}

关键注意事项

不要依赖indexOf()找大括号的方式截取JSON：这种方式在内容存在嵌套大括号时极易截取到非法片段，是本次报错的核心诱因。
所有gviz请求必须携带OAuth鉴权令牌：访问非公开共享的表格时，无鉴权的请求会被权限系统拦截，返回的错误文本没有固定结构，会直接导致JSON解析失败。
转换后的二维数组与原生getValues()返回结构完全一致，可直接替换原有全量拉取的逻辑，不需要改动后续的业务检索代码。
该方案的性能优势：所有筛选计算在Google服务端完成，仅返回符合条件的少量行，针对5万行以上的大表格，相比全量加载后本地遍历检索的方案，可将执行耗时从数分钟压缩到数秒级别。
若查询范围包含表头，需要在请求参数中添加headers=1声明首行是表头，否则列名识别会出现偏差。

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