Create Google Calendar Events from Sheets with Apps Script: Automate Your Scheduling

If your team manages rosters, project milestones, or client appointments in Google Sheets, there is a high chance someone is spending time each week manually copying those entries into Google Calendar. It is repetitive, error-prone, and entirely unnecessary. Apps Script can read your spreadsheet data and push every row into Calendar automatically — complete with the right guests, reminders, descriptions, and colours — in the time it would take to create two or three events by hand.

This guide walks through everything you need to build a reliable Sheets-to-Calendar automation. You will learn how the CalendarApp service works, how to create both timed and all-day events, how to handle Australian date formats correctly, how to add reminders and guests, and how to prevent your script from creating duplicate events when it runs multiple times. Along the way, three practical examples — a staff roster, a project milestone tracker, and a client meeting scheduler — show you how to apply these patterns to real business workflows.

No prior coding experience is assumed. If you have never opened the Apps Script editor, the setup section has you covered before the first line of code.

Understanding CalendarApp: The Foundation

CalendarApp is the Apps Script built-in service that gives your code direct access to Google Calendar. There is no API key to set up and no external library to import — it is available in every Apps Script project the moment you start writing.

The core workflow for creating an event is three steps:

1. Get a reference to the calendar you want to add events to.
2. Prepare the event details (title, start time, end time, options).
3. Call createEvent() or createAllDayEvent() on the calendar object.

Here is the simplest possible example — a script that creates a single timed event on your primary calendar:

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

  var startTime = new Date('2026-03-10T10:00:00');
  var endTime   = new Date('2026-03-10T11:00:00');

  calendar.createEvent(
    'Team Kick-off Meeting',
    startTime,
    endTime,
    {
      description: 'Q1 project kick-off. Please review the brief before attending.',
      location:    '22 Collins Street, Melbourne VIC 3000'
    }
  );

  Logger.log('Event created successfully.');
}

CalendarApp.getDefaultCalendar() retrieves your primary calendar — the one associated with your Google account email address. To target a specific calendar instead (a shared team calendar, for example), replace it with CalendarApp.getCalendarById('your-calendar-id@group.calendar.google.com'). You find a calendar's ID by opening Google Calendar, clicking the three-dot menu beside the calendar name in the left sidebar, selecting Settings and sharing, and scrolling down to the Calendar ID field.

To run this script, open any Google Sheet, click Extensions > Apps Script, paste the code, click Run, and approve the permissions Google requests. The event will appear in your Calendar immediately.

Infographic placeholder: [INFOGRAPHIC: CalendarApp workflow — Sheets row → Apps Script → Calendar event, showing the five key event properties: title, start, end, description, guests]

Setting Up Your Google Sheet

The spreadsheet is the data source for everything that follows. A consistent column structure is essential because the script reads each row by column position. Here is the recommended layout:

A few important notes:

• Column I (Event Created?) is used by the duplicate-prevention logic. The script writes the Calendar event ID into this cell after creating each event, so subsequent runs can check whether an event already exists before creating another one.
• Dates in column B should be formatted as dates in Sheets (not plain text). Select the column and apply Format > Number > Date to ensure Apps Script reads them as Date objects.
• For all-day events, leave Start Time and End Time blank and set All Day? to Yes.
• Guests in column F can be a single email address or a comma-separated list of addresses.

Handling Australian Date Formats

Australian date formatting (DD/MM/YYYY) is one of the most common sources of bugs when Sheets data feeds into Calendar events. JavaScript's new Date() constructor parses dates in MM/DD/YYYY order by default, which means a date like 05/03/2026 (5 March) gets misread as the 3rd of May.

The safest approach is to use Apps Script's Utilities.formatDate() and let Google Sheets provide date values directly as JavaScript Date objects — which happens automatically when a cell is formatted as a date in Sheets.

Here is a helper function that reads a date cell value (already a Date object from Sheets) and combines it with a time string in HH:MM format to produce a properly constructed Date object:

/**
 * Combines a date value from a Sheets cell with an HH:MM time string
 * to create a correctly formed Date object in the Australia/Sydney timezone.
 *
 * @param {Date}   dateValue - The date object from the Sheets cell (column B).
 * @param {string} timeStr   - Time as "HH:MM", e.g. "09:00" or "14:30".
 * @returns {Date} Combined date-time object.
 */
function buildDateTime(dateValue, timeStr) {
  var parts = timeStr.toString().split(':');
  var hours   = parseInt(parts[0], 10);
  var minutes = parseInt(parts[1], 10);

  // Clone the date so we do not mutate the original
  var result = new Date(dateValue.getTime());
  result.setHours(hours, minutes, 0, 0);
  return result;
}

And for formatting a Date object back to a readable DD/MM/YYYY string for logging purposes:

function toAustralianDate(dateValue) {
  return Utilities.formatDate(dateValue, 'Australia/Sydney', 'dd/MM/yyyy');
}

Using 'Australia/Sydney' as the timezone string throughout your script ensures that dates stay anchored to AEST (or AEDT during daylight saving) rather than drifting to UTC. For Queensland-based teams that do not observe daylight saving, use 'Australia/Brisbane'.

Creating Timed Events from Sheet Rows

With the date handling understood, here is the core script that reads your spreadsheet and creates timed Calendar events for every row that has not already been processed.

function createCalendarEventsFromSheet() {
  var sheet    = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data     = sheet.getDataRange().getValues();
  var calendar = CalendarApp.getDefaultCalendar();

  // Column indices (0-based) matching the layout described above
  var COL_TITLE    = 0;  // A
  var COL_DATE     = 1;  // B
  var COL_START    = 2;  // C
  var COL_END      = 3;  // D
  var COL_DESC     = 4;  // E
  var COL_GUESTS   = 5;  // F
  var COL_ALLDAY   = 6;  // G
  var COL_COLOUR   = 7;  // H
  var COL_CREATED  = 8;  // I

  var colourMap = {
    'Blue':    CalendarApp.EventColor.BLUE,
    'Red':     CalendarApp.EventColor.RED,
    'Green':   CalendarApp.EventColor.SAGE,
    'Yellow':  CalendarApp.EventColor.YELLOW,
    'Purple':  CalendarApp.EventColor.GRAPE,
    'Teal':    CalendarApp.EventColor.TEAL,
    'Pink':    CalendarApp.EventColor.FLAMINGO,
    'Orange':  CalendarApp.EventColor.TANGERINE
  };

  var createdCount = 0;
  var skippedCount = 0;

  // Start at row 2 (index 1) to skip the header row
  for (var i = 1; i < data.length; i++) {
    var row = data[i];

    // Skip rows where column A (title) is empty
    if (!row[COL_TITLE]) continue;

    // Skip rows that already have an event ID in column I
    if (row[COL_CREATED]) {
      skippedCount++;
      continue;
    }

    var title   = row[COL_TITLE].toString();
    var dateVal = row[COL_DATE];
    var desc    = row[COL_DESC] ? row[COL_DESC].toString() : '';
    var guests  = row[COL_GUESTS] ? row[COL_GUESTS].toString() : '';
    var isAllDay = row[COL_ALLDAY].toString().toLowerCase() === 'yes';
    var colourLabel = row[COL_COLOUR] ? row[COL_COLOUR].toString() : '';

    try {
      var newEvent;

      if (isAllDay) {
        // Create an all-day event
        newEvent = calendar.createAllDayEvent(title, dateVal, { description: desc });

      } else {
        // Build start and end Date objects from date + time columns
        var startTime = buildDateTime(dateVal, row[COL_START] || '09:00');
        var endTime   = buildDateTime(dateVal, row[COL_END]   || '10:00');

        var options = { description: desc };
        if (guests) {
          options.guests    = guests;
          options.sendInvites = true;
        }

        newEvent = calendar.createEvent(title, startTime, endTime, options);
      }

      // Apply colour coding if specified
      if (colourLabel && colourMap[colourLabel]) {
        newEvent.setColor(colourMap[colourLabel]);
      }

      // Write the event ID back to column I to prevent duplicates
      sheet.getRange(i + 1, COL_CREATED + 1).setValue(newEvent.getId());
      createdCount++;

      // Brief pause to stay within Apps Script quota limits
      Utilities.sleep(200);

    } catch (e) {
      Logger.log('Error on row ' + (i + 1) + ': ' + e.message);
      // Write the error into column I so it is visible in the sheet
      sheet.getRange(i + 1, COL_CREATED + 1).setValue('ERROR: ' + e.message);
    }
  }

  var summary = 'Done. Created: ' + createdCount + ', Already existed (skipped): ' + skippedCount;
  Logger.log(summary);
  SpreadsheetApp.getUi().alert(summary);
}

Image placeholder: [CONTEXTUAL IMAGE: Google Sheets spreadsheet with event data in columns A–I, with the "Event Created?" column populated with Calendar event IDs after the script has run]

All-Day Events vs Timed Events

The CalendarApp service distinguishes between all-day events and timed events at the API level. Passing the wrong type causes the event to appear incorrectly in Calendar, so it is worth understanding the difference clearly.

Timed events require a start Date object and an end Date object, both with specific hours and minutes. They appear as blocks on the Calendar time grid.

// Timed event: 10:00 AM to 11:30 AM on 15 April 2026
var start = new Date('2026-04-15T10:00:00');
var end   = new Date('2026-04-15T11:30:00');
calendar.createEvent('Quarterly Review', start, end);

All-day events use createAllDayEvent() and accept a single date (or start and end dates for multi-day events). They appear as banner events at the top of the day column in Calendar, not on the time grid.

// Single all-day event
var date = new Date('2026-04-25'); // ANZAC Day
calendar.createAllDayEvent('ANZAC Day — Public Holiday', date);

// Multi-day all-day event (end date is exclusive — the event ends before this date)
var startDate = new Date('2026-12-25');
var endDate   = new Date('2026-12-29'); // Event covers 25, 26, 27, 28 December
calendar.createAllDayEvent('Christmas Shutdown', startDate, endDate);

In your spreadsheet, a simple Yes/No flag in column G (as shown in the setup above) lets the script decide which method to call. This approach handles both types cleanly without requiring separate sheets or scripts.

Adding Reminders

Calendar reminders notify attendees before an event. Apps Script supports both email reminders and pop-up reminders, and you can add multiple reminders per event at different lead times.

// Add reminders after creating the event
var event = calendar.createEvent('Client Presentation', startTime, endTime, options);

// Pop-up reminder 30 minutes before
event.addPopupReminder(30);

// Email reminder 24 hours (1440 minutes) before
event.addEmailReminder(1440);

// Email reminder 1 week (10080 minutes) before — useful for milestone events
event.addEmailReminder(10080);

To build reminder preferences into your spreadsheet, add a column J (Reminder Minutes) where you enter the pop-up lead time in minutes. The script reads this and applies it to each event:

var reminderMinutes = row[COL_REMINDER] ? parseInt(row[COL_REMINDER], 10) : 30;
newEvent.addPopupReminder(reminderMinutes);

For most business scheduling use cases, a 30-minute pop-up reminder works well as a default when no preference is specified.

Updating Existing Events

Once an event ID is stored in column I, you can use it to update the event rather than always creating new ones. This is useful when your spreadsheet data changes — for example, a meeting time shifts or additional guests need to be invited — and you want Calendar to reflect the update without creating a duplicate.

function updateCalendarEventsFromSheet() {
  var sheet    = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data     = sheet.getDataRange().getValues();
  var calendar = CalendarApp.getDefaultCalendar();

  var COL_TITLE   = 0;
  var COL_DATE    = 1;
  var COL_START   = 2;
  var COL_END     = 3;
  var COL_DESC    = 4;
  var COL_GUESTS  = 5;
  var COL_CREATED = 8;

  for (var i = 1; i < data.length; i++) {
    var row     = data[i];
    var eventId = row[COL_CREATED] ? row[COL_CREATED].toString() : '';

    // Only process rows that already have an event ID and do not start with ERROR
    if (!eventId || eventId.indexOf('ERROR') === 0) continue;

    try {
      var event = CalendarApp.getEventById(eventId);

      if (!event) {
        Logger.log('Row ' + (i + 1) + ': event ID not found in Calendar. May have been deleted.');
        continue;
      }

      // Apply updated values from the sheet
      event.setTitle(row[COL_TITLE].toString());
      event.setDescription(row[COL_DESC] ? row[COL_DESC].toString() : '');

      var dateVal   = row[COL_DATE];
      var startTime = buildDateTime(dateVal, row[COL_START] || '09:00');
      var endTime   = buildDateTime(dateVal, row[COL_END]   || '10:00');
      event.setTime(startTime, endTime);

      Logger.log('Updated event on row ' + (i + 1) + ': ' + row[COL_TITLE]);
      Utilities.sleep(200);

    } catch (e) {
      Logger.log('Error updating row ' + (i + 1) + ': ' + e.message);
    }
  }

  Logger.log('Update run complete.');
}

Note that CalendarApp.getEventById() retrieves an event by its ID across all calendars the authorised user can access. If the event was created on a shared team calendar rather than the default personal calendar, this still works without specifying the calendar ID first.

Avoiding Duplicate Events

The event ID approach in column I is the most reliable duplicate-prevention strategy for simple use cases: if column I has a value, skip the row. But there is a second scenario worth handling — what if someone clears column I accidentally, or exports the sheet fresh without the IDs? The script would attempt to create events for every row.

A more robust duplicate check queries Calendar before creating an event to see whether one with the same title already exists on that date:

function eventAlreadyExists(calendar, title, startTime) {
  // Search a 1-minute window around the expected start time
  var searchStart = new Date(startTime.getTime() - 60000);
  var searchEnd   = new Date(startTime.getTime() + 60000);

  var existing = calendar.getEvents(searchStart, searchEnd);

  for (var k = 0; k < existing.length; k++) {
    if (existing[k].getTitle() === title) {
      return true;
    }
  }
  return false;
}

Use this inside the main loop as a secondary guard:

if (!isAllDay && eventAlreadyExists(calendar, title, startTime)) {
  Logger.log('Row ' + (i + 1) + ': duplicate found, skipping "' + title + '"');
  skippedCount++;
  continue;
}

This adds a small number of API calls per row but is worth it for scenarios where the event ID column cannot be fully trusted.

Infographic placeholder: [INFOGRAPHIC: Duplicate prevention decision tree — Check column I → If empty, check Calendar by title and date → If not found, create event and write ID → If found, skip]

Colour Coding Events

Colour coding is a low-effort way to make a busy calendar scannable at a glance. The CalendarApp.EventColor enum provides a fixed set of named colours that correspond to the colour options in the Google Calendar UI.

The full set of available colours:

For business calendars, a simple colour convention reduces cognitive load considerably. A common approach for Australian service businesses:

• Red — urgent deadlines, compliance dates, EOFY events
• Blue — client meetings and external appointments
• Green — internal team events and stand-ups
• Yellow — training and professional development
• Purple — project milestones

Map this convention to column H in your spreadsheet and the main script applies it automatically during event creation.

Practical Example 1: Staff Roster to Calendar

A hospitality, retail, or healthcare business typically manages a weekly roster in Google Sheets. Converting that roster into Calendar events gives each staff member a visible record of their shifts without requiring a separate rostering app.

Set up a roster sheet with columns: Staff Name, Date, Shift Start, Shift End, Role/Location, and Event Created?

Each row represents a single shift. The calendar used here is a shared team calendar (not individual calendars), so all shifts are visible to managers in one place:

function syncRosterToCalendar() {
  var ROSTER_CALENDAR_ID = 'your-roster-calendar@group.calendar.google.com';
  var sheet    = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data     = sheet.getDataRange().getValues();
  var calendar = CalendarApp.getCalendarById(ROSTER_CALENDAR_ID);

  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    if (!row[0] || row[5]) continue; // Skip if no name or already created

    var staffName  = row[0].toString();
    var shiftDate  = row[1];
    var startTime  = buildDateTime(shiftDate, row[2] || '09:00');
    var endTime    = buildDateTime(shiftDate, row[3] || '17:00');
    var roleLabel  = row[4] ? row[4].toString() : '';

    var title       = staffName + (roleLabel ? ' — ' + roleLabel : '');
    var description = 'Shift for ' + staffName + '.\n' +
                      'Date: ' + toAustralianDate(shiftDate) + '\n' +
                      'Start: ' + row[2] + '   Finish: ' + row[3];

    try {
      var event = calendar.createEvent(title, startTime, endTime, {
        description: description
      });

      event.addPopupReminder(60); // 1-hour reminder before shift starts

      sheet.getRange(i + 1, 6).setValue(event.getId());
      Utilities.sleep(200);

    } catch (e) {
      sheet.getRange(i + 1, 6).setValue('ERROR: ' + e.message);
    }
  }

  Logger.log('Roster sync complete.');
}

Image placeholder: [CONTEXTUAL IMAGE: Google Calendar week view showing colour-coded staff shifts across Monday–Friday, each labelled with staff name and role]

Practical Example 2: Project Milestones

Project milestones are natural candidates for all-day events. A Sheet that tracks deliverables, due dates, and owners can feed directly into a project Calendar that all team members subscribe to:

function syncMilestonesToCalendar() {
  var MILESTONES_CALENDAR_ID = 'your-milestones-calendar@group.calendar.google.com';
  var sheet    = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data     = sheet.getDataRange().getValues();
  var calendar = CalendarApp.getCalendarById(MILESTONES_CALENDAR_ID);

  // Expected columns: A=Milestone, B=Date, C=Owner Email, D=Status, E=Event Created?
  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    if (!row[0] || row[4]) continue;

    // Skip milestones marked Complete
    if (row[3] && row[3].toString().toLowerCase() === 'complete') continue;

    var milestoneTitle = row[0].toString();
    var dueDate        = row[1];
    var ownerEmail     = row[2] ? row[2].toString() : '';

    var description = 'Project milestone: ' + milestoneTitle + '\n' +
                      'Due: ' + toAustralianDate(dueDate) + '\n' +
                      'Owner: ' + ownerEmail;

    try {
      var options = { description: description };
      if (ownerEmail) {
        options.guests    = ownerEmail;
        options.sendInvites = true;
      }

      var event = calendar.createAllDayEvent(milestoneTitle, dueDate, options);

      // Colour milestones red to stand out against regular calendar events
      event.setColor(CalendarApp.EventColor.RED);

      // Email reminder one week before the due date
      event.addEmailReminder(10080);

      sheet.getRange(i + 1, 5).setValue(event.getId());
      Utilities.sleep(200);

    } catch (e) {
      sheet.getRange(i + 1, 5).setValue('ERROR: ' + e.message);
    }
  }

  Logger.log('Milestone sync complete.');
}

Practical Example 3: Client Meeting Scheduler

A client-facing business can maintain a Sheets-based appointment schedule that automatically creates Calendar events and sends invites to the client's email address. This removes the need to manually create events after each booking:

function syncClientMeetingsToCalendar() {
  var sheet    = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data     = sheet.getDataRange().getValues();
  var calendar = CalendarApp.getDefaultCalendar();

  // Columns: A=Client Name, B=Client Email, C=Date, D=Start Time, E=End Time,
  //          F=Meeting Type, G=Notes, H=Event Created?
  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    if (!row[0] || row[7]) continue;

    var clientName  = row[0].toString();
    var clientEmail = row[1] ? row[1].toString() : '';
    var meetingDate = row[2];
    var startTime   = buildDateTime(meetingDate, row[3] || '09:00');
    var endTime     = buildDateTime(meetingDate, row[4] || '10:00');
    var meetingType = row[5] ? row[5].toString() : 'Meeting';
    var notes       = row[6] ? row[6].toString() : '';

    var title       = meetingType + ' — ' + clientName;
    var description = 'Client: ' + clientName + '\n' +
                      'Date: ' + toAustralianDate(meetingDate) + '\n' +
                      'Time: ' + row[3] + ' – ' + row[4] + ' AEST\n' +
                      (notes ? '\nNotes: ' + notes : '');

    var options = { description: description };
    if (clientEmail) {
      options.guests    = clientEmail;
      options.sendInvites = true;
    }

    try {
      var event = calendar.createEvent(title, startTime, endTime, options);

      // Blue for client meetings
      event.setColor(CalendarApp.EventColor.BLUE);

      // 1-day and 1-hour reminders for client appointments
      event.addEmailReminder(1440);
      event.addPopupReminder(60);

      sheet.getRange(i + 1, 8).setValue(event.getId());
      Utilities.sleep(200);

    } catch (e) {
      sheet.getRange(i + 1, 8).setValue('ERROR: ' + e.message);
    }
  }

  Logger.log('Client meeting sync complete.');
}

A calendar-based view of upcoming client appointments, with invites sent automatically and reminders firing 24 hours and 1 hour before each session, removes significant scheduling overhead for consultancies, clinics, and professional services firms.

Running the Script Automatically with a Trigger

Running these scripts manually is fine for an initial bulk import, but the real value comes from running them automatically whenever new rows are added to the spreadsheet.

Option 1 — Time-based trigger (recommended for most use cases):

Set the script to run every hour or every morning:

1. In the Apps Script editor, click the clock icon in the left sidebar (Triggers).
2. Click Add Trigger.
3. Set Function to run to createCalendarEventsFromSheet (or whichever function you are using).
4. Set Event source to Time-driven and Time-based trigger type to Hour timer (every 1 hour) or Day timer (once per day at a time you choose).
5. Click Save.

Option 2 — On edit trigger (for small sheets where new rows are infrequent):

An onEdit trigger fires whenever the spreadsheet is edited. This can create events in near-real time when a new row is added:

function onNewRowAdded(e) {
  // Only fire when editing column A (the Title column)
  if (e && e.range.getColumn() === 1) {
    createCalendarEventsFromSheet();
  }
}

Set this as an Installable trigger (not a simple trigger) under the Triggers panel, because simple onEdit triggers cannot call services like CalendarApp that require authorisation.

For a roster or milestone sheet updated by a manager once a week, a daily trigger is clean and avoids unnecessary API calls. For a high-frequency client scheduling sheet used by multiple staff throughout the day, an hourly trigger ensures new bookings are in Calendar within the hour.

Affiliate & Partner Programs

If you are building on Google Workspace and want to explore the tools that complement the workflows in this guide, the following programs are worth knowing about:

• Google Workspace Referral Program: https://referworkspace.app.goo.gl/ — Apps Script and CalendarApp are included in every Google Workspace plan at no additional cost. Business Starter starts from around $9.40 AUD per user per month and covers everything in this guide. If your team runs on a personal Gmail account, upgrading to a paid Workspace plan also unlocks shared drives, admin audit logging, Google Vault, and the organisational controls that make team-wide calendar automation much easier to manage.
• Google Workspace for Nonprofits: Eligible Australian not-for-profits can access Google Workspace for Nonprofits at no cost through the Google for Nonprofits program. This includes full Apps Script access. Apply via google.com/nonprofits.

For teams that want to go further than Apps Script alone — integrating Google Workspace with external tools like a CRM, project management platform, or payroll system — Zapier and Make both offer Google Calendar connectors and integrate with thousands of other apps. Apps Script remains the better choice for logic-heavy automation that stays within Google's ecosystem.

Conclusion

Google Sheets and Google Calendar are already tools your team uses every day. Apps Script is the bridge between them that eliminates the manual copying that would otherwise happen in between. Whether you are syncing a staff roster, publishing project milestones, or automatically booking client appointments, the same pattern applies: structured rows in a spreadsheet, a script that reads them, a calendar that reflects the result.

The scripts in this guide are production-ready starting points. They handle Australian date formats correctly, prevent duplicate events, apply colour coding, send invites, and store event IDs for future updates. Each one is designed to run on a trigger so you can set it up once and have it maintain your Calendar automatically.

The best place to start is whichever use case is costing your team the most manual effort right now. Set up the spreadsheet structure, copy the relevant script, run it once manually to confirm the events appear correctly, then set the trigger. The whole process takes under 30 minutes.

Your Calendar should reflect how your business runs — not lag behind it because someone has not had time to update it yet.

Need help with your Google Workspace setup? Contact our team for a free consultation.