如何通过脚本将Google Sheets按钮链接至当前日期？技术求助

Hey there! Let's get that "Vandaag" button working to jump straight to today's date in your Google Sheet. Here's a clear, step-by-step guide to adjust your script:

Step 1: Ensure your button is tied to a dedicated function #

First, make sure your "Vandaag" button is linked to a script function (we'll create/modify this function next). If you haven't assigned a function yet, just click the button, select "Assign script", and note the function name you enter (we'll use goToToday as an example here).

Step 2: Add or update the core date-jump function #

Open your script editor (Tools > Script editor) and add or modify this function. I'll break down the key parts you might need to tweak:

function goToToday() {
  // 1. Choose the sheet you want to target (adjust if needed)
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // If you need to target a specific sheet instead of the active one, use this line instead:
  // const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Your Sheet Name");

  // 2. Get today's date and format it to match your sheet's date format
  const today = new Date();
  // IMPORTANT: Change the date format string to match how dates are displayed in your sheet
  // Examples: "dd/MM/yyyy" for day/month/year, "MM/dd/yyyy" for month/day/year
  const formattedToday = Utilities.formatDate(today, Session.getScriptTimeZone(), "yyyy-MM-dd");

  // 3. Search for today's date in the sheet
  const dateCell = targetSheet.createTextFinder(formattedToday).findNext();

  // 4. Jump to the cell if found, or show an alert if not
  if (dateCell) {
    targetSheet.setActiveRange(dateCell);
    // Optional: Show a confirmation alert
    SpreadsheetApp.getUi().alert("Successfully jumped to today's date!");
  } else {
    SpreadsheetApp.getUi().alert("Couldn't find today's date. Double-check your sheet's date format matches the script!");
  }
}

Key adjustments you need to make: #

• Date format matching: This is the most critical part. The "yyyy-MM-dd" string must exactly match how dates are displayed in your sheet. If your sheet uses dd/MM/yyyy (e.g., 15/09/2024), replace the format string with that.
• Target sheet: If your button needs to jump to a specific sheet (not the one you're currently viewing), swap out the getActiveSheet() line with getSheetByName("Your Sheet Name") and replace the placeholder with your actual sheet name.

Step 3: Link the function to your "Vandaag" button #

If you didn't do this earlier:

1. Click the "Vandaag" button in your sheet
2. Select "Assign script"
3. Type goToToday (no parentheses!) and click "OK"

Troubleshooting quick fixes #

• If the button doesn't find today's date: Double-check that your sheet's date display format matches the format string in the script. Sometimes Google Sheets stores dates as date values but displays them differently—make sure the formatted string in the script matches what you see in the cells.
• Check script logs: If something breaks, open the script editor, go to View > Logs to see error messages that can help you debug.

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