Google Apps Script onFormSubmit触发器多选异常及自定义响应列位置问题

问题修复说明 #

1、多选类选项输出异常字符串修复 #

异常[Ljava.lang.Object;@7c2a6e46是因为多选（Checkbox）类表单项的返回值是数组类型，Google Apps Script底层基于Java，未处理的数组直接写入单元格时会调用Java数组的默认toString方法输出内存地址，处理方法是将数组拼接为分隔符分隔的字符串即可。

2、自定义表单回答写入表格位置实现 #

可以通过提前配置「表单项标题-目标列」的映射规则，不用按表单默认的项顺序写入，可自由指定每个字段的存放列。

调整后完整代码 #

首先修改snore函数（仅需给单选项增加标题，其余逻辑不变） #

function snore() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('TC');
  var value = sheet.getRange("B2").getValue();
  var em = sheet.getRange("C2").getValue();
  var cos = sheet.getRange("F2").getValue();
  var name = sheet.getRange("E2").getValue();
  var sup = sheet.getRange("D2").getValue();

  var form = FormApp.create(value);
  var item = form.addMultipleChoiceItem();
  // 新增：给单选项设置标题，后续映射配置要和这个标题完全一致
  item.setTitle('是否同意报价');
  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('Approved'), item.createChoice('Denied')]);
  var itm = form.addCheckboxItem();
  itm.setTitle('What extras would you like to add on? (all prices are Ex-GST)');
    itm.setChoices([
        itm.createChoice('Watergate Plus 1370mm x 36.5m, 36.5Lm - $109.80'),
        itm.createChoice('Watergate Plus 2740mm x 30m, 30Lm - $171.60'),
        itm.createChoice('White General Purpose Repair Tape 60mm x 66m, 66Lm - $39.21'),
        itm.createChoice('Wall Wrap, Foil Fasteners, 500/box - $39.21'),
        itm.createChoice('Super Course 500, 300mm x 0.5 x 50Lm - $29.30'),
        itm.createChoice('40mm Metal Top Hat Batten - 3.10/mtr')
    ])
    .showOtherOption(true);
  Logger.log('Published URL: ' + form.getPublishedUrl());
  Logger.log('Editor URL: ' + form.getEditUrl());
  Logger.log('ID: ' + form.getId());

  var myDId = DriveApp.getRootFolder().getId();

  var rootFolder = DriveApp.getFolderById(myDId);
  var archiveFolder = DriveApp.getFolderById("1Rt6xikEpHs-F2cQOTRrvATCZHCFU0bQ6");

  var newForms = rootFolder.getFilesByName(value);
  while (newForms.hasNext()) {
    var newForm = newForms.next();
    archiveFolder.addFile(newForm)
    rootFolder.removeFile(newForm);
  }

  SpreadsheetApp.getActiveSheet().getRange('J2').setValue('SENT');

  var id = "1uw8HAJsuNHses_9XGfNtQ71fp3DXE7q1";
  var buildBlob = DriveApp.getFileById(id).getBlob().setName("buildBlob");

  var SendTo = "Jonathon.banks@westal.com.au";

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

  var body = HtmlService.createTemplateFromFile("email");

  body.order = value;
  body.overdue = form.getPublishedUrl();

  var con = sheet.getRange("A2").getValue();
  var file = DriveApp.getFilesByName(con).next()

 var Subject = 'Quote' + value + 'Confirmation';

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

      MailApp.sendEmail({
      to: recipient,
      attachments: file,
      subject: subject,
        htmlBody: body.evaluate().getContent(),
      inlineImages:
      {
      build: buildBlob
    }

      });

  const SOURCE_FORM_ID = link;

 {
  const form = FormApp.openById(SOURCE_FORM_ID);
  ScriptApp.newTrigger("onFormSubmitTrigger")
    .forForm(form)
    .onFormSubmit()
    .create();
  }

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var seet = ss.getSheetByName('TC');
  var testrange = seet.getRange('J:J');
  var testvalue = (testrange.getValues());
  var csh = ss.getSheetByName('C');
  var data = [];
  var j =[];

for (i=0; i<testvalue.length;i++) {
  if ( testvalue[i] == 'SENT') {
  data.push.apply(data,seet.getRange(i+1,1,1,25).getValues());
  j.push(i);
 }
 }

 csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);

  for (i=0;i<j.length;i++){
  var k = j[i]+1;
  sheet.deleteRow(k);

  if (!(i == j.length-1)) {
  j[i+1] = j[i+1]-i-1;
}
}
}

替换原有的onFormSubmitTrigger函数 #

function onFormSubmitTrigger(e) {
  const targetSpreadsheet = SpreadsheetApp.openById("1JRhCnS048fhWMifkrD1mqdgFcs3-Cw-kGOzSgpLWfI0");
  const targetSheet = targetSpreadsheet.getSheetByName("C");

  // ========== 自定义配置区，按需修改即可 ==========
  // 映射规则：键为表单项的标题，值为要写入的列号（1对应A列，2对应B列，以此类推）
  const fieldColumnMap = {
    "Timestamp": 1, // 时间戳写入A列
    "是否同意报价": 2, // 同意/拒绝单选写入B列，需和你给表单项设置的标题完全一致
    "What extras would you like to add on? (all prices are Ex-GST)": 3 // 多选项写入C列
  }
  // 多选选项的拼接分隔符
  const multipleChoiceSeparator = "、";
  // ========== 配置区结束 ==========

  // 首次运行自动生成表头，不需要可以删除这段
  if (targetSheet.getLastRow() === 0) {
    const headerRow = new Array(targetSheet.getMaxColumns()).fill("");
    Object.entries(fieldColumnMap).forEach(([title, col]) => {
      headerRow[col - 1] = title;
    })
    targetSheet.appendRow(headerRow);
  }

  // 处理表单响应
  const responses = {};
  responses["Timestamp"] = new Date();
  e.response.getItemResponses().forEach(itemRes => {
    const title = itemRes.getItem().getTitle();
    let value = itemRes.getResponse();
    // 自动处理多选数组，避免输出异常地址
    if (Array.isArray(value)) {
      value = value.join(multipleChoiceSeparator);
    }
    responses[title] = value;
  })

  // 按自定义列写入数据
  const targetRowNum = targetSheet.getLastRow() + 1;
  const targetRow = new Array(targetSheet.getMaxColumns()).fill("");
  Object.entries(responses).forEach(([title, value]) => {
    if (fieldColumnMap[title]) {
      targetRow[fieldColumnMap[title] - 1] = value;
    }
  })
  targetSheet.getRange(targetRowNum, 1, 1, targetRow.length).setValues([targetRow]);
}

注意事项 #

• 新增表单项后，只要在fieldColumnMap里添加对应的「表单项标题-目标列号」映射即可，顺序不影响
• 不需要写入表格的字段不用加到映射表中，会自动忽略
• 可自行修改multipleChoiceSeparator调整多选项的拼接符号

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