Google Sheets中查询日期对应法定节假日并返回名称的公式咨询

Great question! Google Sheets doesn’t have a built-in function that directly pulls system-level holiday names, but there are two solid ways to achieve what you’re asking—one using standard formulas (no coding needed) and another with a custom script for automation.

1. Manual Holiday List (No Script Required) #

This is the simplest approach if you only need to cover specific holidays or regions:

• First, create a separate sheet (name it something like Holidays) where you list your holidays:
  • Column A: Holiday dates (e.g., 1/1/2018)
  • Column B: Corresponding holiday names (e.g., New Year's Day)
• In your main sheet, use this formula in cell B1 to match the date in A1 to your holiday list:

  =XLOOKUP(A1, Holidays!A:A, Holidays!B:B, "", 0)
  

  • The fourth parameter ("") defines what to return if the date isn’t a holiday—you can change this to "Not a holiday" or another message if you prefer.
• If you’re using an older version of Google Sheets without XLOOKUP, use VLOOKUP instead:

  =IFERROR(VLOOKUP(A1, Holidays!A:B, 2, FALSE), "")
  

  IFERROR handles cases where the date isn’t found, returning an empty string (or your custom message).

2. Custom Function with Google Apps Script (Automated Holiday Lookup) #

If you want to pull holidays automatically without maintaining a list, you can use a custom script that taps into Google’s Calendar API:

1. Open your Google Sheet, go to Extensions > Apps Script
2. Delete the default code and paste this script:

   function GET_HOLIDAY_NAME(date) {
     // Validate input is a date
     if (!(date instanceof Date)) {
       return "Invalid date";
     }
     // Replace with your country's holiday calendar ID (example uses US)
     const calendarId = 'en.usa#holiday@group.v.calendar.google.com';
     const start = new Date(date);
     start.setHours(0, 0, 0, 0);
     const end = new Date(start);
     end.setDate(end.getDate() + 1);
     
     try {
       const events = Calendar.Events.list(calendarId, {
         timeMin: start.toISOString(),
         timeMax: end.toISOString(),
         singleEvents: true,
         orderBy: 'startTime'
       });
       return events.items.length > 0 ? events.items[0].summary : "";
     } catch (e) {
       return "Error fetching holiday: " + e.message;
     }
   }
   

3. Save the script (name it HolidayFetcher), then click Run—you’ll need to grant permissions for the script to access Google Calendar (follow the on-screen prompts, you may need to click "Advanced" to proceed).
4. Back in your sheet, use the custom function in B1:

   =GET_HOLIDAY_NAME(A1)
   

• To use this for other countries, replace the calendarId with the appropriate ID (e.g., en.uk#holiday@group.v.calendar.google.com for the UK, es.es#holiday@group.v.calendar.google.com for Spain).

内容的提问来源于stack exchange，提问作者Mr. B