使用Google Apps Script通过HTTP POST向Google Sheets添加JSON数据失败

目标 #

通过HTTP POST请求传递JSON数据，将多行数据批量追加到Google Sheets中。

问题复现 #

• 在Google Sheets中创建名为"test log"的工作表；
• 编写Apps Script接收HTTP POST请求中的多条JSON记录，并将每条记录追加到工作表新行；
• 运行cURL发起HTTP POST请求，尝试将JSON数据追加到工作表。

执行结果 #

• Apps Script报错：Sorry, unable to open the file at this time. Please check the address and try again.；
• 请求完成后，工作表仅被添加一行空数据。

前提说明 #

该Apps Script已部署为允许匿名用户访问的Web应用。

已尝试的无效操作 #

• 将App Script所有者改为匿名用户排查账号问题；
• 打开无痕窗口打开Google Sheets后运行cURL；
• 将POST请求中的多条JSON记录改为单条；
• 尝试Stack Overflow等平台的同类问题解决方案。

核心问题分析 #

1. JSON数组未遍历处理：当前代码直接把POST过来的JSON数组当成单个对象解析，导致data.date这类字段取值全为undefined，最终插入空行。
2. 表头映射不匹配：代码中用的是合并表头Energy Consumed (kWh)，但实际Sheet里是拆分的Energy和Consumed (kWh)两列，数据无法正确对应写入。
3. 文档获取方式不稳定：SpreadsheetApp.getActiveSpreadsheet()在匿名Web应用场景下容易触发权限问题，改用指定文档ID的方式更可靠。

修正后的代码 #

var sheetName = "test log";
// 替换为你的Google Sheets文档ID（从URL中提取，格式类似1AbCdEfGhIjKlMnOpQrStUvWxYz1234567890）
var spreadsheetId = "你的文档ID";

function doGet(e) {
    return ContentService.createTextOutput("GET请求处理成功").setMimeType(ContentService.MimeType.TEXT);
}

function doPost(e) {
  try {
    // 用openById确保匿名访问时能稳定获取文档
    var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
    if (!sheet) throw new Error("未找到指定工作表");

    var jsonData = e.postData.contents;
    var dataArray = JSON.parse(jsonData);
    if (!Array.isArray(dataArray)) throw new Error("请求数据不是JSON数组");

    // 按实际Sheet的表头顺序构造数据行
    var orderedHeaders = ["Date", "Panel", "Energy", "Consumed (kWh)", "Meter ID", "Gateway Poll Result", "1st Register Value", "2nd Register Value"];
    var rows = [];

    dataArray.forEach(function(item) {
      var row = [];
      orderedHeaders.forEach(function(header) {
        // 根据实际字段映射取值，这里处理Energy和Consumed (kWh)的拆分逻辑
        switch(header) {
          case "Date":
            row.push(item.Date || "");
            break;
          case "Energy":
            var energyVal = item["Energy Consumed (kWh)"] || "";
            row.push(energyVal.split(".")[0] || ""); // 拆分整数部分
            break;
          case "Consumed (kWh)":
            var energyVal = item["Energy Consumed (kWh)"] || "";
            row.push(energyVal.includes(".") ? "." + energyVal.split(".")[1] : ""); // 拆分小数部分
            break;
          case "Meter ID":
            row.push(item["Meter ID"] || "");
            break;
          case "Gateway Poll Result":
            row.push(item["Gateway Poll Result"] || "");
            break;
          case "1st Register Value":
            row.push(item["1st Register Value"] || "");
            break;
          case "2nd Register Value":
            row.push(item["2nd Register Value"] || "");
            break;
          default:
            row.push("");
        }
      });
      rows.push(row);
    });

    // 批量插入数据，比逐行插入效率更高
    if (rows.length > 0) {
      sheet.insertRowsBefore(2, rows.length);
      sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
    }

    return ContentService.createTextOutput(`成功插入${rows.length}条数据`).setMimeType(ContentService.MimeType.TEXT);
  } catch (error) {
    return ContentService.createTextOutput("处理失败：" + error.message).setMimeType(ContentService.MimeType.TEXT);
  }
}

关键配置说明（appsscript.json） #

{
  "timeZone": "Asia/Kolkata",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "ANYONE_ANONYMOUS"
  }
}

测试用cURL命令 #

curl -X POST \
-H 'Content-Type: application/json' \
-d '[{"Date":"1705599163","Energy Consumed (kWh)":"0.00","Meter ID":"1","Gateway Poll Result":"0","1st Register Value":"0","2nd Register Value":"0"},{"Date":"1705599163","Energy Consumed (kWh)":"999259.13","Meter ID":"2","Gateway Poll Result":"9","1st Register Value":"18803","2nd Register Value":"62898"},{"Date":"1705599163","Energy Consumed (kWh)":"4603.56","Meter ID":"3","Gateway Poll Result":"9","1st Register Value":"32081","2nd Register Value":"19084"},{"Date":"1705599163","Energy Consumed (kWh)":"4563.96","Meter ID":"4","Gateway Poll Result":"9","1st Register Value":"18416","2nd Register Value":"19083"},{"Date":"1705599163","Energy Consumed (kWh)":"42749.73","Meter ID":"5","Gateway Poll Result":"9","1st Register Value":"18214","2nd Register Value":"64956"},{"Date":"1705599163","Energy Consumed (kWh)":"31587.45","Meter ID":"6","Gateway Poll Result":"9","1st Register Value":"18166","2nd Register Value":"50919"},{"Date":"1705599163","Energy Consumed (kWh)":"39329.72","Meter ID":"7","Gateway Poll Result":"9","1st Register Value":"2031","2nd Register Value":"19478"},{"Date":"1705599163","Energy Consumed (kWh)":"131805.58","Meter ID":"8","Gateway Poll Result":"9","1st Register Value":"26161","2nd Register Value":"19707"},{"Date":"1705599163","Energy Consumed (kWh)":"116166.34","Meter ID":"9","Gateway Poll Result":"9","1st Register Value":"37336","2nd Register Value":"19677"},{"Date":"1705599163","Energy Consumed (kWh)":"136570.23","Meter ID":"10","Gateway Poll Result":"9","1st Register Value":"15960","2nd Register Value":"19714"},{"Date":"1705599163","Energy Consumed (kWh)":"41472.99","Meter ID":"11","Gateway Poll Result":"9","1st Register Value":"13560","2nd Register Value":"19486"},{"Date":"1705599163","Energy Consumed (kWh)":"82634.91","Meter ID":"12","Gateway Poll Result":"9","1st Register Value":"40212","2nd Register Value":"19613"},{"Date":"1705599163","Energy Consumed (kWh)":"85259.05","Meter ID":"13","Gateway Poll Result":"9","1st Register Value":"40550","2nd Register Value":"19618"},{"Date":"1705599163","Energy Consumed (kWh)":"147647.23","Meter ID":"14","Gateway Poll Result":"9","1st Register Value":"52912","2nd Register Value":"19724"}]' \
 https://script.google.com/macros/s/AKfyxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxp2Q/exec

Google Sheets实际表头 #

• Date
• Panel
• Energy
• Consumed (kWh)
• Meter ID
• Gateway Poll Result
• 1st Register Value
• 2nd Register Value
• Dummy Col 1
• Dummy Col 2

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