Sync Sheet Data to Calendar

One of the most powerful patterns in Apps Script is syncing a Google Sheet to Google Calendar — creating calendar events from spreadsheet rows, storing event IDs back in the sheet, and updating or deleting events when the data changes.

The Core Pattern: Store Event IDs in the Sheet

The key to a reliable sync is saving the calendar event ID back to the sheet row after creating the event. This lets you find and update or delete the exact event later.

Add a column Event ID (column I) to your Sales Tracker for this purpose.

Full Sync: Create or Update Events from Sheet

function syncDealsToCalenar() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 9).getValues();
  // Columns: [Customer Name, Email, Product, Amount, Region, Sales Rep, Status, Date, Event ID]

  var calendar = CalendarApp.getDefaultCalendar();

  data.forEach(function(row, i) {
    var customerName = row[0];
    var product = row[2];
    var salesRep = row[5];
    var status = row[6];
    var dealDate = row[7] ? new Date(row[7]) : new Date();
    var existingEventId = row[8];

    if (status !== "In Progress") return;

    // Schedule follow-up 2 days after the deal date
    var start = new Date(dealDate.getTime() + 2 * 24 * 60 * 60 * 1000);
    start.setHours(10, 0, 0, 0);
    var end = new Date(start.getTime() + 30 * 60 * 1000);

    var title = "Follow-up – " + customerName + " (" + product + ")";
    var description = "Sales rep: " + salesRep + "\nStatus: " + status;

    if (existingEventId) {
      // Update the existing event
      var event = calendar.getEventById(existingEventId);
      if (event) {
        event.setTitle(title);
        event.setTime(start, end);
        event.setDescription(description);
        Logger.log("Updated event for: " + customerName);
        return;
      }
    }

    // Create a new event and store its ID
    var newEvent = calendar.createEvent(title, start, end, { description: description });
    sheet.getRange(i + 2, 9).setValue(newEvent.getId());
    Logger.log("Created event for: " + customerName + " | ID: " + newEvent.getId());
  });
}

Deleting Events When a Deal is Removed

function removeCalendarEventsForCancelledDeals() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 9).getValues();
  var calendar = CalendarApp.getDefaultCalendar();

  data.forEach(function(row, i) {
    var status = row[6];
    var eventId = row[8];

    // If status changed to Closed or deal removed, clean up the calendar event
    if (status === "Closed" && eventId) {
      var event = calendar.getEventById(eventId);
      if (event) {
        event.deleteEvent();
        sheet.getRange(i + 2, 9).clearContent(); // Clear the stored ID
        Logger.log("Deleted calendar event for closed deal: " + row[0]);
      }
    }
  });
}

Triggering Sync on Edit

Combine the sync with an onEdit trigger so the calendar stays live as the sheet changes:

function onEditInstallable(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() !== "Sales Tracker") return;

  var editedCol = e.range.getColumn();

  // Re-sync if Status (col 7) or Date (col 8) changed
  if (editedCol === 7 || editedCol === 8) {
    syncDealsToCalenar();
  }
}

Reading the Sheet to Build a Weekly Agenda Email

This combines calendar reading with Gmail to send a Monday morning agenda showing all this week's sales-related events:

function sendWeeklyAgendaEmail() {
  var calendar = CalendarApp.getDefaultCalendar();

  var monday = new Date();
  monday.setDate(monday.getDate() - monday.getDay() + 1);
  monday.setHours(0, 0, 0, 0);

  var friday = new Date(monday.getTime() + 4 * 24 * 60 * 60 * 1000);
  friday.setHours(23, 59, 59);

  var events = calendar.getEvents(monday, friday);

  var rows = events.map(function(event) {
    var day = Utilities.formatDate(event.getStartTime(), Session.getScriptTimeZone(), "EEE, MMM dd");
    var time = Utilities.formatDate(event.getStartTime(), Session.getScriptTimeZone(), "h:mm a");
    return "<tr><td>" + day + "</td><td>" + time + "</td><td>" + event.getTitle() + "</td></tr>";
  }).join("");

  var htmlBody = `
    <h2>Your Week Ahead – Acme Corp Sales</h2>
    <table border="1" cellpadding="8" cellspacing="0" style="border-collapse:collapse;">
      <thead style="background:#4A90D9;color:#fff;">
        <tr><th>Day</th><th>Time</th><th>Event</th></tr>
      </thead>
      <tbody>${rows || "<tr><td colspan='3'>No events this week.</td></tr>"}</tbody>
    </table>
  `;

  GmailApp.sendEmail(
    "[email protected]",
    "Your Week Ahead – " + Utilities.formatDate(monday, Session.getScriptTimeZone(), "MMM dd"),
    "",
    { htmlBody: htmlBody }
  );

  Logger.log("Weekly agenda sent.");
}