How to Export Google Calendar to Google Sheets (Quickest Method)

Wondering how you can export Google Calendar to Google Sheets?

Google Calendar is a powerful tool to manage tasks and events.

It may contain birthdays, invoice due dates, milestones, and more.

All your Google Calendar events can be exported to Google Sheets using Apps Script.

Google Sheets is an online spreadsheet program that can be shared with unlimited users. They can collaborate in real-time, and the owner is able to track all the changes using the Version history feature.

Let us create a Google Sheet that automatically captures all the entries from your Google Calendar.

How to Export Google Calendar to Google Sheets

There are a number of third-party add-ons that can be used to sync Google Calendar with Google Sheets. The auto-update feature provided by these services allows you to schedule the syncing.

We won’t use any add-on or third-party services in this article.

We will completely focus on Google Sheets’s built-in capabilities to fetch the data from Google Calendar.

It is a simple process, but make sure to read till the end.

Step #1 – Create Dates Table

For the Apps Script to work, we need a start and end date. Let’s create a table as below,

Note that this is crucial information for Google Sheets Apps Script to work. If your Google Sheets is missing this information, you will encounter an error in the final step.

We suggest you use the same cell references for start (A2) and end (B2) dates. If you use different cell references, then you have to adjust the same in the script, which we will discuss in step 3.

Step #2 – Open Apps Script Window 

Google Sheets is an online spreadsheet program that will open the Apps Script in a new tab of your browser.

Follow the steps below,

• Hover to the main menu and click on “Extensions” tab
• Select the “Apps Script” from the popup
  
• The browser will take you to the new tab, which will look as below
  

Step #3 – Write the Script

We will take help from the ready-to-use script available on GitHub.

Here is the script:

function export_gcal_to_gsheet(){
//your calendar email address herevar mycal = Session.getActiveUser().getEmail();var cal = CalendarApp.getCalendarById(mycal);
//put dates herevar events = cal.getEvents(new SpreadsheetApp.getActiveSheet().getRange('A2').getValue(), new SpreadsheetApp.getActiveSheet().getRange('B2').getValue(), {search: '-project123'});var sheet = SpreadsheetApp.getActiveSheet();var header = [[ "Weekday", "Date", "Event Title", "Event Location" ]]
var range = sheet.getRange(5,1,1,4);
range.setValues(header);for (var i=0;i<events.length;i++) {  var row=i+6;  var details=[[ getWeekDay(events[i].getStartTime()),  events[i].getStartTime() , events[i].getTitle() , events[i].getLocation() ]];
  var range=sheet.getRange(row,1,1,4);
  range.setValues(details); }}
function getWeekDay(date){
  var dayNumber = date.getDay();
  var days = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'];
  return days[dayNumber];}

Now, you need to go back to the Apps Script tab and paste this code, as shown in the following GIF.

Step #4 – Save the Project

Once you are done with the script, it’s time to save it by clicking on the save icon as shown below.

Note that the script is capable of automatically detecting the Google Account you are using for the Apps Script and Google Sheets tab.

Step #5 – Run the Project

In this step, we will execute the script written in step 3.

You will find the Run button in the toolbar section of the App Script window.

• Click on the Run button, and a new dialog box will appear, as shown below
• 
• The script is asking for permission to access your data from the Google Sheets
• Click on the “Review Permissions” button to proceed further
• A new tab will open from where you need to select your Google Account
  
  (If you will be using multiple Google accounts in your browser, then please make sure to select the Google account you are using for Google Sheets and Google Calendar)
• Next, after selecting the Google account, a warning will be displayed, as shown below
  
• Click on the “Advanced” button to see more options
  
• Now, click on “Go to Untitled project (unsafe)” as shown below
  
• A new tab will open, asking you for further permission to grant access to your Google account.
  
• Click on the “Allow” button as shown in the above image

The execution will begin. Go back to the Apps Script tab.

You will see the Execution log as shown below.

If everything is working fine, then you will see the above messages.

Step #6 – See the results

Now, you can go back to your Google Sheets. A new table will be created right below the start and end date table.

The new table will contain all the events and tasks from your Google Calendar.

Step #7 – Create a button

To avoid opening the Apps Script tab every time you wish to see the latest data from your Google Calendar, let’s create a button in Google Sheets.

We will create a drawing by following the steps below,

• Hover to the main menu and click on “Insert” tab
• Select the “Drawing” option from the popup
  
• A new dialog box will open, as shown below
  
• Click on the Shape icon available in the toolbar section. Refer to the following image
  
• Choose the first option, “Shapes”
• You will see the variety of shapes available; we will select the second one for rectangular shape as shown below
  
• Now, use your mouse cursor to draw a shape
• Change the background and border color to green (Hex Code – #188038ff)
• Click on the Text box icon from the toolbar section
  
• Place this text box over the rectangular shape as shown below
  
• Type “Load”
• Change the font color to white (Hex Code – #ffffff)
• Click on the “Save and Close” button at the top

You may have to adjust the position of this button in your spreadsheet. We have put our button at the top of the spreadsheet near the cell “H2”. Refer to the following image.

Step #8 – Connect Apps Script to Button

When you click on the drawing we have created in the previous step, you will notice the three dots as shown below.

Click on those three dots and choose the “Assign script” from the popup.

A new dialog box will appear, asking you the name of the script.

Copy and paste the following name.

export_gcal_to_gsheet

Now, click on the “Ok” button in the green to convert the drawing we created in the previous step to a button.

This way, whenever you click on the Load button, the script will be executed, and the latest data will be fetched.

Conclusion

Syncing Google Calendar with Google Sheets is not hard. But make sure to follow all the steps discussed above correctly.

The final spreadsheet after properly formatting cells and fonts is as below.

In case you face any error or are stuck somewhere, feel free to comment.

Our team of Google Sheets experts is always here to help.

Also, don’t forget to explore our blog section for more tips and tricks about using Google Sheets like a pro.