如何通过脚本将Google Sheet数据导出至另一表格实现表单提交式存储？

Got it, let's walk through exactly how to build this data archiving workflow with Google Apps Script—perfect for replacing that Google Forms setup while keeping your existing nightly scripts intact. Here's a step-by-step solution tailored to your needs:

1. Prep Your Target Archive Spreadsheet #

First, set up a dedicated Google Sheet to store all historical data (let's call it "Shift Report Archive"). Make sure its header row matches exactly the columns in your daily dashboard sheet. This ensures data lines up correctly when we copy it over.

2. Write the Core Archiving Script #

Open your dashboard's Google Sheet, go to Extensions > Apps Script to launch the editor. Replace any default code with this script (customize the placeholders to match your sheet names/IDs):

function archiveDashboardData() {
  // Define your source and target sheets
  const sourceSS = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = sourceSS.getSheetByName("Dashboard"); // Replace with your dashboard sheet name
  const targetSS = SpreadsheetApp.openById("TARGET_SPREADSHEET_ID"); // Replace with your archive sheet's ID
  const targetSheet = targetSS.getSheetByName("Archive"); // Replace with your archive sheet's name

  // Grab data from the dashboard (skip header row, assume data starts at row 2)
  const lastSourceRow = sourceSheet.getLastRow();
  const lastSourceCol = sourceSheet.getLastColumn();
  
  // Exit if there's no data to archive
  if (lastSourceRow <= 1) {
    console.log("No data to archive today—skipping.");
    return;
  }

  // Get all non-header data from the dashboard
  const dataToArchive = sourceSheet.getRange(2, 1, lastSourceRow - 1, lastSourceCol).getValues();

  // Append the data to the end of the archive sheet
  const lastTargetRow = targetSheet.getLastRow();
  targetSheet.getRange(lastTargetRow + 1, 1, dataToArchive.length, dataToArchive[0].length).setValues(dataToArchive);

  // Optional: Add a timestamp column to track when data was archived
  const timestampCol = lastSourceCol + 1;
  const timestamps = Array(dataToArchive.length).fill([new Date()]);
  targetSheet.getRange(lastTargetRow + 1, timestampCol, timestamps.length, 1).setValues(timestamps);

  console.log(`Successfully archived ${dataToArchive.length} rows of data.`);
}

Key Customizations: #

• TARGET_SPREADSHEET_ID: Grab this from your archive sheet's URL (it's the long string between /d/ and /edit).
• Sheet Names: Update "Dashboard" and "Archive" to match your actual sheet names.
• Data Range: If your dashboard data doesn't start at row 2, adjust the getRange parameters (format: getRange(startRow, startCol, numRows, numCols)).

3. Integrate with Your Existing Nightly Workflow #

Since you already have scripts to export PDFs and clear the dashboard, add the archiving step before clearing data to avoid losing information. For example:

function nightlyWorkflow() {
  // Step 1: Archive data first (critical!)
  archiveDashboardData();
  
  // Step 2: Run your existing PDF export/send script
  exportAndSendPDF(); // Replace with your actual function name
  
  // Step 3: Clear the dashboard for tomorrow
  clearDashboardData(); // Replace with your actual function name
}

4. Set Up a Time-Driven Trigger #

To automate this nightly:

1. In the Apps Script editor, click the clock icon (Triggers) on the left sidebar.
2. Click Add Trigger.
3. Configure it to:
   • Run the nightlyWorkflow function (or archiveDashboardData if running separately)
   • Event source: Time-driven
   • Type: Day timer
   • Time window: Pick a time before your existing clear/export script runs (e.g., 8:00 PM if you clear at 8:30 PM)

5. Test the Workflow #

Manually run the archiveDashboardData function once (click the play button in the editor) to verify:

• Data is correctly appended to the archive sheet
• Timestamps (if enabled) are added
• No errors appear in the logs (check View > Logs in the editor)

Pro Tips #

• Preserve Formatting: If you need to keep cell colors or styles, replace setValues with copyTo:

  sourceSheet.getRange(2, 1, lastSourceRow - 1, lastSourceCol)
    .copyTo(targetSheet.getRange(lastTargetRow + 1, 1), SpreadsheetApp.CopyPasteType.PASTE_ALL, false);
  

• Handle Duplicates: If you're worried about accidental re-runs, add a check for today's date in the archive before appending.
• Error Logging: Add try/catch blocks to notify you if the script fails (e.g., send an email alert).

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