求适用于Google Sheets的每日数据迁移脚本：按日期列存储并追踪每日数据

I’ve put together a script that exactly matches your requirement—automatically copying the B3:B24 range from your source sheet to a new column in the log sheet each day, with the date recorded in the top cell of each new column. Here's how it works:

Step-by-Step Breakdown #

• First, the script identifies the next empty column in your log sheet (so it never overwrites existing data).
• It writes today’s date to the top cell of that column (e.g., A1 for day 1, B1 for day 2, etc.).
• Then it copies the B3:B24 range from your source sheet into the rest of that column, matching the source’s row structure.

Complete Script #

function logDailyData() {
  // Replace these with your actual sheet names
  const sourceSheetName = "Source";
  const logSheetName = "Log";
  
  // Access the active spreadsheet and target sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName(sourceSheetName);
  const logSheet = ss.getSheetByName(logSheetName);
  
  // Error check: Make sure both sheets exist
  if (!sourceSheet || !logSheet) {
    throw new Error("One or both sheets not found! Double-check your sheet names.");
  }
  
  // Fetch the data from the source sheet's B3:B24 range
  const sourceData = sourceSheet.getRange("B3:B24").getValues();
  
  // Find the next empty column in the log sheet
  const nextColumn = logSheet.getLastColumn() + 1;
  
  // Write today's date to the top cell of the new column
  const today = new Date();
  logSheet.getRange(1, nextColumn).setValue(today);
  // Optional: Uncomment below to format the date (e.g., "yyyy-MM-dd")
  // logSheet.getRange(1, nextColumn).setNumberFormat("yyyy-MM-dd");
  
  // Paste the source data into the new column (starting at row 3 to match source)
  logSheet.getRange(3, nextColumn, sourceData.length, 1).setValues(sourceData);
  
  Logger.log(`Successfully logged data to column ${nextColumn} on ${today}`);
}

How to Set Up the Daily Auto-Trigger #

To make this run automatically every day:

1. Open your Google Sheet, go to Extensions > Apps Script to launch the script editor.
2. Paste the code above, updating Source and Log to match your actual sheet names.
3. Save the project (click the floppy disk icon) and name it something like "DailyDataLogger".
4. Click the clock icon (Triggers) in the left sidebar.
5. Click Add Trigger in the bottom-right corner.
6. Configure the trigger settings:
   • Choose function: logDailyData
   • Choose deployment: Head
   • Select event source: Time-driven
   • Select type of time based trigger: Day timer
   • Pick a time of day (choose when your source data will be ready for logging)
7. Click Save—you’ll need to authorize the script to access your sheet (follow the prompts; you may need to click "Advanced" to allow access).

Quick Tips #

• If you want the logged data to start at row 2 instead of row 3, just change the 3 in getRange(3, nextColumn...) to 2.
• The date is stored as a raw Date object by default, but you can uncomment the setNumberFormat line to use a cleaner, human-readable format.

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