Google Sheets每周循环倒计时计时器GAS实现技术咨询

Got it! Let's build a countdown that automatically resets every Monday at 7 AM once it hits the target time. Here's a complete, easy-to-implement solution with Google Apps Script (GAS):

Approach #

We’ll create a script that:

• Dynamically calculates the next target time (every Monday 7 AM)
• Updates the countdown display every minute
• Auto-resets to the next week’s target as soon as the current update time is reached

Step 1: Prep Your Sheet #

Choose a cell to display the countdown (e.g., cell A1). This is where your timer will live.

Step 2: Add the GAS Code #

1. Open your Google Sheet
2. Click Extensions > Apps Script to launch the script editor
3. Replace the default code with the following:

function getNextUpdateTime() {
  const now = new Date();
  const targetDay = 1; // Monday = 1 (Sunday = 0, Tuesday = 2, etc.)
  const targetHour = 7;
  const targetMinute = 0;
  
  let nextUpdate = new Date(now);
  nextUpdate.setHours(targetHour, targetMinute, 0, 0);
  
  // If today is Monday but past 7 AM, or any other day, jump to next Monday
  if (now.getDay() !== targetDay || now > nextUpdate) {
    const daysToAdd = (targetDay - now.getDay() + 7) % 7;
    nextUpdate.setDate(nextUpdate.getDate() + daysToAdd);
  }
  
  return nextUpdate;
}

function updateCountdown() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const countdownCell = sheet.getRange("A1"); // Change this to your preferred cell
  
  const nextUpdate = getNextUpdateTime();
  const now = new Date();
  
  // If we've reached the target time, reset to next week
  if (now >= nextUpdate) {
    countdownCell.setValue("Update in progress...");
    Utilities.sleep(1000); // Brief pause to show update status
    updateCountdown(); // Recursive call to reset the countdown
    return;
  }
  
  // Calculate remaining time components
  const diffMs = nextUpdate - now;
  const days = Math.floor(diffMs / (1000 * 60 * 60 * 24));
  const hours = Math.floor((diffMs % (1000 * 60 * 60 * 24)) / (1000 * 60 * 60));
  const minutes = Math.floor((diffMs % (1000 * 60 * 60)) / (1000 * 60));
  const seconds = Math.floor((diffMs % (1000 * 60)) / 1000);
  
  // Format and display the countdown
  const countdownText = `${days}d ${hours}h ${minutes}m ${seconds}s until next update`;
  countdownCell.setValue(countdownText);
}

function createCountdownTrigger() {
  // Delete existing triggers to avoid duplicates
  const existingTriggers = ScriptApp.getProjectTriggers();
  for (let trigger of existingTriggers) {
    if (trigger.getHandlerFunction() === "updateCountdown") {
      ScriptApp.deleteTrigger(trigger);
    }
  }
  
  // Create a recurring trigger to update the countdown every minute
  ScriptApp.newTrigger("updateCountdown")
    .timeBased()
    .everyMinutes(1)
    .create();
  
  SpreadsheetApp.getUi().alert("Countdown trigger activated! Your timer will now update automatically.");
}

Step 3: Set Up the Auto-Update Trigger #

The countdown needs to run every minute to stay accurate. Do this once:

1. In the script editor, select createCountdownTrigger from the function dropdown menu
2. Click the run button (▶️) and follow the authorization prompts (you’ll need to allow the script to access your sheet)

How It Works #

• getNextUpdateTime(): Figures out the next Monday 7 AM. If today is Monday and it’s still before 7 AM, it uses today’s time. Otherwise, it calculates the next week’s Monday.
• updateCountdown(): Checks if we’ve hit the target time. If yes, it shows a quick "Update in progress..." message then resets to the next week’s countdown. If not, it computes and displays the remaining time.
• createCountdownTrigger(): Sets up a repeating timer to refresh the countdown every minute, and cleans up old triggers to prevent duplicates.

Customization Tips #

• Change the countdown cell: Modify countdownCell = sheet.getRange("A1") to use your desired cell (e.g., B5).
• Adjust the target schedule: Update targetDay, targetHour, or targetMinute in getNextUpdateTime() if you need a different day/time for updates.
• Timezone: The script uses your spreadsheet’s timezone (check File > Settings > Time zone to confirm it’s correct).

Testing #

To verify it works:

1. Run updateCountdown manually from the script editor—you should see the countdown appear in your chosen cell.
2. To test the reset logic, temporarily set targetHour to a few minutes from now, wait until it hits zero, and confirm it automatically resets to the next week’s target.

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