请求编写Google Sheets中按日期触发UpdateData按钮的JavaScript代码

Hey there! Let's tackle this for you—since Google Sheets uses Google Apps Script (a JavaScript-based environment) instead of regular browser JS, here's a straightforward solution to automate this task:

Step 1: Confirm Your Button's Bound Function #

First, your UpdateData button is linked to an Apps Script function. You can verify this by right-clicking the button → Assign script to get the exact function name (we'll assume it's updateData() for this example—be sure to replace it with your actual function name later).

Step 2: Write the Check & Trigger Function #

Open your Google Sheet, go to Extensions → Apps Script to open the script editor, then paste this code:

function checkDueDateAndUpdate() {
  // Get the active sheet in your spreadsheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Replace "A1" with the cell reference where your dueDate is stored
  const dueDateCell = sheet.getRange("A1");
  const dueDate = dueDateCell.getValue();
  
  // Normalize today's date (strip time to only compare day/month/year)
  const today = new Date();
  today.setHours(0, 0, 0, 0);
  
  // Normalize the due date to match today's format (remove time)
  const normalizedDueDate = new Date(dueDate);
  normalizedDueDate.setHours(0, 0, 0, 0);
  
  // Compare the dates
  if (normalizedDueDate.getTime() === today.getTime()) {
    // Call the function linked to your UpdateData button
    updateData();
    console.log("Today matches the due date—ran updateData() successfully!");
  } else {
    console.log("Due date doesn't match today, no action taken.");
  }
}

Step 3: Set Up a Time-Driven Trigger #

To make this run automatically every day (so it checks if today is the due date without you needing to open the sheet):

1. In the Apps Script editor, click the clock-shaped Triggers icon on the left sidebar.
2. Click Add trigger.
3. Configure the trigger:
   • Choose function to run: checkDueDateAndUpdate
   • Choose deployment type: Time-driven
   • Choose time based on: Day timer
   • Pick a time window (e.g., "9 AM to 10 AM"—select when you want the check to run each day)
4. Save the trigger, then follow the prompts to grant the script permission to access your spreadsheet (this is a standard security step for Google Apps Script).

Important Notes #

• Update the Cell Reference: Replace "A1" in the code with the actual cell where your dueDate is stored.
• Match the Button Function: If your button's linked function isn't named updateData(), replace that in the code with your real function name.
• Date Consistency: Normalizing the dates (stripping time) ensures even if your due date cell has a hidden time stamp (like 00:00), the comparison will work correctly.

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