如何通过Apps Script将Web应用修改同步到Google Sheets？

以下是导致修改无法同步到原表格的常见问题及对应的修复方案：

1. 替换占位符URL #

前端代码中updateCell函数的fetch地址仍为占位符YOUR_APP_URL，必须替换为你部署Web应用后生成的实际URL（格式类似https://script.google.com/macros/s/xxxx/exec）。

2. 修复行号不匹配核心问题 #

当前代码通过index + 2计算行号是错误的——因为filteredData是原表格的子集，过滤后的索引和原表格的行号没有对应关系。需要在过滤数据时保留原表格的行号：

修改doGet中的数据过滤逻辑： #

var filteredData = data.map((row, index) => ({
  rowData: row,
  originalRow: index + 1 // 原表格的行号（1索引）
}))
// 排除表头行，筛选符合条件的行
.filter(item => item.originalRow !== 1 && item.rowData[2] === "/");

// 保持排序逻辑
filteredData.sort((a, b) => new Date(b.rowData[4]) - new Date(a.rowData[4]));

修改HTML生成部分的行号传递： #

遍历filteredData时，使用item.originalRow作为原表格行号：

filteredData.forEach((item) => {
  var row = item.rowData;
  var originalRow = item.originalRow;
  var date = new Date(row[4]);
  date.setHours(0, 0, 0, 0);
  var formattedDate = `${date.getDate()}.${date.getMonth() + 1}.${date.getFullYear()}`;
  var phoneNumber = row[0].toString().replace(/^(7)/, "+7");
  var isNotLead = row[21] === true;

  htmlOutput += `
    <tr>
      <td style="padding: 10px; border: 1px solid #ddd;"><a href='tel:${phoneNumber}'>${phoneNumber}</a></td>
      <td style="padding: 10px; border: 1px solid #ddd;" contenteditable='true' onBlur='updateCell(${originalRow}, 2, this.innerText)'>${row[1]}</td>
      <td style="padding: 10px; border: 1px solid #ddd;">${formattedDate}</td>
      <td style="padding: 10px; border: 1px solid #ddd;" contenteditable='true' onBlur='updateCell(${originalRow}, 21, this.innerText)'>${row[20]}</td>
      <td style="padding: 10px; border: 1px solid #ddd;"><input type='checkbox' ${isNotLead ? "checked" : ""} onclick='updateCell(${originalRow}, 22, this.checked)'></td>
    </tr>
  `;
});

3. 优化doPost的响应与错误处理 #

当前doPost返回HTML格式响应，改为JSON格式更适合前端处理，同时完善错误信息返回：

function doPost(e) {
  try {
    var data = JSON.parse(e.postData.contents);
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    
    if (data.row && data.col !== undefined) {
      sheet.getRange(data.row, data.col).setValue(data.value);
      return ContentService.createTextOutput(JSON.stringify({
        status: "success",
        message: "单元格已更新"
      })).setMimeType(ContentService.MimeType.JSON);
    } else {
      return ContentService.createTextOutput(JSON.stringify({
        status: "error",
        message: "缺少行号或列号参数"
      })).setMimeType(ContentService.MimeType.JSON);
    }
  } catch (error) {
    Logger.log("更新错误: " + error);
    return ContentService.createTextOutput(JSON.stringify({
      status: "error",
      message: error.toString()
    })).setMimeType(ContentService.MimeType.JSON);
  }
}

同步修改前端的updateCell函数： #

function updateCell(row, col, value) {
  fetch('你的实际应用URL', {
    method: 'POST',
    body: JSON.stringify({ row, col, value }),
    headers: { 'Content-Type': 'application/json' }
  })
  .then(response => response.json())
  .then(result => {
    console.log(result);
    if (result.status === "error") {
      alert('更新失败: ' + result.message);
    }
  })
  .catch(error => {
    console.error('请求错误:', error);
    alert('请求失败，请检查网络或应用配置');
  });
}

4. 检查Web应用部署配置 #

部署时必须确保：

• 执行方式：选择「我（你的邮箱地址）」，确保脚本以你的权限访问表格。
• 访问权限：根据需求选择（如「任何人，甚至匿名」），但注意安全。
• 代码修改后必须重新部署为新版本，否则修改不会生效。

5. 确认表格权限 #

如果表格不是你创建的，需确保部署时使用的邮箱拥有表格的编辑权限，或已将表格共享给该邮箱。

完整修正后的代码示例 #

function doGet() {
  var sheetName = "Sheet1";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();

  var today = new Date();
  today.setHours(0, 0, 0, 0);

  // 过滤数据并保留原行号
  var filteredData = data.map((row, index) => ({
    rowData: row,
    originalRow: index + 1
  }))
  .filter(item => item.originalRow !== 1 && item.rowData[2] === "/");

  filteredData.sort((a, b) => new Date(b.rowData[4]) - new Date(a.rowData[4]));

  var htmlOutput = `
    <h2>筛选后的记录</h2>
    <table style="width: 100%; border-collapse: collapse; font-family: Arial, sans-serif;">
      <tr>
        <th style="padding: 10px; border: 1px solid #ddd;">电话</th>
        <th style="padding: 10px; border: 1px solid #ddd;">姓名</th>
        <th style="padding: 10px; border: 1px solid #ddd;">日期</th>
        <th style="padding: 10px; border: 1px solid #ddd;">备注</th>
        <th style="padding: 10px; border: 1px solid #ddd;">非潜在客户</th>
      </tr>
  `;

  filteredData.forEach((item) => {
    var row = item.rowData;
    var originalRow = item.originalRow;
    var date = new Date(row[4]);
    date.setHours(0, 0, 0, 0);
    var formattedDate = `${date.getDate()}.${date.getMonth() + 1}.${date.getFullYear()}`;
    var phoneNumber = row[0].toString().replace(/^(7)/, "+7");
    var isNotLead = row[21] === true;

    htmlOutput += `
      <tr>
        <td style="padding: 10px; border: 1px solid #ddd;"><a href='tel:${phoneNumber}'>${phoneNumber}</a></td>
        <td style="padding: 10px; border: 1px solid #ddd;" contenteditable='true' onBlur='updateCell(${originalRow}, 2, this.innerText)'>${row[1]}</td>
        <td style="padding: 10px; border: 1px solid #ddd;">${formattedDate}</td>
        <td style="padding: 10px; border: 1px solid #ddd;" contenteditable='true' onBlur='updateCell(${originalRow}, 21, this.innerText)'>${row[20]}</td>
        <td style="padding: 10px; border: 1px solid #ddd;"><input type='checkbox' ${isNotLead ? "checked" : ""} onclick='updateCell(${originalRow}, 22, this.checked)'></td>
      </tr>
    `;
  });

  htmlOutput += `</table>
    <button onclick="saveChanges()">保存修改</button>
    <script>
      function updateCell(row, col, value) {
        fetch('你的实际应用URL', {
          method: 'POST',
          body: JSON.stringify({ row, col, value }),
          headers: { 'Content-Type': 'application/json' }
        })
        .then(response => response.json())
        .then(result => {
          console.log(result);
          if (result.status === "error") {
            alert('更新失败: ' + result.message);
          }
        })
        .catch(error => {
          console.error('请求错误:', error);
          alert('请求失败，请检查网络或应用配置');
        });
      }

      function saveChanges() {
        alert('修改已保存!');
      }
    </script>`;

  return HtmlService.createHtmlOutput(htmlOutput);
}

function doPost(e) {
  try {
    var data = JSON.parse(e.postData.contents);
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    
    if (data.row && data.col !== undefined) {
      sheet.getRange(data.row, data.col).setValue(data.value);
      return ContentService.createTextOutput(JSON.stringify({
        status: "success",
        message: "单元格已更新"
      })).setMimeType(ContentService.MimeType.JSON);
    } else {
      return ContentService.createTextOutput(JSON.stringify({
        status: "error",
        message: "缺少行号或列号参数"
      })).setMimeType(ContentService.MimeType.JSON);
    }
  } catch (error) {
    Logger.log("更新错误: " + error);
    return ContentService.createTextOutput(JSON.stringify({
      status: "error",
      message: error.toString()
    })).setMimeType(ContentService.MimeType.JSON);
  }
}

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