Sync Google Sheets Task Deadlines to a Google Calendar
How can I sync Google Sheets task deadlines to Google Calendar without creating duplicate events on each run?
Use Apps Script to create an all-day Calendar event for each stable task ID, then save its event ID in the sheet. Later runs update that event, and a time-driven trigger schedules the one-way sync.
How should the Tasks sheet store deadlines and event IDs?
Give each task its own row, with a stable Task ID and a blank Event ID cell for the script to fill. Name the worksheet Tasks and put these exact headers in columns A through D.
| Task ID | Event ID | Task | Due Date |
|---|---|---|---|
| TASK-001 | Draft Moonpickle Labs kickoff | 10/05/2026 | |
| TASK-002 | Review Zorbington Studio handoff | 10/06/2026 | |
| TASK-003 | Send Fluffwidget Co launch brief | 10/09/2026 |
The organizations shown here are fictional. Set the spreadsheet locale to United States, format column D as Date, and enter actual date values, not text. Changing text to look like a date does not make it a date value. Before touching Calendar events, the script checks that every due date is a valid date object.
Leave column B blank when you start. Apps Script will put Calendar event IDs there. Give every task a unique Task ID and keep it stable. That ID links the task to its event. Change it and the link breaks. Reuse an old ID and the script may not know which task owns the saved event.
When sorting, select the whole table, including column B, so task rows stay paired with their event IDs. Sorting only part of the table can attach an ID to the wrong task. For a broader remote-operations process, document who owns each handoff and how deadlines work alongside the calendar sync. The remote operations guides cover related handoff workflows.
How do you configure and schedule the Google Sheets deadline sync?
Set the spreadsheet, script, and calendar to the same time zone, add the three required Script Properties, then schedule a time-driven trigger.
-
Choose a calendar. Make a calendar for deadlines or choose one where the executing account can create and edit events.
-
Copy its ID. In the calendar settings, open Integrate calendar and find Calendar ID. Use that value, not the display name. Keep the target calendar fixed once syncing begins.
-
Align time zones. Under File > Settings, set the spreadsheet time zone to the calendar’s zone, such as
America/New_York. -
Open Apps Script. Choose Extensions > Apps Script, paste the full script below, and save it. In Project Settings, set the script time zone to match the spreadsheet and calendar.
-
Add Script Properties. In Project Settings, enter the values below. You can find the spreadsheet ID between
/d/and/editin its address. No API key is needed.Property Value SPREADSHEET_IDYour spreadsheet ID CALENDAR_IDYour target calendar ID SHEET_NAMETasks -
Authorize manually. Select
syncTaskDeadlines, click Run, and follow Google’s authorization prompts. The executing account needs permission to edit the sheet and modify the calendar. -
Schedule runs. Check the output, then open Triggers and create a time-driven trigger for
syncTaskDeadlines, such as an hourly trigger. The account that created it must retain access. Changes show up after a successful run, not immediately after a row edit.
The script compares the named time zones reported by the spreadsheet, script, and calendar. If any differ, it stops before processing tasks. If the spreadsheet also tracks handoff times for people in different locations, convert those times separately. This sync handles all-day deadlines, not handoff times. See how to convert UTC handoff times to local time in Google Sheets.
Which Apps Script creates and updates the all-day deadline events?
The script checks the sheet, updates events using their saved IDs, and uses description markers to recover from some interrupted writes.
A project-wide script lock keeps two executions from running at the same time. LockService provides the lock through getScriptLock. The script validates its input before editing events, but a later Calendar error can still leave only part of the sheet synced. The lock does not make the entire process transactional. After a failed run, check both the sheet and calendar.
function syncTaskDeadlines() {
const lock = LockService.getScriptLock();
lock.waitLock(30000);
try {
const props = PropertiesService.getScriptProperties();
const spreadsheetId = props.getProperty("SPREADSHEET_ID");
const calendarId = props.getProperty("CALENDAR_ID");
const sheetName = props.getProperty("SHEET_NAME");
if (!spreadsheetId || !calendarId || !sheetName) {
throw new Error(
"Set SPREADSHEET_ID, CALENDAR_ID, and SHEET_NAME in Script Properties."
);
}
const ss = SpreadsheetApp.openById(spreadsheetId);
const sheet = ss.getSheetByName(sheetName);
const calendar = CalendarApp.getCalendarById(calendarId);
if (!sheet) {
throw new Error("Worksheet not found: " + sheetName);
}
if (!calendar) {
throw new Error("Calendar not found or not accessible.");
}
const zone = ss.getSpreadsheetTimeZone();
if (
zone !== Session.getScriptTimeZone() ||
zone !== calendar.getTimeZone()
) {
throw new Error(
"Spreadsheet, script, and calendar time zones must match exactly."
);
}
const headers = sheet.getRange(1, 1, 1, 4).getDisplayValues()[0];
const expected = ["Task ID", "Event ID", "Task", "Due Date"];
if (headers.some((header, i) => header !== expected[i])) {
throw new Error("Headers must be: Task ID, Event ID, Task, Due Date.");
}
const lastRow = sheet.getLastRow();
if (lastRow < 2) return;
const values = sheet.getRange(2, 1, lastRow - 1, 4).getValues();
const idFormulas = sheet.getRange(2, 2, lastRow - 1, 1).getFormulas();
const taskIds = new Set();
const eventIds = new Set();
const tasks = [];
values.forEach((row, index) => {
const rowNumber = index + 2;
if (idFormulas[index][0]) {
throw new Error("Row " + rowNumber + ": Event ID cannot be a formula.");
}
if (row.every(value => value === "")) return;
const taskId = String(row[0]).trim();
const eventId = String(row[1]).trim();
const title = String(row[2]).trim();
const due = row[3];
if (!/^[A-Za-z0-9_-]+$/.test(taskId)) {
throw new Error(
"Row " + rowNumber + ": use letters, numbers, _ or - for Task ID."
);
}
if (taskIds.has(taskId)) {
throw new Error("Row " + rowNumber + ": duplicate Task ID.");
}
if (eventId && eventIds.has(eventId)) {
throw new Error("Row " + rowNumber + ": duplicate Event ID.");
}
if (!title || !(due instanceof Date) || isNaN(due.getTime())) {
throw new Error(
"Row " + rowNumber + ": enter a task title and a real Sheets date."
);
}
taskIds.add(taskId);
if (eventId) eventIds.add(eventId);
tasks.push({
rowNumber,
taskId,
eventId,
title,
day: new Date(due.getFullYear(), due.getMonth(), due.getDate())
});
});
for (const task of tasks) {
const marker =
"Managed Sheets task: " + task.taskId +
"\nSource: " + ss.getId() + "/" + sheet.getSheetId();
let event = task.eventId
? calendar.getEventById(task.eventId)
: null;
if (!event) {
const matches = calendar.getEventsForDay(task.day).filter(
candidate => candidate.getDescription() === marker
);
if (matches.length > 1) {
throw new Error(
"Row " + task.rowNumber + ": multiple matching events on due date."
);
}
event = matches[0] || null;
}
if (event) {
if (event.isRecurringEvent() || event.getDescription() !== marker) {
throw new Error(
"Row " + task.rowNumber +
": event ownership mismatch or recurring event."
);
}
event.setTitle(task.title);
event.setAllDayDate(task.day);
} else {
event = calendar.createAllDayEvent(task.title, task.day, {
description: marker
});
}
const savedId = event.getId();
if (savedId !== task.eventId) {
sheet.getRange(task.rowNumber, 2).setValue(savedId);
SpreadsheetApp.flush();
}
}
console.log("Synced " + tasks.length + " task deadlines.");
} finally {
lock.releaseLock();
}
}
The script uses the Calendar service to choose a calendar, find events, search a day, and create all-day events. For events that already exist, the CalendarEvent methods update the title and all-day date. Each event gets a description marker with its task ID, source spreadsheet, and worksheet. That marker is a better match than a title, which someone might change.
Before editing Calendar, the script reads and checks every nonempty row. It catches duplicate task IDs or event IDs, malformed IDs, missing titles, text dates, and formulas in the Event ID column. These checks do not make Calendar operations atomic. If Calendar returns an error after some rows have been handled, the sync may be incomplete. Check the changed rows, correct the problem, and run the script again.
What Calendar events should the sample task rows produce?
The three sample rows should create three single-day, all-day Calendar events, with each generated ID saved in column B.
| Task ID | Calendar event title | All-day date | Sheet result |
|---|---|---|---|
| TASK-001 | Draft Moonpickle Labs kickoff | October 5, 2026 | B2 contains its event ID |
| TASK-002 | Review Zorbington Studio handoff | October 6, 2026 | B3 contains its event ID |
| TASK-003 | Send Fluffwidget Co launch brief | October 9, 2026 | B4 contains its event ID |
The first run processes three nonempty task rows, so the log reads Synced 3 task deadlines. Calendar returns one event ID per task, and the script saves each ID in the corresponding row. These IDs come from Calendar; they are not browser event addresses. On later runs, the script finds each event by its saved ID and updates it rather than creating another one.
Change TASK-002’s deadline to 10/08/2026, and the next successful run moves its event to October 8 while leaving the same ID in B3. Change a task name and the event title changes. The sync runs from Sheets to Calendar: Sheets provides the title and date, and a later successful run overwrites manual Calendar edits to those fields. Changes made in Calendar do not flow back to the sheet.
The script creates events without guests and does not set reminders. Notifications depend on Calendar and account settings, so these events are not an assignment-alert system. For a separate deadline notification, use a workflow intended for that job, such as sending one daily task reminder from Google Sheets without repeat emails.
Deleting a row does not delete its Calendar event. To remove a deadline permanently, delete both the event and its sheet row before the next run. Remove only the row and the event remains orphaned. Delete only the event and the script recreates it from the remaining row. Clearing required fields will not remove an event because validation stops the script.
Why does syncTaskDeadlines update existing Calendar events instead of creating new ones each run?
syncTaskDeadlines finds events by saved IDs and task-specific description markers, then updates them instead of creating replacements on every run.
First, getScriptLock and waitLock prevent overlapping executions within the project. Script Properties identify the spreadsheet, worksheet, and calendar, so the script does not depend on whichever sheet happens to be active. It checks time zones and headers, then reads rows with getValues to retain date objects rather than displayed date strings. Before making Calendar changes, it validates every row and uses sets to catch duplicate IDs. Each deadline is converted to a date at local midnight, representing a calendar day rather than a timed appointment.
For each task, the description marker contains its stable Task ID and the spreadsheet and worksheet IDs. The script first calls getEventById to look up the saved event. If the sheet has no saved reference, getEventsForDay searches the deadline date for an exact marker match, not a title match. Once the script verifies ownership and confirms the event is not recurring, setTitle and setAllDayDate update it. If nothing matches, createAllDayEvent creates an event. The CalendarEvent reference documents the methods used to change titles and all-day dates.
If the event ID changes, the script writes the new ID to column B and calls flush to apply pending spreadsheet changes. It logs the number of tasks and releases the lock in the finally block. This supports routine reruns without duplicate events. The sync still runs in one direction. It cannot resolve arbitrary edits made in both places.
How do you fix rejected dates and broken event associations?
Fix the date, time-zone setting, or task-to-event association behind the error, then run the sync again.
A populated deadline is rejected
Symptom: “enter a task title and a real Sheets date.”
Exact fix: Add a missing title, remove any leading apostrophe from the deadline, check the United States locale, and enter 10/05/2026 again. Changing the cell’s date format may leave its contents as text. To check whether the cell stores a date value, enter =ISNUMBER(D2). It should return TRUE.
Time zones do not match
Symptom: Spreadsheet, script, and calendar time zones must match exactly.
Exact fix: Set all three to the same named zone and run the script again. Choose a regional zone that observes daylight saving rules, not a fixed GMT offset.
Event ownership does not match
Symptom: The script stops updating after rows are rearranged or an event description is changed.
Exact fix: If someone sorted column B by itself, use Sheets version history to restore the row associations. If only the managed description changed, restore its original text. Leave the other event IDs in place. Clearing them can leave old events behind.
An interruption leaves the Event ID blank
Symptom: The event appears in Calendar, but its ID is missing from the sheet.
Exact fix: Keep the original deadline and run the script again. The description marker may let it recover the ID. If the deadline has changed, restore the original date, recover the ID, then enter the new deadline. Recovery checks that date, not the whole calendar. If the event is no longer on the current due date, this method cannot find it.
When should you avoid this scheduled Sheets-to-Calendar sync?
Do not use this script for large deadline lists, two-way editing, or workflows that depend on dependable assignment alerts.
- Large lists: The script makes one Calendar operation per task, which may exceed Apps Script execution limits. A larger integration needs batching and durable checkpoints.
- Two-way editing: Sending Calendar changes back to Sheets requires conflict rules and change tracking. Adding another trigger for this function does not provide either. Choose an integration with documented two-way sync.
- Critical alerts: The script does not configure reminders. Calendar and account settings control notifications. Use explicit assignment notifications if a missed alert could cause operational problems.