Google Apps Script实现Google Sheets数据转换与定时同步需求

Google Sheets中，Input工作表通过IMPORTRANGE导入数据，需借助Google Apps Script每日7点自动将数据转换后同步至Output工作表，转换规则如下：

• 拆分Area列中逗号分隔的多Code值，每行对应一个Code，其余列数据复制
• 忽略Area列空白或非数值的行
• 相同Code的记录横向排列，含Assistant的Division记录优先置于左侧
• 含Assistant的Division需添加前缀Category，例如Assistant - Bike改为Category Assistant - Bike

function transformAndSyncData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const inputSheet = ss.getSheetByName('Input');
  const outputSheet = ss.getSheetByName('Output');
  
  // 清空Output工作表内容，保留Input表头（若无需表头可删除此段）
  outputSheet.clearContents();
  const headerRange = inputSheet.getRange(1, 1, 1, inputSheet.getLastColumn());
  headerRange.copyTo(outputSheet.getRange(1, 1));
  
  // 获取Input表数据（跳过第一行表头）
  const inputData = inputSheet.getRange(2, 1, inputSheet.getLastRow() - 1, inputSheet.getLastColumn()).getValues();
  
  // 初步处理：拆分Code、过滤无效行、处理Division前缀
  const processedItems = [];
  inputData.forEach(row => {
    const areaValue = row[0]; // 假设Area是第一列，需根据实际列位置调整索引
    // 过滤Area空白或非数值的行
    if (!areaValue || isNaN(Number(areaValue.toString().trim()))) return;
    
    // 拆分逗号分隔的Code值
    const codes = areaValue.toString().split(',').map(code => code.trim()).filter(code => code);
    const divisionValue = row[1]; // 假设Division是第二列，需根据实际列位置调整索引
    
    codes.forEach(code => {
      const newRow = [...row];
      newRow[0] = code; // 替换为单个Code
      // 给含Assistant的Division添加前缀
      if (divisionValue && divisionValue.toString().includes('Assistant')) {
        newRow[1] = `Category ${divisionValue}`;
      }
      processedItems.push({
        code: code,
        rowData: newRow,
        isAssistant: divisionValue && divisionValue.toString().includes('Assistant')
      });
    });
  });
  
  // 按Code分组，区分Assistant和普通记录
  const codeGroups = {};
  processedItems.forEach(item => {
    if (!codeGroups[item.code]) {
      codeGroups[item.code] = { assistantRows: [], regularRows: [] };
    }
    item.isAssistant ? codeGroups[item.code].assistantRows.push(item.rowData) : codeGroups[item.code].regularRows.push(item.rowData);
  });
  
  // 转换为横向排列格式
  const finalOutput = [];
  Object.values(codeGroups).forEach(group => {
    const combinedRows = [...group.assistantRows, ...group.regularRows];
    if (!combinedRows.length) return;
    
    // 保留一个Code列，其余数据横向拼接
    const resultRow = [combinedRows[0][0]];
    combinedRows.forEach(row => resultRow.push(...row.slice(1)));
    finalOutput.push(resultRow);
  });
  
  // 将处理后的数据写入Output表
  if (finalOutput.length) {
    outputSheet.getRange(2, 1, finalOutput.length, finalOutput[0].length).setValues(finalOutput);
  }
}

// 创建每日7点定时触发器
function setupDailyTrigger() {
  // 删除已有同名触发器，避免重复
  ScriptApp.getProjectTriggers().forEach(trigger => {
    if (trigger.getHandlerFunction() === 'transformAndSyncData') {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  
  // 新建每日7点执行的触发器
  ScriptApp.newTrigger('transformAndSyncData')
    .timeBased()
    .atHour(7)
    .everyDays(1)
    .create();
}

关键说明 #

1. 列索引调整：代码中默认Area在第1列（索引0）、Division在第2列（索引1），请根据你的表格实际列位置修改对应索引值
2. 表头处理：如果Output表有自定义表头，可删除复制Input表头的代码段
3. 权限与激活：首次运行setupDailyTrigger时需完成权限授权，之后每日7点会自动执行数据同步
4. 数据去重：脚本会自动过滤Area列无效行，拆分后的重复Code会按规则横向排列

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