How to put a shift schedule from Google Sheets into Google Calendar

From a rota grid to a list

Say your Rota tab has dates across row 2 (B2:H2, real dates, not text like “Mon”), employees down column A from row 3, and a code in each cell: E for early, L for late, N for night, blank or OFF for a day off.

On a new tab called Build, put the headers Employee, Start date and Code in A1:C1, and this in A2:

=ARRAYFORMULA(SPLIT(TOCOL(IF((Rota!B3:H12="")+(Rota!B3:H12="OFF"), , Rota!A3:A12&"|"&Rota!B2:H2&"|"&Rota!B3:H12), 1, TRUE), "|"))

It joins each employee, date and code into one piece of text, skips blanks and OFF, lists everything in date order (that’s the TRUE), and splits it back into three columns. The date comes out as a number such as 46328: select column B and choose Format → Number → Date. Widen B3:H12 and A3:A12 to fit your rota.

We call the column Employee rather than Name on purpose: CalendarRows reads a column called Name as the event title.

Shift codes to start and end times

Make a Codes tab with Code, Start, End, Title and Color in A:E, one row per shift type:

Back on Build, add the headers Start time, End time, Title and Color in D1:G1, and fill each with one formula in row 2:

Format D and E as Time. A #N/A means a code in the rota that isn’t on the Codes tab, which is worth knowing before anything reaches a calendar.

Night shifts that end the next day

A 10:00 PM to 6:00 AM shift ends on the following day, and every calendar tool needs to be told so. Without an end date, the end looks earlier than the start, so the shift can’t be placed correctly: CalendarRows, for example, marks the row “End is not after the start.” instead of guessing. Add an End date header in H1 and:

=ARRAYFORMULA(IF(C2:C="", , IF(E2:E <= D2:D, B2:B + 1, B2:B)))

Whenever the end time is not later than the start time, the end date moves to the next day. Format H as Date.

If staff should get the shift on their own calendars, add a Staff tab with names in A and emails in B, and a Guests column on Build: =ARRAYFORMULA(IF(C2:C="", , IFERROR(VLOOKUP(A2:A, Staff!A2:B, 2, FALSE), ""))).

Hours, totals and coverage checks

With the Build tab in place, a few formulas answer the questions managers ask before the rota goes out. Put the first three in empty columns to the right of Build (J, K and L, say) so they grow with the list, and the totals on a separate Summary tab. We checked the logic on a small test rota that included a night shift and a double booking.

MOD(E2-D2, 1) is what makes overnight shifts work: 6:00 AM minus 10:00 PM is negative, and MOD wraps it to the 8 hours it really is. To find the Monday of a date’s week for Summary!B1, use =A1-WEEKDAY(A1, 3) with any date of that week in A1. Swap the break rule for your own; the 6-hour threshold here is an example, not legal advice. These helper columns are for checking: leave them out when you copy the list to the Shifts tab for CalendarRows.

Stop typos in the rota. Select the grid (Rota!B3:H12) and choose Data → Data validation → Add rule → Dropdown (from a range) with Codes!A2:A. Each cell then offers only the codes you defined (plus blank), so a stray “e” or “Late” can’t reach the calendar.

Getting shifts to your staff

First, create a separate calendar for the rota: in Google Calendar on a computer, click + next to Other calendars → Create new calendar and call it Shifts. Then a bad import can be fixed by deleting one calendar, and nobody’s personal calendar fills up with everyone’s shifts. From there, two options:

• Share the Shifts calendar with the team (Settings and sharing → Share with specific people, “See all event details”). Everyone sees the whole rota. Simple, and nobody gets an email per shift.
• Add each person as a guest on their own shifts, using the Guests column. The shift then shows on their calendar, subject to their own “Add invitations to my calendar” setting. Only the add-on and script methods below can add guests; a CSV import can’t.

1. One-off: CSV import

Google Calendar can import a CSV with the headers Subject, Start Date, Start Time, End Date and End Time. Build those from the Build columns (Title, Start date, Start time, End date, End time), download the tab as CSV, and in Google Calendar go to Settings → Import & export → Import and choose the Shifts calendar. Our CSV import guide has the exact date and time formats.

Fine for a schedule you publish once and never touch. The catch for rotas: importing a corrected week adds every shift again, so a change means deleting and re-importing, and there is no guest or colour column.

2. Free: an Apps Script

The free script in our Sheets-to-Calendar guide writes each event’s ID back into the sheet, so running it again updates shifts instead of duplicating them. It expects a start and end date-time in one cell each, so add two columns to a copy of the list: Start =B2+D2 and End =H2+E2, formatted as Date time. Point it at the Shifts calendar’s ID. It doesn’t add guests or colours as written, and deleting a row doesn’t delete its shift.

3. An add-on: CalendarRows

CalendarRows reads the columns you already built, including Guests, Color and End date.

1. Copy the Build list and paste it as values (Edit → Paste special → Values only) into a tab called Shifts, one tab per rota period. Why values: CalendarRows keeps each event’s ID on its row, and formula output re-sorts itself when the rota changes, which would put IDs next to the wrong shifts.
2. Install CalendarRows, then choose Extensions → CalendarRows → Open CalendarRows. On To Calendar, pick the Shifts tab and the Shifts calendar.
3. Check the suggested columns: Title, Start date, Start time, End date, End time, Guests and Color are recognised from those headers.
4. Click Preview. It lists how many shifts will be created and flags rows with problems, such as a missing end date on a night shift. Nothing changes yet.
5. Click Send to Calendar. Each row gets an event ID and a status (CREATED, or the reason it was skipped).

Guests are added to the shift without CalendarRows sending an invitation email, so a big rota doesn’t flood anyone’s inbox.

When the rota changes

Make the change on the Shifts tab, not just the Rota grid, then send again. Only changed rows are sent:

• New times or a swapped shift: edit the times, or the Employee and Guests cells. Sending again updates the same event.
• A cancelled shift: add an Action column and type delete on that row. The event is removed and the status says DELETED.
• Extra cover: add a row at the bottom. It becomes a new event.
• The same shift every week: CalendarRows Pro reads a Repeat column such as weekly x 12 or weekly until 2026-12-31, so one row becomes a repeating event. On Free, keep one row per shift, which the grid formula gives you anyway.

Troubleshooting

A code shows #N/A on Build. The rota has a code that isn’t on the Codes tab, often with a trailing space. Use the dropdown above, or wrap the lookups as VLOOKUP(TRIM(C2:C), …).

Start or End times are numbers like 0.25. They are times shown as decimals (0.25 is 6:00 AM). Select the column and choose Format → Number → Time.

Times typed as text don’t calculate. If =ISNUMBER(D2) is FALSE, the time is text, often pasted from another system. =TIMEVALUE(D2) converts “6:00 AM” to a real time.

Shifts are an hour off around the clock change. The calendar places each shift at local time on its date, so a night shift that spans the switch to or from daylight saving time lasts 7 or 9 real hours, while the sheet still shows 8. Check those one or two nights by hand.

The grid formula shows #REF!. Something is typed in the area where its results go. Clear the Build tab below the headers and let the formula fill it.

Frequently asked questions

Related

• How to make a timesheet from Google Calendar in Google Sheets
• How to create recurring Google Calendar events from Google Sheets
• How to create Google Calendar events from Google Sheets
• How to import a spreadsheet or CSV into Google Calendar
• CalendarRows documentation
• All guides

Disclosure: we make CalendarRows, the Google Sheets add-on mentioned in this guide. We describe the other methods as fairly as we can, including when they are the better choice.

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