时间戳

首页 > 解决方案 > 删除用于将选项从工作表复制到表单的重复项

google-apps-script - 删除用于将选项从工作表复制到表单的重复项

问题描述

我正在尝试获取列表，定期更新的多个列表，并将它们放入谷歌表单中的选项中。以下是我拥有的代码，但我似乎无法消除重复项，因此我在 Google 表单上有 9000 个选项。

function getDataFromGoogleSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Inventory Data");
  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 populateGoogleForms() {
  const Google_Form_ID = "1Bu-sdLlxM2KzGZTqPdxNgv23lS-iGIPLa6KPr4uQU74";
  const googleForm = FormApp.openById("1Bu-sdLlxM2KzGZTqPdxNgv23lS-iGIPLa6KPr4uQU74");
  const items = googleForm.getItems();
  const choices = getDataFromGoogleSheets();
  items.forEach(function(item) {
    const itemTitle = item.getTitle();
    if (itemTitle in choices) {
      const itemType = item.getType();
      switch (itemType) {
        case FormApp.ItemType.CHECKBOX:
          item.asCheckboxItem().setChoiceValues(choices[itemTitle]);
          break;
        case FormApp.ItemType.LIST:
          item.asListItem().setChoiceValues(choices[itemTitle]);
          break;
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item.asMultipleChoiceItem().setChoiceValues(choices[itemTitle]);
          break;
        default:
          Logger.log("Ignore question", itemTitle);
      }
    }
  });
}

标签： google-apps-scriptgoogle-sheets

解决方案

要设置的数组并返回数组：

function getDataFromGoogleSheets() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Sheet1");
  const [header, ...data] = sheet.getDataRange().getDisplayValues();
  const choices = {};
  header.forEach((title, index) => { let uA = []; choices[title] = data.map(row => row[index]).filter(e => e) });
  return choices;
}


function populateGoogleForms() {
  const Google_Form_ID = gobj.globals.editformid;
  const googleForm = FormApp.openById(Google_Form_ID);
  const items = googleForm.getItems();
  const choices = getDataFromGoogleSheets();
  items.forEach(function (item) {
    const itemTitle = item.getTitle();
    if (itemTitle in choices) {
      const itemType = item.getType();
      switch (itemType) {
        case FormApp.ItemType.CHECKBOX:
          item.asCheckboxItem().setChoiceValues([...new Set(choices[itemTitle])]);
          break;
        case FormApp.ItemType.LIST:
          item.asListItem().setChoiceValues([...new Set(choices[itemTitle])]);
          break;
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item.asMultipleChoiceItem().setChoiceValues([...new Set(choices[itemTitle])]);
          break;
        default:
          Logger.log("Ignore question", itemTitle);
      }
    }
  });
}

只需将choices[itemTitle] 转换为 [...new Set(choices[itemTitle]] 即可创建一个新集合，从而消除所有重复项并将重复项返回到数组中。

推荐阅读