Export Google Calendar to Google Sheets and read the week as rows

Calendars are built to be looked at, not counted. The grid answers where to be at two in the afternoon and says nothing about how many hours went to one client last month, how much of the week belongs to recurring meetings, or whether Thursday is as bad as it feels. A spreadsheet answers all three, which is why the search for a way into Google Sheets happens at the end of a month rather than at the start of one.

The direct route people expect does not exist. Google Calendar has no option to send events to a spreadsheet, and the file it does produce is not one Sheets can read as a table. Closing that gap takes one of three approaches, and the right one depends less on skill than on whether the question will be asked again in thirty days.

Google Calendar writes one format, and a spreadsheet is not it

Stating this plainly saves an afternoon. The only file Google Calendar produces is an iCalendar file, ending in .ics. The export in Settings hands over a zip archive with one .ics per calendar. Exporting a single calendar from its own settings page hands over one .ics file. There is no CSV option and no spreadsheet option in the interface.

The import direction is wider. Google documents that events can be brought in from both .ics and .csv files on a computer, so Calendar reads spreadsheets while refusing to write one. That asymmetry explains why so much advice on this subject looks contradictory. Half of it is describing the import path with the arrow pointed the wrong way.

Two more conditions sit on the export itself, and both stop a plan before it starts. Google states that exporting requires the "Make changes and manage sharing" permission on a calendar, which means a colleague's calendar that can only be viewed will never appear in the archive. And on an account issued by a workplace or a school, an administrator can restrict export settings, so a missing or half empty archive is not always a fault in the steps.

What follows from all of this is simple. Getting rows into Sheets means asking Google Calendar for its events through something other than the export button, or taking the .ics file and translating it.

The script that writes the rows straight into the sheet

This is the route that belongs in the sheet itself, and it is the reason the destination being Google Sheets rather than Excel matters. The spreadsheet can fetch its own data.

Google Apps Script runs inside a Sheets file. Open Extensions, then Apps Script, and the editor opens attached to that spreadsheet. The class that reaches the calendar is CalendarApp, and the method that matters is getEvents(startTime, endTime), documented as returning all events that occur within a given time range. On each event returned, getTitle(), getStartTime(), getEndTime(), getLocation(), getDescription() and getGuestList() supply the fields a timesheet needs. Twenty lines is enough:

function pullLastMonth() {
  const cal = CalendarApp.getDefaultCalendar();
  const events = cal.getEvents(new Date('2026-08-01'), new Date('2026-09-01'));
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  sheet.appendRow(['Title', 'Start', 'End', 'Hours', 'Guests', 'Location', 'Calendar']);
  const rows = events.map(e => [
    e.getTitle(),
    e.getStartTime(),
    e.getEndTime(),
    (e.getEndTime() - e.getStartTime()) / 3600000,
    e.getGuestList().length,
    e.getLocation(),
    cal.getName(),
  ]);
  if (rows.length) sheet.getRange(2, 1, rows.length, 7).setValues(rows);
}

The detail that makes these rows trustworthy is what getEvents does with repetition. A weekly standup is stored in the calendar once, with a rule saying it repeats. getEvents returns the occurrences that fall inside the window, so four or five separate rows arrive rather than one, and a sum of the hours column is a real figure rather than an undercount.

Two columns above are worth arguing for. The guest count turns a meeting into a cost, because thirty minutes with six people on the invitation is three hours of working time. The calendar name matters the moment more than one calendar is in play. CalendarApp.getAllCalendars() returns every calendar the account owns or subscribes to, and CalendarApp.getCalendarById(id) fetches a specific one, so a loop over a chosen few produces one table with a column saying where each row came from.

Keeping the sheet current instead of rebuilding it

A one time pull answers one question. The version worth building answers the same question every month without anyone remembering to do it.

Apps Script supports installable triggers, which the documentation describes as running a function automatically, with time driven triggers executing at specific times or intervals. Attach one to the function above, set it to run daily or weekly, and the sheet rewrites itself. The window then has to be relative rather than hard coded, so the script calculates the first and last day of the period each time it runs instead of holding two fixed dates.

One design decision decides whether this stays useful. A script that clears the sheet and rewrites it keeps the data clean and destroys anything typed into it by hand, which matters because the column people most want is the one the calendar does not hold, such as which client an event belongs to. The way around it is two tabs. The script owns a raw tab and never touches anything else. A second tab reads from it with formulas, adds the human columns, and survives every refresh.

Room is not the constraint. Google documents a ceiling of 20 million cells or 100MB for a spreadsheet created in or converted to Sheets, and a decade of calendar events in seven columns does not come close.

The routes that need no code

Not every situation allows a script, and two alternatives cover the rest.

The first is converting the exported file. An .ics file is plain text, with each event sitting between BEGIN:VEVENT and END:VEVENT, carrying a summary, a start timestamp, an end timestamp and often a location. Anything that can read text can turn those blocks into rows: a short script, a text editor with a good search and replace, or one of the many browser based converters. Two cautions apply, and both distort totals rather than breaking the process. Timestamps are often stored in UTC, which shifts a nine in the morning meeting to a different hour and quietly moves it to a different day. Recurring events are stored once with a rule rather than fifty-two times, so a plain conversion produces a single row where the calendar shows a year of meetings, and every duration figure built from it flatters the schedule.

The second is an add-on from the Google Workspace Marketplace. Several exist specifically to put calendar events into a spreadsheet and refresh them on a schedule, which is a fair trade for anyone who does not want to maintain a script. Pricing and free tiers on these tools move, so the number to trust is the one on the listing today. What to compare is short: whether it can read the calendars you actually need, whether it expands recurring events into occurrences, whether it refreshes on a timer, and what access it asks for on the account. That last point deserves a pause, because a tool that reads a calendar is reading who was met and where, and on a work account an administrator may need to approve it before it installs at all.

Which route fits which job

Route	Setup	Recurring events	Stays current	Fits
Apps Script in the sheet	Twenty lines, one authorisation	Expanded into occurrences	With a time driven trigger	A number wanted every month
Convert an exported .ics	None	Stored as a rule, needs expanding	No	One period, once, no account access needed
Marketplace add-on	Install and authorise	Depends on the tool	Usually yes	Recurring reports without code

What the rows can answer, and what they cannot

Once the events are in columns, the useful work is a pivot table rather than another export, and the questions worth asking are the ones a calendar grid hides.

Hours by week, by calendar, or by the first word of the title come out immediately. Hours weighted by guest count is the figure that changes behaviour, because it separates the half hour with one person from the half hour with nine. Counting distinct titles shows how much of the schedule is the same meeting recurring under one name.

Three corrections need making before any total is quoted. Declined and tentative invitations still occupy rows, so a sum of raw durations counts time that was never spent, and filtering on guest status is the fix. Overlapping events double count, which is why a day can appear to hold twenty-six hours. And the same meeting held on both a shared calendar and a personal one produces two rows with the same title and hour, so deduplicating on title plus start time is worth doing before anything is reported.

The larger gap is everything the calendar never recorded. Two appointments forty minutes apart across town read as two hours of meetings in a spreadsheet and consume half a day in life. Ten minutes of setup before a call and twenty minutes of notes after it appear nowhere, because nothing was ever booked. Working in a calendar that accounts for travel time between appointments puts that cost in front of the person choosing the time, which is a month earlier than a report can raise it. A spreadsheet is good at describing what the schedule contained. It cannot describe what the schedule cost.

Sending rows the other way

Anyone building a schedule in a spreadsheet and pushing it into the calendar is using the one direction Google supports natively, and the format is documented precisely enough to follow.

Only two headers are required. Subject is the event name and Start Date is the first day, written in month, day, year order, as in 05/30/2020. The optional headers are Start Time, End Date, End Time, All Day Event, Description, Location and Private. Times take the form 10:00 AM, and All Day Event and Private take True or False. Google states that the headers must be in English whatever language the interface is set to, and that a field containing a comma, which locations usually do, has to be wrapped in quotation marks.

Two limits shape what this path is good for. Google documents that repeating events imported from a .csv file might not appear as a series and will instead land as a run of one-time events, so a recurring schedule routed through a spreadsheet arrives flattened and has to be edited occurrence by occurrence. And guests and conference data are not imported at all, which means a meeting built in a spreadsheet arrives as an appointment with a title and a time, without the invitees or the video link. For a schedule that has to carry people and links, typing the event into the calendar directly, ideally somewhere that accepts a whole sentence rather than a form, is faster than repairing an import.

What to change first

Decide whether the answer is needed once or every month. Once, convert the exported file and watch the time zone and the recurring rows. Every month, spend twenty minutes on an Apps Script in the sheet, give it a trigger, and split the raw tab from the tab people read. Then act on the thing the rows reveal, which is usually the unbooked time between the meetings rather than the meetings, and that is a change to make in the calendar itself. A Mac calendar built around the gaps is described in the Caltimate feature list.