How to add birthdays and anniversaries from Google Sheets to Google Calendar

The short answer

To add birthdays from a spreadsheet to Google Calendar, make a sheet with a title and a date for each event, then send the rows to a calendar you can edit. Leave out times to make the events all-day. Our Google Sheets add-on, CalendarRows, can send the rows and keep track of which event belongs to each one.

A birthday comes back every year, so one event is not enough. On the free CalendarRows plan, a row with a value in the Repeat column shows an error and is not sent. The free approach below makes five individual event rows for each person. The yearly Repeat option is available on Pro and needs only one row per person.

Google Calendar also has a birthdays calendar fed by Google Contacts. Check in your account to see whether that already covers the people you want to remember. A sheet is useful when people are not in Contacts, or when you want anniversaries, work dates or custom titles alongside birthdays.

The formulas are free to use in Google Sheets without the add-on. Sending the resulting rows with CalendarRows uses its free allowance of 50 calendar changes per day. If you are starting with a CSV, put its data into a sheet first, check the dates, and follow the same preparation steps.

Prepare your birthday list

Create a source tab named People. Put the following headers in row 1, and use one row for each occasion. These names and dates are fictional:

For a birthday, use the actual birth date if you want the title to include the person’s age. For an anniversary, the date can be the original occasion date. Enter dates as date cells, not as text that merely looks like a date. If you do not know a birth year, leave the age out of the title rather than guessing it; use a known month and day with a year you choose for the date calculation.

Type and Color are optional in the source list. The example title formula uses Type, so fill it in or replace that part of the formula with your own wording. CalendarRows accepts Google Calendar color names including Lavender, Sage and Banana, as well as the numbers 1 through 11. Keep the spelling consistent with the names in the CalendarRows documentation.

We leave Guests blank in the event rows. A name in a birthday title is not a guest email address. If you do want to add guests, CalendarRows accepts email addresses separated by commas, semicolons or spaces, up to 50 per event. Anything that is not an email address is ignored and noted in the status.

Birth dates are personal information. Keep the spreadsheet private, share it only with people who need it, and consider whether the calendar and its event titles are visible to others. You can use a title without an age, or a less specific title, if that is more appropriate.

Make five years of events

This helper method makes five upcoming occurrences for each source row. It uses the current date when the formulas calculate: if this year’s date has already passed, the first event is next year. If it is today, the first event is today. For simplicity, handle February 29 separately as described below.

Create another tab for the events. Put these headers in cells A1 through I1: Name, Original date, Type, Source color, Event year, Start date, Title, Guests, Color. The first five columns are helpers. Start date, Title, Guests and Color are the columns you can map when sending. Leave H2 and the rest of the Guests column empty.

In A2, enter this formula, then fill it down for five rows per source entry. For the three example entries, fill A2 down through A16. Keep the source entries together without blank rows.

=INDEX(People!A$2:A,INT((ROW()-2)/5)+1)

ROW() identifies the event row. INT((ROW()-2)/5)+1 selects the first source entry for five rows, then the second for five rows, and so on. In B2, C2 and D2, enter the corresponding formulas and fill each down over the same rows:

=INDEX(People!B$2:B,INT((ROW()-2)/5)+1)
=INDEX(People!C$2:C,INT((ROW()-2)/5)+1)
=INDEX(People!D$2:D,INT((ROW()-2)/5)+1)

Now enter the following in E2 and fill it down. It chooses the year for each occurrence, starting with this year or next year. MOD adds zero through four for the five rows belonging to each source entry.

=YEAR(TODAY())+IF(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))<TODAY(),1,0)+MOD(ROW()-2,5)

In F2, make the event date from that year and the month and day of the original date. Fill it down and format column F as dates:

=DATE(E2,MONTH(B2),DAY(B2))

In G2, make a title. This version adds an age only when Type is exactly birthday. It calculates that age by subtracting the birth year from the event year. For example, Maria’s 2027 event is Maria's birthday (turns 40).

=IF(C2="birthday",A2&"'s birthday (turns "&(E2-YEAR(B2))&")",A2&" "&C2)

Fill G2 down, then enter =D2 in I2 and fill that down too. If you do not want ages, use =A2&" "&C2 in G2 instead. You can also type your own titles after the next step. The formulas use commas as argument separators; your Google Sheets locale may require semicolons instead.

Make the event rows stable before sending. Select the filled cells in A2:I16, or the equivalent range for your list, copy them and paste values over the same range. This freezes the calculated dates and titles. Without this step, the formulas can change when the current date or source list changes, and a later send could update events you intended to keep. Check the resulting rows before continuing.

For a February 29 birthday, check how your calendar handles that date in non-leap years. The DATE formula can roll an invalid date into March, so decide deliberately whether you want February 28 or March 1 in each non-leap year and enter those dates in the pasted values. Check the leap-year rows too. If you want more than five years, create another set of dates deliberately rather than filling these five-row formulas farther down for the same person.

Send the rows to Google Calendar

Check the event tab first: each row should have a Title and a Start date, and neither should be blank. The date should have no time attached. Do not map an All day column for this example; with no time, CalendarRows creates an all-day event. You can leave Guests blank and use the Color values you chose.

1. Install CalendarRows from the Google Workspace Marketplace, then open the sheet and choose Extensions → CalendarRows → Open CalendarRows.
2. On To Calendar, choose the event sheet, its header row and the calendar. The picker lists calendars where you can edit events.
3. Map Title to Title and Start date to Start date. Optionally map Guests and Color. Do not map the helper columns as other fields, and leave Repeat unmapped or empty for this free method.
4. Choose all rows or selected rows, then click Preview. Check the counts and any rows with problems. Preview does not change the calendar.
5. Click Send to Calendar. Check the progress and status cells, then open the calendar and compare a few titles and dates with the sheet.

On Free, each create, update or delete counts as one of 50 calendar changes per day. Ten people with five individual dates each make 50 creates, which fits the daily allowance if you have not used any of it already. A larger list may span several days: a task can pause at the limit and resume after the reset. You can also send selected rows in smaller batches.

CalendarRows adds event ID and status columns to the sheet. Keep both columns and their notes: the ID notes include the calendar and a fingerprint of the row, which help the add-on match rows to events. A CREATED status means an event was made; an ERROR status gives a reason to fix before sending again.

Use a yearly repeat with Pro

If you prefer a single row per person, CalendarRows Pro supports a Repeat column. Put yearly in that column for each birthday or anniversary and map it when you send. The documentation also describes an optional count, an end date or an RRULE line. Check the first event and its later occurrences in your calendar, especially for February 29.

Pro costs $9 per month or $59 per year, tax included. Its allowance is 2,000 calendar changes per day, subject to fair use. The free plan does not send a row with a Repeat value: it reports an error. Leave Repeat empty when using the five-individual-events method.

A repeating event has one title for its series. If you want the title to say a different age each year, the individual rows in the free method let you write a different title for each date. If an age is unnecessary, a title such as Maria's birthday keeps the yearly row simple. See our guide to recurring events for more on the Repeat column and the pricing page for the plans.

Edit, delete and maintain your list

To change one individual event, edit its event row and send it again to the same calendar. CalendarRows sends changed rows and updates the event associated with each row. If Maria has five individual events and you want to change all five titles, edit all five rows. A change to the original People tab will not automatically change the event rows after you have pasted values, so maintain those rows deliberately.

To delete an event, add an Action column, map it, type delete on that event’s row and send again to the same calendar. The status becomes DELETED. For five individual events, use the action on each row whose event you want to remove. Deleting a spreadsheet row alone is not the documented deletion action.

Keep the CalendarRows event ID and status columns and their notes. If you send a row to a different calendar, CalendarRows creates a new event there and the row’s ID points to that new event; the event in the first calendar is not moved or removed. Decide which calendar should hold the list before the first send.

Five years of individual events will eventually run out. Add later years as new, checked rows when you need them, and keep existing IDs with their existing rows. For reminders, review Google Calendar’s own notification settings. Check those settings for the calendar you use and make sure they suit the notice you want.

Common problems

An event is on the wrong day. Check that the source and Start date cells are real dates rather than text, and inspect the pasted event date. CalendarRows uses the spreadsheet’s time zone; check it under File → Settings in Google Sheets. Also check whether a time was included in the date cell or an All day column was mapped.

There are duplicate events. Check whether the same occasion appears twice in the source list, whether the event rows were sent as new rows, or whether you sent them to a different calendar. Keep the ID columns and notes with their rows and preview before sending. A PENDING status means an ID was reserved but the run stopped before confirmation; send again to finish, and CalendarRows will not make a duplicate for that pending row.

A row has a Repeat error on Free. Remove its Repeat value and use individual dated rows, or use Pro for yearly. Preview again to check the row before sending.

A February 29 date looks wrong. Check each generated date, particularly in non-leap years. Decide on February 28 or March 1 for those years and enter it deliberately. For a repeating event, check how your calendar handles February 29.

A row has an unreadable date or another error. Read the status text, correct the cell and send again. A date such as 2026-11-03 is one format documented for CalendarRows; make sure the cell represents the date you intend.

The run pauses before all events are created. Count the creates, updates and deletes against the daily allowance. On Free, the limit is 50 calendar changes per day. Resume after the reset, then check the remaining statuses.

Frequently asked questions

Related

• Create Google Calendar events from Google Sheets
• Import a spreadsheet or CSV into Google Calendar
• Create recurring events from Google Sheets
• Bulk delete Google Calendar events
• CalendarRows documentation
• All guides

Disclosure: we make CalendarRows, the Google Sheets add-on mentioned in this guide. The free formulas for preparing individual event rows work in Google Sheets without it.

Google Sheets, Google Calendar and Google Workspace are trademarks of Google LLC. CalendarRows and Cellbrook are not affiliated with or endorsed by Google.