Google Script调用setDestination报错 无法将Google Form回应同步到指定Sheet

错误原因 #

• setDestination方法传入的第二个参数不符合要求：当目标类型为FormApp.DestinationType.SPREADSHEET时，需要传入整个Google Sheets文件的ID，而非单个工作表的ID
• 你代码中使用sheet.getSheetId()获取的是单个工作表在所属文件内的内部编号，不属于有效表格文件ID，因此触发参数异常。

修复代码 #

你只需要修改ID获取逻辑即可，完整修改后代码如下：

function bug() {
  var spreadSheet = SpreadsheetApp.getActive();
  var sheet = spreadSheet.getSheetByName('TC');
  var value = sheet.getRange("A3").getValue();
  var em = sheet.getRange("B3").getValue();
  var cos = sheet.getRange("E3").getValue();
  var name = sheet.getRange("D3").getValue();
  var sup = sheet.getRange("C3").getValue();

  var form = FormApp.create(value);
  var item = form.addMultipleChoiceItem();
  item.setHelpText("Name: " + name +
                 "\n\nEmail: " + em +
                 "\n\nQuote No: " + value +
                 "\n\nProject: " + sup +
                 "\n\nTotal Cost: " + cos +
                 "\n\nBy selecting approve you agree to the cost and timeframe specified by the quote and that the details above are correct")
                 .setChoices([item.createChoice('Approve'), item.createChoice('Deny')]);
  var item = form.addCheckboxItem();
  item.setTitle('What extras would you like to add on?');
  item.setChoices([
        item.createChoice('Damp-Course'),
        item.createChoice('Wrap'),
        item.createChoice('Taco')
    ]);
  Logger.log('Published URL: ' + form.getPublishedUrl());
  Logger.log('Editor URL: ' + form.getEditUrl());
  Logger.log('ID: ' + form.getId());

  // 修改点：获取整个电子表格文件的ID，而非单个工作表ID
  var ssId = spreadSheet.getId();
  form.setDestination(FormApp.DestinationType.SPREADSHEET, ssId);

  var SendTo = "insertgenertic@email.com";

  var link = form.getPublishedUrl();
  var message = "Please follow the link to accept you Quotation " + form.getPublishedUrl();

 //set subject line
 var Subject = 'Quote' + value + 'Confirmation';

  const  recipient = em;
  const subject = "Confirmation of order"
  const url = link

  GmailApp.sendEmail(recipient, subject, message);
}

注意事项 #

• 表单关联表格后会自动在目标表格中新建一个工作表存储响应，不会直接写入你现有的TC表，如果需要将响应数据同步到TC表，可以新增表单提交触发函数，在用户提交表单后自动将数据写入TC表对应位置
• 首次运行修改后的代码需要授权对应表单、表格、邮件的操作权限，确保当前账号有所有资源的编辑权限。

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