Home›Guides

Export Google Calendar to Google Sheets automatically

A one-off export is out of date the next morning. If a report, a dashboard or a timesheet depends on your calendar, the sheet has to refresh itself.

What “automatic” can and cannot mean

It helps to be exact about the word before choosing a tool.

• Scheduled, not instant. Google lets add-ons run on a timer at most once an hour. Daily refresh runs once a day, in the hour you choose. A change you make in the calendar at noon shows in the sheet after the next run.
• One-way. The sheet is rewritten from the calendar. Editing a cell in the sheet does not change the event.
• A moving date range. A refresh that always exported 1 to 31 October would be useless in November. Daily refresh follows This month, Last month, Last 30 days or Next 30 days.

Set it up

1. Install DropScribe Calendar Export from the Google Workspace Marketplace and approve the permissions Google shows you.
2. Open a spreadsheet in Google Sheets and follow the menu below. The panel opens on the right.

Extensions ›then DropScribe Calendar Export ›then Open

3. Under Calendars, tick the ones to export.
4. Under Date range, choose a moving range such as This month or Last 30 days.
5. Switch on Daily refresh and pick the hour beside “Every day around”.

The panel shows when the last run happened and how many events it exported.

The export lands in a sheet named Calendar export, and every run rewrites that same sheet. The first run comes at the hour you picked, later today or tomorrow. To fill the sheet straight away, set Save to to The same sheet and click Export events.

Build on top of it

Because the same sheet is rewritten every day, anything that reads from it stays current. Keep your own work on a separate sheet, because the Calendar export sheet is cleared at each run.

• A total per calendar. The formula below lists every calendar with its hours.
• A chart or a pivot table. Point it at the Calendar export sheet and it redraws after each refresh.
• A timesheet. The timesheet guide has the formulas for totals by client.

=QUERY('Calendar export'!A:E, "select A, sum(E) where A is not null group by A label sum(E) 'Hours'", 1)

Doing it with a script instead

If you would rather not use an add-on, Google Apps Script can do the same job: a function that reads your events and writes them into the sheet, and a time-driven trigger that runs it every day. It is free, and it is yours to maintain. You handle the date range, repeating events, all-day events, time zones and the six-minute limit on each run. The add-on is that script already written, with a panel to control it.

Good to know

• One refresh per spreadsheet. For a second set of calendars or dates, use a second spreadsheet.
• It reads the calendars of the person who switched it on. In a shared spreadsheet, colleagues see the exported rows, and the refresh keeps running under your account.
• The hour follows the spreadsheet's time zone. Google starts the run at some point within that hour.
• If Pro ends, the refresh pauses. The panel says so, and the sheet keeps its last export.

Questions