如何用Google Apps Script将谷歌表格列表转为指定JSON结构

核心思路 #

依据表格行的层级关系（空单元格表示继承上层父节点），遍历数据时维护当前上下文节点，区分对象、数组两种结构类型，逐步构建目标JSON结构。

转换算法实现 #

以下是可扩展的Google Apps Script代码，支持任意层级的对象嵌套与数组结构：

function convertSheetDataToJson(sheetData) {
  const result = {};
  let currentParent = null;
  let currentKey = null;
  let currentType = null; // 标记当前父节点类型：'object' 或 'array'

  for (const row of sheetData) {
    // 过滤行内空字符串，跳过纯空行
    const trimmedRow = row.filter(cell => cell !== "");
    if (trimmedRow.length === 0) continue;

    // 第一列非空：处理顶层节点或新父节点
    if (row[0] !== "") {
      currentKey = row[0];
      // 判断当前节点类型：第二列是键则为对象，是值则判断为数组/单个值
      if (row[1] && typeof row[1] === 'string' && !['true', 'false'].includes(row[1].toLowerCase())) {
        currentType = 'object';
        result[currentKey] = {};
        currentParent = result[currentKey];
        // 处理当前行的键值对
        currentParent[row[1]] = convertValue(row[2]);
      } else if (row[1]) {
        // 检查下一行是否为同数组的元素
        const nextRowIndex = sheetData.indexOf(row) + 1;
        if (nextRowIndex < sheetData.length && sheetData[nextRowIndex][0] === "") {
          currentType = 'array';
          result[currentKey] = [];
          currentParent = result[currentKey];
          currentParent.push(convertValue(row[1]));
        } else {
          // 单个值类型
          result[currentKey] = convertValue(row[1]);
          currentParent = null;
        }
      }
    } else {
      // 第一列空：继承当前父节点
      if (currentType === 'object') {
        currentParent[row[1]] = convertValue(row[2]);
      } else if (currentType === 'array') {
        currentParent.push(convertValue(row[1]));
      }
    }
  }

  return JSON.stringify(result, null, 2);
}

// 辅助函数：将单元格值转换为对应JSON类型
function convertValue(value) {
  if (typeof value === 'string') {
    const lowerVal = value.toLowerCase();
    if (lowerVal === 'true') return true;
    if (lowerVal === 'false') return false;
    // 尝试转换数字类型
    const num = Number(value);
    if (!isNaN(num)) return num;
  }
  return value;
}

使用示例 #

在你的脚本中调用该函数即可完成转换：

function runConversion() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const rawData = sheet.getDataRange().getValues();
  const targetJson = convertSheetDataToJson(rawData);
  
  // 可选：将JSON写入Google Drive文件
  const targetFolder = DriveApp.getFolderById("你的文件夹ID");
  targetFolder.createFile("output.json", targetJson, MimeType.JSON);
  
  // 或直接在日志查看结果
  console.log(targetJson);
}

关键特性 #

• 可扩展性：适配任意层级的对象嵌套与数组，只要表格层级结构（空单元格）正确即可
• 类型自动转换：将字符串型的true/false转为布尔值，数字字符串转为数字类型
• 鲁棒性：自动跳过空行，处理表格内的空字符串占位

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