Google Sheets转Google Form时标题显示为[object Object]问题求助

问题场景 #

• 将Google Sheet数据转换为Google Form时
• 选项可正常导入，但问题标题被标记为[object Object]

原代码 #

function getDataFromGoogleSheets() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('test');
  const [header, ...data] = sheet.getDataRange().getDisplayValues();
  const choices = {};

  header.forEach(function(title, index) {
    choices[title] = data.map(row => row[index]).filter(e => e !== '');
  });

  return choices;
}

function myFunction() {

  const choices = getDataFromGoogleSheets();

  var form = FormApp.create('New Form');

  Logger.log(choices)

  for (var i in choices){
    form.addMultipleChoiceItem()
      .setTitle(choices) // [object Object] ???
      .setChoiceValues(choices[i]) // Okay
      .showOtherOption(true);
      Logger.log(choices[i]);
}
}

问题原因 #

在myFunction的for...in循环中，setTitle(choices)传入的是整个choices对象而非当前循环的表头标题。JavaScript将对象转为字符串时，默认输出就是[object Object]，这就是标题显示异常的核心原因。

修正后的代码 #

function getDataFromGoogleSheets() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('test');
  const [header, ...data] = sheet.getDataRange().getDisplayValues();
  const choices = {};

  header.forEach(function(title, index) {
    choices[title] = data.map(row => row[index]).filter(e => e !== '');
  });

  return choices;
}

function myFunction() {

  const choices = getDataFromGoogleSheets();

  var form = FormApp.create('New Form');

  Logger.log(choices)

  for (var i in choices){
    form.addMultipleChoiceItem()
      .setTitle(i) // 改为当前循环的表头标题
      .setChoiceValues(choices[i])
      .showOtherOption(true);
      Logger.log(choices[i]);
}
}

修正后，循环中的i就是choices对象的键（即Google Sheet里的表头标题，比如你需要的"P-1~4"），将其传入setTitle就能正确设置问题标题。

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