Google Apps Script中UrlFetchApp提交Google表单报错求助

我尝试通过Google Apps Script把Google表格数据导入Google表单，按YouTube教程生成了表单提交的完整URL，但调用UrlFetchApp.fetch()时出错。已用匿名模式测试公开表单，问题依旧，相关代码如下：

function autoEntry() {
 
  var wrkBk = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/17O3MhqcWIcdq3SlaR-cdr2Y6t3Mr1EWJ9E6AWh_qamo/edit?pli=1#gid=0');
  var wrkSht = wrkBk.getSheetByName("Sheet1");

  var formURL = ""
  var formData = ""

  var JenisKelamin = ""
  var Usia = ""
  var PendidikanTerakhir = ""
  var Pekerjaan = ""
  var Penghasilan = ""
  var TransaksiSering = ""
  var LamaGuna = ""
  var Frekuensi = ""
  var Informasi = ""
  var Aplikasi = ""
  var X11 = ""
  var X12 = ""
  var X13 = ""
  var X14 = ""
  var X15 = ""
  var X16 = ""
  var X21 = ""
  var X22 = ""
  var X23 = ""
  var X24 = ""
  var X25 = ""
  var X26 = ""
  var Y1 = ""
  var Y2 = ""
  var Y3 = ""
  var Y4 = ""
  var Y5 = ""
  var Y6 = ""
  var Y7 = ""

  var noOfRows = 409;

  for (i=2;i<=noOfRows;i++)
  {
    JenisKelamin = wrkSht.getRange("A"+i).getDisplayValue();
    Usia = wrkSht.getRange("B"+i).getDisplayValue();
    PendidikanTerakhir = wrkSht.getRange("C"+i).getDisplayValue();
    Pekerjaan = wrkSht.getRange("D"+i).getDisplayValue();
    Penghasilan = wrkSht.getRange("E"+i).getDisplayValue();
    TransaksiSering = wrkSht.getRange("F"+i).getDisplayValue();
    LamaGuna = wrkSht.getRange("G"+i).getDisplayValue();
    Frekuensi = wrkSht.getRange("H"+i).getDisplayValue();
    Informasi = wrkSht.getRange("I"+i).getDisplayValue();
    Aplikasi = wrkSht.getRange("J"+i).getDisplayValue();
    X11 = wrkSht.getRange("K"+i).getDisplayValue();
    X12 = wrkSht.getRange("L"+i).getDisplayValue();
    X13 = wrkSht.getRange("M"+i).getDisplayValue();
    X14 = wrkSht.getRange("N"+i).getDisplayValue();
    X15 = wrkSht.getRange("O"+i).getDisplayValue();
    X16 = wrkSht.getRange("P"+i).getDisplayValue();
    X21 = wrkSht.getRange("Q"+i).getDisplayValue();
    X22 = wrkSht.getRange("R"+i).getDisplayValue();
    X23 = wrkSht.getRange("S"+i).getDisplayValue();
    X24 = wrkSht.getRange("T"+i).getDisplayValue();
    X25 = wrkSht.getRange("U"+i).getDisplayValue();
    X26 = wrkSht.getRange("V"+i).getDisplayValue();
    Y1 = wrkSht.getRange("W"+i).getDisplayValue();
    Y2 = wrkSht.getRange("X"+i).getDisplayValue();
    Y3 = wrkSht.getRange("Y"+i).getDisplayValue();
    Y4 = wrkSht.getRange("Z"+i).getDisplayValue();
    Y5 = wrkSht.getRange("AA"+i).getDisplayValue();
    Y6 = wrkSht.getRange("AB"+i).getDisplayValue();
    Y7 = wrkSht.getRange("AC"+i).getDisplayValue();

    formURL = "https://docs.google.com/forms/d/e/1FAIpQLSchR5C98u79wi42-efVxKv3_epKSdpFklX1bMzC1dJbS2nmOQ/formResponse?&pageHistory=0,1,2,3"
    formData = "&entry.648709615="+ JenisKelamin + "&entry.2126511656=" + Usia + "&entry.26299417=" + PendidikanTerakhir + "&entry.1006337461=" + Pekerjaan + "&entry.605483759=" + Penghasilan + "&entry.696686468=" + TransaksiSering + "&entry.431572043=" + LamaGuna + "&entry.1848702501=" + Frekuensi + "&entry.2074200768=" + Informasi + "&entry.2141675646=" + Aplikasi + "&entry.1532148781=" + X11 + "&entry.986181026=" + X12 + "&entry.1899553718=" + X13 + "&entry.831653079=" + X14 + "&entry.2026432620=" + X15 + "&entry.77763837=" + X16 + "&entry.1791093619=" + X21 + "&entry.576102553=" + X22 + "&entry.1775027265=" + X23 + "&entry.601678288=" + X24 + "&entry.1603179306=" + X25 + "&entry.636968087=" + X26 + "&entry.812416152=" + Y1 + "&entry.1349313252=" + Y2 + "&entry.724123780=" + Y3 + "&entry.387033357=" + Y4 + "&entry.1193680077=" + Y5 + "&entry.1060716965=" + Y6 + "&entry.618773433=" + Y7 +""

    var finalURL = formURL + formData

    var options = {
      "method" : "post"
    };

    UrlFetchApp.fetch(finalURL, options);
  }

}

问题原因与修复方案 #

核心问题

1. POST请求格式错误：将参数拼在URL中同时使用POST方法，不符合HTTP规范，Google表单的POST接口要求参数放在请求体而非URL。
2. 未编码特殊字符：直接拼接字符串会导致空格、中文等特殊字符破坏请求结构。
3. 性能低下：循环中多次调用getRange()会大幅拖慢脚本运行速度。
4. 无错误处理：单次请求失败会导致整个脚本中断，无法定位具体错误行。

修复后的代码

function autoEntry() {
  var wrkBk = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/17O3MhqcWIcdq3SlaR-cdr2Y6t3Mr1EWJ9E6AWh_qamo/edit?pli=1#gid=0');
  var wrkSht = wrkBk.getSheetByName("Sheet1");
  
  // 一次性读取所有数据，提升性能
  var dataRange = wrkSht.getRange(2, 1, 408, 29); // 第2行到409行，共408行，A到AC列
  var data = dataRange.getDisplayValues();
  
  var formURL = "https://docs.google.com/forms/d/e/1FAIpQLSchR5C98u79wi42-efVxKv3_epKSdpFklX1bMzC1dJbS2nmOQ/formResponse";
  
  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    // 对参数值进行URL编码，避免特殊字符报错
    var payload = {
      "entry.648709615": encodeURIComponent(row[0]),
      "entry.2126511656": encodeURIComponent(row[1]),
      "entry.26299417": encodeURIComponent(row[2]),
      "entry.1006337461": encodeURIComponent(row[3]),
      "entry.605483759": encodeURIComponent(row[4]),
      "entry.696686468": encodeURIComponent(row[5]),
      "entry.431572043": encodeURIComponent(row[6]),
      "entry.1848702501": encodeURIComponent(row[7]),
      "entry.2074200768": encodeURIComponent(row[8]),
      "entry.2141675646": encodeURIComponent(row[9]),
      "entry.1532148781": encodeURIComponent(row[10]),
      "entry.986181026": encodeURIComponent(row[11]),
      "entry.1899553718": encodeURIComponent(row[12]),
      "entry.831653079": encodeURIComponent(row[13]),
      "entry.2026432620": encodeURIComponent(row[14]),
      "entry.77763837": encodeURIComponent(row[15]),
      "entry.1791093619": encodeURIComponent(row[16]),
      "entry.576102553": encodeURIComponent(row[17]),
      "entry.1775027265": encodeURIComponent(row[18]),
      "entry.601678288": encodeURIComponent(row[19]),
      "entry.1603179306": encodeURIComponent(row[20]),
      "entry.636968087": encodeURIComponent(row[21]),
      "entry.812416152": encodeURIComponent(row[22]),
      "entry.1349313252": encodeURIComponent(row[23]),
      "entry.724123780": encodeURIComponent(row[24]),
      "entry.387033357": encodeURIComponent(row[25]),
      "entry.1193680077": encodeURIComponent(row[26]),
      "entry.1060716965": encodeURIComponent(row[27]),
      "entry.618773433": encodeURIComponent(row[28])
    };
    
    var options = {
      "method": "post",
      "payload": payload,
      "followRedirects": false,
      "muteHttpExceptions": true // 捕获HTTP错误，方便调试
    };
    
    try {
      var response = UrlFetchApp.fetch(formURL, options);
      console.log("第" + (i+2) + "行提交状态码：" + response.getResponseCode());
    } catch (e) {
      console.error("第" + (i+2) + "行提交失败：" + e.toString());
    }
  }
}

关键优化点

1. 规范POST请求：将参数放在payload对象中，由系统自动处理请求格式，避免URL拼接错误。
2. URL编码：用encodeURIComponent()处理所有参数值，确保特殊字符正确传输。
3. 批量读取数据：一次性读取所有需要的行和列，减少getRange()调用次数，提升脚本运行效率。
4. 错误捕获：try-catch块保证单条数据提交失败不影响整体脚本，muteHttpExceptions允许查看错误详情。
5. 表单权限检查：确认表单设置为公开可提交，未启用验证码、登录验证等限制提交的功能。

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