Google Apps Script中appendRow如何解析表单复选框数组响应？

问题原因 #

当表单包含复选框项时，itemResponse.getResponse()返回的是数组类型结果（比如选中周一、周三时，返回["Monday", "Wednesday"]）。直接将数组传入appendRow()时，Google Sheets会把数组当作对象输出其内存地址标识，也就是你看到的[Ljava.lang.Object;@40637acb。

解决方法 #

核心是把数组类型的响应转换为逗号分隔的字符串，修改代码中处理表单响应的逻辑即可：

修改后的完整代码 #

function onFormSubmit(event) {
  const record_array = [];
  const formId = '你的表单ID';
  const form = FormApp.openById(formId);
  const formResponses = form.getResponses();
  const latestResponse = formResponses[formResponses.length - 1];
  const itemResponses = latestResponse.getItemResponses();

  for (let j = 0; j < itemResponses.length; j++) {
    const itemResponse = itemResponses[j];
    let answer = itemResponse.getResponse();
    // 关键：判断是否为数组，是则转为逗号分隔的字符串
    if (Array.isArray(answer)) {
      answer = answer.join(', ');
    }
    record_array.push(answer);
  }
  AddRecord(record_array[0], record_array[1], record_array[2], record_array[3]);
}

function AddRecord(first_name, last_name, color, test) {
  const sheetUrl = '你的表格URL';
  const ss = SpreadsheetApp.openByUrl(sheetUrl);
  const dataSheet = ss.getSheetByName("Sheet1");
  dataSheet.appendRow([first_name, last_name, color, test, new Date()]);
}

更高效的优化版本（利用event参数） #

onFormSubmit触发器本身会传入event对象，直接从event中获取当前提交的响应，无需遍历所有历史响应，效率更高：

function onFormSubmit(event) {
  const record_array = [];
  const itemResponses = event.response.getItemResponses();

  for (let j = 0; j < itemResponses.length; j++) {
    const itemResponse = itemResponses[j];
    let answer = itemResponse.getResponse();
    if (Array.isArray(answer)) {
      answer = answer.join(', ');
    }
    record_array.push(answer);
  }
  AddRecord(record_array[0], record_array[1], record_array[2], record_array[3]);
}

function AddRecord(first_name, last_name, color, test) {
  const sheetUrl = '你的表格URL';
  const ss = SpreadsheetApp.openByUrl(sheetUrl);
  const dataSheet = ss.getSheetByName("Sheet1");
  dataSheet.appendRow([first_name, last_name, color, test, new Date()]);
}

关键代码说明 #

• Array.isArray(answer)：判断当前响应是否为数组（复选框项专属）
• answer.join(', ')：将数组元素用逗号加空格拼接成字符串，比如["Monday", "Wednesday"]会转为"Monday, Wednesday"

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