How to color code Google Calendar events from Google Sheets

The short answer

To color code Google Calendar events from Google Sheets, give each event its own row and add a Color column. Enter a supported color name or a number from 1 to 11, then map Color when you send the rows with our Google Sheets add-on, CalendarRows. A separate Category column can make the choice easier to understand: a formula can turn Meeting into Lavender, for example.

CalendarRows accepts these Google Calendar color names: Lavender, Sage, Grape, Flamingo, Banana, Tangerine, Peacock, Graphite, Blueberry, Basil and Tomato. It also accepts their numbers, 1 through 11. If you use a number, send a test row and check the result in Calendar before relying on it. Names make the intended choice easier to read in the spreadsheet.

The formula and category drop-down in this guide are free to use in Google Sheets without the add-on. They fill and organize cells; they do not send events by themselves. CalendarRows Free allows 50 calendar changes per day, counting each create, update or delete as one change. Preview a small selection first, then check the resulting events and colors.

The CSV import method described in our event creation guide has no color column. It can be useful for a one-time list, but it will not apply the Color values prepared here. CalendarRows sends those values and keeps an event ID with each row so that a later send can update the same event.

Prepare your event sheet

Use one row for each event. For this example, put Title, Start date, Start time, Category and Color in cells A1 through E1. Category is a helper column for people and formulas; Color is the column to map to CalendarRows. The names and dates below are fictional.

Title and Start date are required by CalendarRows. Start time is optional; a date can also contain a time. Enter dates as real date cells rather than text that only looks like a date. The sample times are separate cells, so map Start time as well as Start date. You can add an End time column if the events need specific end times. Without an end, CalendarRows uses the default length selected in its sidebar.

If you leave the time out of an event and do not map an All day column, CalendarRows creates an all-day event. If you map an All day column, its value decides whether that row is all-day. Check the spreadsheet’s time zone under File → Settings, because CalendarRows uses that zone for the rows it sends.

You can type a supported name directly into Color instead of using a formula. That is practical for a few unrelated events. For a larger list, a Category column gives people a short set of choices and lets you keep the color rule in one place. Keep the categories meaningful to the people editing the sheet: the calendar color is a visual cue, while the title still explains what the event is.

Fill Color from a category

With the example layout, enter this formula in E2 and fill it down alongside your event rows:

=IFS(D2="Meeting","Lavender",D2="Deadline","Tomato",D2="Personal","Sage",TRUE,"")

The formula checks Category in D2 and returns the matching name in Color. The last pair returns a blank for an empty or unrecognized category. For example, changing D2 from Meeting to Deadline changes E2 from Lavender to Tomato. The next section shows how to make those category choices available in a drop-down.

A blank result is a useful signal to review the row, but it does not tell you why the category was not matched. Check for a typo or a category that has not been added to the formula. If you want every event to have an explicit color, filter or inspect the Color column for blanks before sending. The formula uses commas as argument separators; your Google Sheets locale may require semicolons instead.

If the list of categories will change, use a small lookup table instead. On a tab named Colors, put Category in A1 and Color in B1, then enter these pairs in A2:B4:

On the event tab, use this alternative in E2 and fill it down. Use either this lookup or the IFS formula in a given Color cell, not both.

=IF(D2="","",IFNA(VLOOKUP(D2,Colors!A$2:B$4,2,FALSE),""))

The lookup searches the first column of the table for an exact category and returns the color from the second. IFNA leaves Color blank when there is no match. To add a category, add its name and a supported color to the table and extend the range in the formula to include the new row. Keep category names unique in the lookup table so that the rule is easy to review.

Changing a lookup color can change the calculated Color value for every event in that category. Review the affected rows before sending them again: each changed event that is updated counts against the daily allowance. If you want to change just one event, give it a category with its own rule or replace that row’s formula with a supported color name. Replacing the formula means that cell will no longer follow later category changes unless you restore the formula.

Add a category drop-down

A data validation drop-down makes the permitted categories visible to editors and reduces spelling differences. Select the Category cells where people will enter events, add a drop-down through Google Sheets data validation, and use Meeting, Deadline and Personal as its choices. Check the available data validation controls in your account. If you use a range for the choices, the category cells on the Colors tab can serve as the source.

Choose a category in each event row and confirm that the adjacent Color cell displays the expected name. The drop-down controls the category in the sheet; it does not itself set a Google Calendar event color. The formula supplies the Color value, and the send step applies the row to the selected calendar.

When you add a category, update both the drop-down choices and the formula or lookup table. For instance, if you add Review but do not add a matching rule, the example formulas leave Color blank. Add the new rule, choose one of the supported names, and test it on a row before using it across the schedule.

Preview and send the events

Before sending, check that every row you want to create has a Title and a valid Start date. Review the formula results in Color and confirm that each name is one of the eleven supported values. For a first test, select one or two rows whose dates, times and colors you can easily recognize.

1. Install CalendarRows from the Google Workspace Marketplace. Open your sheet and choose Extensions → CalendarRows → Open CalendarRows.
2. On To Calendar, choose the event sheet, its header row and an existing calendar. The picker lists calendars where you can edit events.
3. Map Title and Start date to their corresponding columns. Map Start time if you use the sample’s separate times, and map Color to the formula results. Category is a helper, so it does not need to be mapped as an event field.
4. Choose selected rows for the test and 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 selected calendar and compare the test events with their rows.

CalendarRows adds event ID and status columns to the sheet. Keep those columns and their notes with the rows: the ID cell notes contain the calendar and a fingerprint of the row, helping CalendarRows match a row to its event. A CREATED status means a new event was made. If a row shows ERROR, read the reason, fix the row and send it again.

Once the test looks right, preview and send the rest of the rows. On Free, 50 creates would use the daily allowance if no other changes have been made that day. Updates and deletes also count. A task that reaches the limit can resume after the reset, and selected rows can be useful for sending a manageable batch.

Change a color later

To change an event’s color, edit the relevant row and send it again to the same calendar. With the IFS example, choose a different Category and check that Color recalculates to the intended name. If Color is entered directly, replace it with another supported name or number. Preview the selection, confirm that the row is counted as an update, then send and inspect the event.

CalendarRows sends only rows that changed, and each changed row updates its own event. An UPDATED status indicates that the event was updated. Keep the event ID and status columns and their notes in place so the add-on can associate the row with the event. Editing a sheet cell alone does not send a change to Calendar; the send is a separate step.

Be careful when changing a shared lookup rule. If every Meeting changes from Lavender to Peacock, the Color result changes for every row using that rule. Preview the affected rows and allow for one update per event. If the change is only for one meeting, edit that row’s Color value or use a separate category instead of changing the rule for all meetings.

Sending a row to a different calendar creates a new event there and changes the row’s ID to point to the new event. The event in the first calendar is not moved or removed. Decide which calendar the schedule belongs to before sending, and choose it again for later updates.

CalendarRows does not create or rename calendars, and it does not change your calendar list or settings. Pick an existing calendar where you can edit events. This guide uses only the eleven documented event colors; try a test row to see the result in your calendar.

Ways to use event colors

A content calendar can use one category for planning meetings and another for deadlines, as in the example. Add categories for the stages your team actually uses, then assign each a supported color in the lookup table. Keep the event title specific enough to identify the work: a color can distinguish a deadline, but the title should still say which item is due.

For a shift schedule, categories might describe an opening shift, a closing shift and a training shift. Give each shift its own row, date and time, and use the category rule for its color. Check the time zone and test a shift before sending the full schedule. Our shift schedule guide covers preparing that kind of list.

For classes, you could use categories for subjects or for lecture and lab sessions. For project milestones, categories could distinguish reviews, handoffs and deadlines. Choose a small set that people can remember, document the rule in the lookup table, and check that event titles and dates remain clear without relying on color alone.

These examples all use the same workflow: a category produces a supported Color value, Preview shows the proposed calendar changes, and Send applies them. If your schedule already exists in a sheet, you can add the helper and Color columns to that layout rather than rebuilding every row.

Common problems

The Color cell is blank. Check whether Category is empty or differs from the values in the formula or lookup table. A new category needs a matching rule, and a lookup range must include its row. Check that the formula has been filled down to the event row. An intentionally blank category also produces a blank with the example formulas.

The wrong color appears. Compare the Category, the calculated Color value and the mapped Color column. If you entered a number, send a test row and inspect its result before using that number throughout the sheet. If you changed the lookup table, check whether the new rule was intended for every row in that category.

A sheet edit is not reflected in Calendar. Confirm that the Color cell changed, then select the row, preview and send it again to the same calendar. Look for UPDATED in the status. Keep the event ID and status columns and their notes; they help CalendarRows identify the event belonging to the row.

The calendar is missing from the picker. CalendarRows lists calendars where you can edit events. Check your access to the intended calendar and choose an existing one. The add-on does not create or rename calendars.

An event is on the wrong day or time. Check that Start date is a real date and that Start time is mapped if it is in a separate column. Review the spreadsheet’s time zone under File → Settings. If you expected an all-day event, check whether a time was entered or an All day column was mapped.

The run stops before every row is sent. Check the status cells and the daily allowance. On Free, each create, update or delete counts toward 50 changes per day. Resume after the reset. 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.

Frequently asked questions

Related

• Create Google Calendar events from Google Sheets
• Sync Google Sheets with Google Calendar
• Import a spreadsheet into Google Calendar
• Put a shift schedule into Google Calendar
• CalendarRows documentation
• All guides

Disclosure: we make CalendarRows, the Google Sheets add-on mentioned in this guide. The category formulas and data validation drop-down 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.