如何避免Google Sheets脚本复制行时重复处理数据？

我用带多板块的Google Form做每日签到表单，共25个小节对应12个主板块。编写脚本根据列值将每行数据复制到对应板块的工作表，但每次运行脚本都会重复处理已复制的数据，导致大量重复行。尝试过时间触发器、onFormSubmit、onChange等，问题依旧；且因多人同时提交的风险，无法使用onFormSubmit触发器。

原脚本代码：

function moveRows() {
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const mainSheet = ss.getSheetByName('Form Responses 1');
 const data = mainSheet.getDataRange().getValues();

 const processedRows = new Set();

 const sectionMapping = {
   'Guests': {
     sheetName: 'Guests',
     valueToIncludeColumnIndex: 0,
     valueToInclude: ['Guest'],
     columns: [2, 3]
   },
   'Members': {
     sheetName: 'Members',
     valueToIncludeColumnIndex: 0,
     valueToInclude: ['Member'],
     columns: [4, 5, 6]
   },
   'Youth Programming': {
     sheetName: 'Youth Programming',
     valueToIncludeColumnIndex: 6,
     valueToInclude: ['Youth Programming'],
     columns: [8, 9]
   },
   'Phone Log': {
     sheetName: 'Phone Log',
     valueToIncludeColumnIndex: 6,
     valueToInclude: ['Phone Log'],
     columns: [10, 11, 12]
   }
 };

 for (let rowIndex = 1; rowIndex < data.length; rowIndex++) {
   if (processedRows.has(rowIndex)) {
     Logger.log(`Row ${rowIndex + 1} already processed. Skipping...`);
     continue;
   }
   const sectionColumnIndex = 1;
   const section = data[rowIndex][sectionColumnIndex - 1];


   try {
     if (section) {
       const rowDataString = JSON.stringify(data[rowIndex]);
      
       if (!processedRows.has(rowDataString)) {
         if (section === 'Staff') {
           const valueColumn7 = data[rowIndex][6];
          
           let matchedSection = '';
          
           if (valueColumn7 === 'Youth Programming') {
             matchedSection = 'Youth Programming';
           } else if (valueColumn7 === 'Phone Log') {
             matchedSection = 'Phone Log';
           }
          
           if (matchedSection) {  
             if (!processedRows.has(`${matchedSection}-${rowIndex}`)) {
               const { sheetName, columns } = sectionMapping[matchedSection];
               const sheet = ss.getSheetByName(sheetName);
               const rowData = columns.map(index => data[rowIndex][index - 1]);
               sheet.appendRow(rowData);
               Logger.log(`Row ${rowIndex + 1} - Transferred to Sheet: ${sheetName}`);
               processedRows.add(rowIndex);
             } else {
               Logger.log(`Row ${rowIndex + 1} already processed. Skipping...`);
             }
           } else {
             Logger.log(`Row ${rowIndex + 1} - Section not found on column 7.`);
           }
           continue;
         }
        
         Logger.log(`Processing Row ${rowIndex + 1} - Section: ${section}`);
          // Find all sections in the sectionMapping where the condition is met
         const matchedSections = Object.keys(sectionMapping).filter(key =>
            sectionMapping[key].valueToInclude.includes(section.trim())
          );

          Logger.log(`Matched Sections: ${matchedSections.join(', ')}`);

          if (matchedSections.length > 0) {
            matchedSections.forEach(matchedSection => {
              const { sheetName, columns, valueToIncludeColumnIndex, valueToInclude } = sectionMapping[matchedSection];


              // Check if the value in the specified column matches the expected value
              if (data[rowIndex][valueToIncludeColumnIndex] !== valueToInclude[0]) {
                Logger.log(`Row ${rowIndex + 1} - Section: ${section} - Value does not match: ${data[rowIndex][valueToIncludeColumnIndex]}`);
                return; // Skip to the next iteration
              }

              const sheet = ss.getSheetByName(sheetName);
              const rowData = columns.map(index => data[rowIndex][index - 1]);
              sheet.appendRow(rowData);
              Logger.log(`Row ${rowIndex + 1} - Transferred to Sheet: ${sheetName}`);


              processedRows.add(rowIndex);
              processedRows.add(rowDataString);
          }); 
          }
        }
     }
   } catch (error) {
     Logger.log(`Error processing row: ${rowIndex + 1}: ${error}`);
   }
 }
}

问题根源 #

原脚本中用processedRows内存Set记录已处理行，但脚本每次执行都是独立会话，内存数据不会持久化，所以每次运行时processedRows都会被重新初始化，之前处理过的行记录完全丢失，导致重复处理。

解决方案 #

需要将“已处理”状态持久化保存，推荐两种可行方案：

方案1：主表新增“已处理”列标记（直观易维护）

在Form Responses 1表最后新增一列，表头设为“已处理”，脚本处理行前检查该列状态，处理后标记为“已完成”。

修改后的脚本：

function moveRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName('Form Responses 1');
  const data = mainSheet.getDataRange().getValues();
  const lastColumn = mainSheet.getLastColumn();

  // 自动添加"已处理"列（如果不存在）
  if (data[0][lastColumn - 1] !== '已处理') {
    mainSheet.getRange(1, lastColumn + 1).setValue('已处理');
    // 重新获取包含新列的数据
    data = mainSheet.getDataRange().getValues();
  }

  const sectionMapping = {
    'Guests': {
      sheetName: 'Guests',
      valueToIncludeColumnIndex: 0,
      valueToInclude: ['Guest'],
      columns: [2, 3]
    },
    'Members': {
      sheetName: 'Members',
      valueToIncludeColumnIndex: 0,
      valueToInclude: ['Member'],
      columns: [4, 5, 6]
    },
    'Youth Programming': {
      sheetName: 'Youth Programming',
      valueToIncludeColumnIndex: 6,
      valueToInclude: ['Youth Programming'],
      columns: [8, 9]
    },
    'Phone Log': {
      sheetName: 'Phone Log',
      valueToIncludeColumnIndex: 6,
      valueToInclude: ['Phone Log'],
      columns: [10, 11, 12]
    }
  };

  for (let rowIndex = 1; rowIndex < data.length; rowIndex++) {
    // 检查当前行是否已处理
    const isProcessed = data[rowIndex][lastColumn] === '已完成';
    if (isProcessed) {
      Logger.log(`Row ${rowIndex + 1} already processed. Skipping...`);
      continue;
    }

    const sectionColumnIndex = 1;
    const section = data[rowIndex][sectionColumnIndex - 1];

    try {
      if (section) {
        if (section === 'Staff') {
          const valueColumn7 = data[rowIndex][6];
          let matchedSection = '';
          
          if (valueColumn7 === 'Youth Programming') {
            matchedSection = 'Youth Programming';
          } else if (valueColumn7 === 'Phone Log') {
            matchedSection = 'Phone Log';
          }
          
          if (matchedSection) {  
            const { sheetName, columns } = sectionMapping[matchedSection];
            const sheet = ss.getSheetByName(sheetName);
            const rowData = columns.map(index => data[rowIndex][index - 1]);
            sheet.appendRow(rowData);
            Logger.log(`Row ${rowIndex + 1} - Transferred to Sheet: ${sheetName}`);
          } else {
            Logger.log(`Row ${rowIndex + 1} - Section not found on column 7.`);
          }
          // 标记为已处理
          mainSheet.getRange(rowIndex + 1, lastColumn + 1).setValue('已完成');
          continue;
        }

        Logger.log(`Processing Row ${rowIndex + 1} - Section: ${section}`);
        const matchedSections = Object.keys(sectionMapping).filter(key =>
          sectionMapping[key].valueToInclude.includes(section.trim())
        );

        Logger.log(`Matched Sections: ${matchedSections.join(', ')}`);

        if (matchedSections.length > 0) {
          matchedSections.forEach(matchedSection => {
            const { sheetName, columns, valueToIncludeColumnIndex, valueToInclude } = sectionMapping[matchedSection];

            if (data[rowIndex][valueToIncludeColumnIndex] !== valueToInclude[0]) {
              Logger.log(`Row ${rowIndex + 1} - Section: ${section} - Value does not match: ${data[rowIndex][valueToIncludeColumnIndex]}`);
              return;
            }

            const sheet = ss.getSheetByName(sheetName);
            const rowData = columns.map(index => data[rowIndex][index - 1]);
            sheet.appendRow(rowData);
            Logger.log(`Row ${rowIndex + 1} - Transferred to Sheet: ${sheetName}`);
          });
        }
        // 标记当前行为已处理
        mainSheet.getRange(rowIndex + 1, lastColumn + 1).setValue('已完成');
      } else {
        // 无section值也标记已处理
        mainSheet.getRange(rowIndex + 1, lastColumn + 1).setValue('已完成');
      }
    } catch (error) {
      Logger.log(`Error processing row: ${rowIndex + 1}: ${error}`);
      // 出错也标记，避免重复报错
      mainSheet.getRange(rowIndex + 1, lastColumn + 1).setValue('处理出错');
    }
  }
}

方案2：用PropertiesService保存已处理行ID（不修改主表结构）

如果不想改动主表结构，可利用Google Apps Script的PropertiesService存储已处理行的唯一标识（比如表单提交时间戳）：

function moveRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName('Form Responses 1');
  const data = mainSheet.getDataRange().getValues();
  const userProps = PropertiesService.getUserProperties();
  // 从持久化存储中读取已处理行ID
  const processedRows = new Set(userProps.getProperty('processedRows')?.split(',') || []);

  const sectionMapping = {
    'Guests': {
      sheetName: 'Guests',
      valueToIncludeColumnIndex: 0,
      valueToInclude: ['Guest'],
      columns: [2, 3]
    },
    'Members': {
      sheetName: 'Members',
      valueToIncludeColumnIndex: 0,
      valueToInclude: ['Member'],
      columns: [4, 5, 6]
    },
    'Youth Programming': {
      sheetName: 'Youth Programming',
      valueToIncludeColumnIndex: 6,
      valueToInclude: ['Youth Programming'],
      columns: [8, 9]
    },
    'Phone Log': {
      sheetName: 'Phone Log',
      valueToIncludeColumnIndex: 6,
      valueToInclude: ['Phone Log'],
      columns: [10, 11, 12]
    }
  };

  for (let rowIndex = 1; rowIndex < data.length; rowIndex++) {
    // 用表单提交时间作为唯一标识
    const rowId = data[rowIndex][0].toString();
    if (processedRows.has(rowId)) {
      Logger.log(`Row ${rowIndex + 1} already processed. Skipping...`);
      continue;
    }

    const sectionColumnIndex = 1;
    const section = data[rowIndex][sectionColumnIndex - 1];

    try {
      if (section) {
        if (section === 'Staff') {
          const valueColumn7 = data[rowIndex][6];
          let matchedSection = '';
          
          if (valueColumn7 === 'Youth Programming') {
            matchedSection = 'Youth Programming';
          } else if (valueColumn7 === 'Phone Log') {
            matchedSection = 'Phone Log';
          }
          
          if (matchedSection) {  
            const { sheetName, columns } = sectionMapping[matchedSection];
            const sheet = ss.getSheetByName(sheetName);
            const rowData = columns.map(index => data[rowIndex][index - 1]);
            sheet.appendRow(rowData);
            Logger.log(`Row ${rowIndex + 1} - Transferred to Sheet: ${sheetName}`);
          } else {
            Logger.log(`Row ${rowIndex + 1} - Section not found on column 7.`);
          }
          processedRows.add(rowId);
          continue;
        }

        Logger.log(`Processing Row ${rowIndex + 1} - Section: ${section}`);
        const matchedSections = Object.keys(sectionMapping).filter(key =>
          sectionMapping[key].valueToInclude.includes(section.trim())
        );

        Logger.log(`Matched Sections: ${matchedSections.join(', ')}`);

        if (matchedSections.length > 0) {
          matchedSections.forEach(matchedSection => {
            const { sheetName, columns, valueToIncludeColumnIndex, valueToInclude } = sectionMapping[matchedSection];

            if (data[rowIndex][valueToIncludeColumnIndex] !== valueToInclude[0]) {
              Logger.log(`Row ${rowIndex + 1} - Section: ${section} - Value does not match: ${data[rowIndex][valueToIncludeColumnIndex]}`);
              return;
            }

            const sheet = ss.getSheetByName(sheetName);
            const rowData = columns.map(index => data[rowIndex][index - 1]);
            sheet.appendRow(rowData);
            Logger.log(`Row ${rowIndex + 1} - Transferred to Sheet: ${sheetName}`);
          });
        }
        processedRows.add(rowId);
      } else {
        processedRows.add(rowId);
      }
    } catch (error) {
      Logger.log(`Error processing row: ${rowIndex + 1}: ${error}`);
      processedRows.add(rowId);
    }
  }

  // 将已处理行ID保存到持久化存储
  userProps.setProperty('processedRows', Array.from(processedRows).join(','));
}

方案说明 #

• 方案1：直观易懂，可直接在主表查看处理状态，适合需要可视化管理的场景。
• 方案2：无需修改主表结构，适合不想改动表单响应表的情况。
• 两种方案均通过持久化存储已处理状态，彻底解决脚本重复处理的问题。

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