Google Sheets单元格编辑时App Script无法向本地服务器发送请求

Google Apps Script运行在谷歌云端服务器，无法直接访问本地localhost服务——localhost是仅本地局域网可见的地址，云端环境不在你的内网中，因此无法建立连接，这是核心问题。

解决步骤 #

1. 用内网穿透工具暴露本地服务 #

使用ngrok、localtunnel等工具将本地3000端口映射到公网：

• 安装ngrok后，执行命令：ngrok http 3000
• 复制生成的公网域名（如https://abc123.ngrok.io），替换Apps Script中的url为：https://abc123.ngrok.io/user/edit

2. 排查Apps Script请求状态 #

在代码中添加日志，确认请求是否发送或报错：

function onEdit(e) {
  const url = "替换为你的公网穿透地址/user/edit";
  const options = {
    "method": "POST",
    "muteHttpExceptions": true
  };
  try {
    const response = UrlFetchApp.fetch(url, options);
    console.log("请求状态码:", response.getResponseCode());
    console.log("响应内容:", response.getContentText());
  } catch (err) {
    console.error("请求错误:", err.toString());
  }
}

查看日志路径：Apps Script编辑器右上角「查看」→「日志」

3. 确认onEdit触发条件 #

• onEdit是简单触发，仅在手动编辑单元格时触发，脚本/API编辑不会触发
• 确保编辑的是你有权限操作的表格范围

原问题代码 #

Apps Script代码

function onEdit(e) {
   const url = "http://localhost:3000/user/edit";
  const options = {
    "method": "POST",
    "muteHttpExceptions": true
  };
  UrlFetchApp.fetch(url, options);
}

服务器端代码

const express = require("express");
const app = express();
const mongoose = require("mongoose");
const { ObjectId } = require("mongodb");
const { urlencoded } = require("express");
const { google } = require("googleapis");
require("dotenv").config();
const User = require("./Models/user");

app.use(urlencoded({ limit: "50mb", extended: true }));
app.use(express.json({ limit: "50mb", extended: true }));

app.use((req, res, next) => {
  res.setHeader("Access-Control-Allow-Origin", "*");
  res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
  next();
});

app.post("/user/edit", (req,res) => {
  console.log("Hello");
})

//db connection
mongoose
  .connect(process.env.MONGODB_URL, {
    useNewUrlParser: true,
    useUnifiedTopology: true,
  })
  .then(() => {
    console.log("Successfully connected to the database");
  })
  .catch((error) => {
    console.log("Could not connect to the database", error);
    process.exit();
  });

require("./Router/userRoute")(app);

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Listening on port ${PORT}`);
});

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