求助：为Google表格配置每周触发的时间触发器脚本实现周度指标留存

Hey there! Let's break this down step by step—since you already know your way around the onEdit trigger, this will be straightforward once we walk through the time-driven setup and the script logic.

Step 1: Set Up the Weekly Sunday Trigger #

First, let's get the time-driven trigger configured (this is the part that runs your script automatically every Sunday):

• Open your Google Sheet, go to Tools > Script Editor to launch the Apps Script environment.
• Click the clock icon 🕒 on the left sidebar (this is the Triggers menu).
• Click the blue Add Trigger button in the bottom right.
• Configure the trigger settings like this:
  • Choose which function to run: Select the name of the script we'll write next (we'll call it copyWeeklyMetrics).
  • Choose which deployment to run: Leave this as Head (default).
  • Select event source: Pick Time-driven.
  • Select type of time based trigger: Choose Week timer.
  • Select day of week: Pick Sunday.
  • Select time of day: Choose a time when your sheet isn't being heavily used (like 1:00 AM - 2:00 AM) to avoid conflicts.
  • Time zone: Select your local time zone so the trigger runs at the correct time.
• Click Save—you might need to authorize the script to access your sheet (follow the prompts, it's safe!).

Step 2: Write the Script to Copy Weekly Metrics #

Now let's write the script that will copy the weekly metrics to the correct row. Paste this into the script editor, and I'll explain each part so you understand what's going on:

function copyWeeklyMetrics() {
  // 1. Get your spreadsheet and target sheet (replace 'Metrics Tracker' with your sheet name)
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Metrics Tracker');
  if (!sheet) {
    SpreadsheetApp.getUi().alert('找不到名为"Metrics Tracker"的工作表，请检查名称拼写！');
    return;
  }

  // 2. Calculate last week's start and end dates (adjust this if your week runs on different days)
  const today = new Date();
  // Assuming we run on Sunday, last week ends on Saturday (yesterday)
  const lastWeekEnd = new Date(today.setDate(today.getDate() - 1));
  // Last week starts 6 days before the end date (so 7 days total)
  const lastWeekStart = new Date(lastWeekEnd.setDate(lastWeekEnd.getDate() - 6));

  // 3. Format dates to match the format in your A/B columns (e.g., MM/dd/yyyy)
  function formatDate(date) {
    const month = date.getMonth() + 1; // Months are 0-indexed in JS
    const day = date.getDate();
    const year = date.getFullYear();
    return `${month}/${day}/${year}`;
  }
  const startDateStr = formatDate(lastWeekStart);
  const endDateStr = formatDate(lastWeekEnd);

  // 4. Find the row in A/B columns that matches last week's dates
  const allRows = sheet.getDataRange().getValues();
  let targetRow = -1;

  for (let i = 0; i < allRows.length; i++) {
    const currentRow = allRows[i];
    // Check if A column matches start date and B column matches end date
    if (currentRow[0] === startDateStr && currentRow[1] === endDateStr) {
      targetRow = i + 1; // Convert to 1-indexed row number for the sheet
      break;
    }
  }

  if (targetRow === -1) {
    SpreadsheetApp.getUi().alert(`未找到日期范围为${startDateStr} - ${endDateStr}的行，请检查A、B列的日期格式！`);
    return;
  }

  // 5. Copy the cumulative totals from row 3 (C-L columns) to the target row
  // C column is index 2, L column is index 11 (0-indexed), so 10 columns total
  const sourceRange = sheet.getRange(3, 3, 1, 10);
  const targetRange = sheet.getRange(targetRow, 3, 1, 10);
  
  // Copy only values (not formulas) so the weekly data stays fixed
  sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
  // Optional: Add a confirmation message
  SpreadsheetApp.getUi().alert(`上周指标已成功复制到第${targetRow}行！`);
}

Key Customization Tips:

• Sheet Name: Replace 'Metrics Tracker' with the exact name of your sheet (it's case-sensitive!).
• Week Date Range: If your week runs on different days (e.g., Monday to Sunday), adjust the lastWeekEnd and lastWeekStart calculations. For example, if your week ends on Sunday, set lastWeekEnd = new Date(today.setDate(today.getDate() - 7)).
• Date Format: Make sure the formatDate function matches how dates are written in your A/B columns. If you use dd/mm/yyyy, swap month and day in the return statement.

Step 3: Test the Script Before the Trigger Runs #

To make sure everything works smoothly:

1. In the script editor, select copyWeeklyMetrics from the dropdown menu next to the run button ▶️.
2. Click the run button—authorize the script if prompted.
3. Check your sheet: The cumulative totals from row 3 should be copied to the row with last week's dates in A/B columns.

If you get an alert, follow the message to fix any issues (like a wrong sheet name or mismatched date format).

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