如何编写Apps Script循环函数提交谷歌工作表条目至同文件日志表并处理模板数据

Alright, let's tackle your two Google Apps Script requirements one by one. I'll provide clear, commented code that you can adapt to your specific sheet names and needs.

This function will grab all data from your currently active sheet, then append it (along with a timestamp for tracking) to the "运行日志" worksheet in the same spreadsheet.

function submitToRunLog() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const activeSheet = ss.getActiveSheet();
  const runLogSheet = ss.getSheetByName("运行日志"); // Replace with your actual log sheet name

  // Get all data from the active sheet (excludes empty rows/columns by default)
  const activeData = activeSheet.getDataRange().getValues();
  
  // Add a timestamp column to each row for audit purposes
  const dataWithTimestamp = activeData.map(row => [...row, new Date()]);

  // Append all rows to the run log (avoids looping through each row individually for efficiency)
  runLogSheet.getRange(runLogSheet.getLastRow() + 1, 1, dataWithTimestamp.length, dataWithTimestamp[0].length)
             .setValues(dataWithTimestamp);

  // Optional: Show a confirmation message
  SpreadsheetApp.getUi().alert("Successfully submitted active sheet data to run log!");
}

Note: If your "运行日志" sheet doesn't have headers yet, you might want to add a check to insert them on the first run.

This function will target the "PO Template" sheet, grab all entries starting from cell A21 and extending down to the last row with data, then write those entries to your target worksheet.

function writePOTemplateEntries() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const poTemplateSheet = ss.getSheetByName("PO Template");
  const targetSheet = ss.getSheetByName("目标工作表"); // Replace with your actual target sheet name

  // Define the starting cell (A21) and get the entire data region below it
  const startCell = poTemplateSheet.getRange("A21");
  const dataRegion = startCell.getDataRegion(SpreadsheetApp.Dimension.ROWS);
  const poEntries = dataRegion.getValues();

  // Skip any empty rows (in case there are gaps between entries)
  const filteredEntries = poEntries.filter(row => row.some(cell => cell !== ""));

  // Append the filtered entries to the target sheet
  if (filteredEntries.length > 0) {
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1, filteredEntries.length, filteredEntries[0].length)
               .setValues(filteredEntries);
    SpreadsheetApp.getUi().alert(`Successfully wrote ${filteredEntries.length} entries to target worksheet!`);
  } else {
    SpreadsheetApp.getUi().alert("No valid entries found below A21 in PO Template.");
  }
}

Pro Tip: If you want to automate this to run on a schedule, you can use Google Apps Script's Triggers feature (found under the "Edit" menu > "Current project's triggers") to set up recurring execution.

内容的提问来源于stack exchange，提问作者Eric K.