如何将Google Sheet特定数据结构转换为可用于更新Google Form选项的对象？

我来帮你搞定这个需求！结合Google Apps Script，我们可以直接在Google Sheet中处理数据，生成你需要的结构化对象，完美适配更新Google Form选项的场景。下面是完整的实现步骤和代码：

核心实现思路 #

1. 读取Sheet数据：先获取整个Sheet的所有数据，方便后续遍历处理
2. 划分年级组列范围：根据第1行的年级标识，确定每个年级对应的列区间（直到下一个新年级出现）
3. 筛选目标列：对每个年级组，只保留第2行标记为Form Display的列，同时排除包含capacity或#registered的列
4. 按日期分组活动：提取符合条件列的活动数据（第3行及以下的非空值），并按第2行的日期分类整理
5. 生成目标结构：将每个年级组的整理结果封装为指定格式的对象，最终组成数组

完整代码实现 #

function transformSheetData() {
  // 替换为你的Sheet名称
  const sheetName = "活动列表";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  
  if (data.length < 3) {
    console.log("数据不足：至少需要3行（年级、日期标识、活动数据）");
    return [];
  }

  // 步骤1：划分年级组对应的列范围
  const yearGroupRanges = [];
  let currentYear = data[0][0];
  let startCol = 0;

  // 遍历第1行的所有列
  for (let col = 1; col < data[0].length; col++) {
    const cellValue = data[0][col];
    // 判断是否是新的年级组（假设年级格式为"Year X"，可根据实际调整判断逻辑）
    if (cellValue && cellValue.startsWith("Year ") && cellValue !== currentYear) {
      yearGroupRanges.push({
        year: currentYear,
        startCol: startCol,
        endCol: col - 1
      });
      currentYear = cellValue;
      startCol = col;
    }
  }
  // 添加最后一个年级组
  yearGroupRanges.push({
    year: currentYear,
    startCol: startCol,
    endCol: data[0].length - 1
  });

  // 步骤2：处理每个年级组，生成目标对象
  const result = [];
  yearGroupRanges.forEach(group => {
    const yearObj = {
      "Year group": group.year,
    };

    // 遍历当前年级组的所有列
    for (let col = group.startCol; col <= group.endCol; col++) {
      const headerValue = data[1][col]?.toString() || "";
      
      // 筛选条件：包含Form Display，且不包含capacity和#registered
      if (headerValue.includes("Form Display") && !headerValue.includes("capacity") && !headerValue.includes("#registered")) {
        // 提取日期（比如从"Sunday - Form Display"中提取"Sunday"，可根据实际格式调整正则）
        const dateMatch = headerValue.match(/(Sunday|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday)/i);
        if (!dateMatch) continue;
        const day = dateMatch[0].charAt(0).toUpperCase() + dateMatch[0].slice(1).toLowerCase();
        
        // 提取该列的活动数据（从第3行开始，收集非空值）
        const activities = [];
        for (let row = 2; row < data.length; row++) {
          const activity = data[row][col]?.toString().trim();
          if (activity) activities.push(activity);
        }

        // 将活动添加到对应日期的数组中
        if (!yearObj[day]) {
          yearObj[day] = [];
        }
        yearObj[day] = [...yearObj[day], ...activities];
      }
    }

    result.push(yearObj);
  });

  console.log("转换结果：", JSON.stringify(result, null, 2));
  return result;
}

关键细节说明 #

• 年级组判断逻辑：代码中假设年级格式为Year X，如果你的Sheet中年级标识格式不同（比如仅数字），可以调整cellValue.startsWith("Year ")这部分的判断条件
• 日期提取正则：正则/(Sunday|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday)/i会匹配英文星期，如果你用的是中文星期，替换成对应的中文关键词即可（比如/(周日|周一|周二|周三|周四|周五|周六)/）
• 数据清洗：收集活动时会自动过滤空单元格和空白内容，保证结果的整洁性
• 扩展适配：如果需要直接用这个结果更新Google Form选项，可以在代码末尾添加Form更新逻辑，比如调用FormApp.openById("你的FormID").getItemById("目标选项ID").setChoiceValues(...)

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