Google Sheets 递增计时功能需求：A2填日期后B2自动启动计时

Got it, let's get that incremental timer working in your Google Sheets! I'll walk you through a custom script solution that starts counting up in B2 as soon as you enter a valid date in A2.

First, fire up your Google Sheet, then click Extensions > Apps Script to open the script editor tab.

Delete any existing code in the editor, then paste this script:

// Triggered when any cell is edited
function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const editedCell = e.range;
  
  // Check if the edited cell is A2 and it contains a date
  if (editedCell.getA1Notation() === "A2" && editedCell.getValue() instanceof Date) {
    const startTime = editedCell.getValue();
    // Store the start time in a hidden cell (C2) to reference later
    sheet.getRange("C2").setValue(startTime);
    
    // Clear any existing timers for this sheet
    deleteExistingTriggers();
    
    // Create a new time-driven trigger to update the timer every second
    ScriptApp.newTrigger("updateTimer")
      .timeBased()
      .everySeconds(1)
      .create();
  } else if (editedCell.getA1Notation() === "A2" && editedCell.getValue() === "") {
    // If A2 is cleared, stop the timer and clear B2
    sheet.getRange("B2").setValue("");
    deleteExistingTriggers();
  }
}

// Updates the timer in B2
function updateTimer() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const startTime = sheet.getRange("C2").getValue();
  
  if (startTime instanceof Date) {
    const currentTime = new Date();
    const timeDiff = currentTime - startTime;
    
    // Calculate hours, minutes, seconds from the difference
    const hours = Math.floor(timeDiff / (1000 * 60 * 60));
    const minutes = Math.floor((timeDiff % (1000 * 60 * 60)) / (1000 * 60));
    const seconds = Math.floor((timeDiff % (1000 * 60)) / 1000);
    
    // Format the time as HH:MM:SS
    const formattedTime = `${padZero(hours)}:${padZero(minutes)}:${padZero(seconds)}`;
    sheet.getRange("B2").setValue(formattedTime);
  }
}

// Helper function to add leading zeros to single-digit numbers
function padZero(num) {
  return num.toString().padStart(2, "0");
}

// Deletes existing updateTimer triggers to avoid duplicates
function deleteExistingTriggers() {
  const triggers = ScriptApp.getProjectTriggers();
  for (let i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === "updateTimer") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
}

Click the Run button (▶️) once to trigger the authorization flow. You'll need to grant the script permission to access your sheet and create time triggers—follow the prompts (you might need to click "Advanced" > "Go to [Your Project Name]" to proceed past the security warning).

Go back to your sheet and:

• Enter a valid date (or date-time) in A2—you’ll see B2 start counting up in HH:MM:SS format right away.
• Clear A2 to stop the timer and reset B2 to blank.

Quick Notes: #

• The script uses cell C2 to store the start time (you can hide this column if you want by right-clicking column C > Hide column).
• Even if you close the sheet, the timer will keep running because the time-driven trigger is server-side.
• If you want a slower update interval (e.g., every 5 seconds instead of 1), change the .everySeconds(1) line to .everySeconds(5) in the onEdit function.

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