In one of our previous article, we have mentioned How to Reinstall Microsoft Store and now in this article, I have provided step by step guide to export google calendar to google sheets, we will divided the article into 2 parts at first part we will export google calendar from gmail account in a zip file and then in second part we will import calendar into google sheets.
Export Google Calendar Events
Here are the steps, which you need to perform to export google calendar with events, on your personal computer after logging in into your Gmail account.
- On your computer, open Google Calendar, as you can't export your calendar from the Google Calendar app.
- At the top right, click on settings
- To download your events, click Import & export from left menu.
- Under "Export" click Export
- A ZIP file downloads to your computer. If you open the ZIP file, you'll find individual ICS files for each of your calendars.
Import into Google Sheets
Now, we need to download Sheets2GCal add-on for google sheets, which allows you to import Google Calendar events into Google Sheets and also update Calendar from Sheets automatically.
You can find this extension by opening Google Sheets and then select "Extension" -> "Add-On" -> select "Get Add-On", search for "Sheets2GCal" and click "Install"
After installing, go to the Google Sheets "Add-Ons" menu and click either "Import Calendar to Sheet" to import events from a Google Calendar, or "Link Sheet" to set up a new blank Sheet for use with Sheets2GCal.
You will need to specify which Calendar you want to import. You can also set the Start Date and End Date if you want to.
But before you perform any import of calendar into google sheets you need to create an account for Sheets2GCal, which is FREE.
If you are logged in into sheets, with same account, you can select "Extension" -> Select "Sheets2GCal" -> Select "Import calendar" and then select events which you want to import.
Here is the sample output, which I can see when I use above method
Import Google Calendar Events using Scripts
You can also import Google calendar events automatically, by creating a javascript based code script.
Step 1: Create a new blank Google Sheets document.
You can get your google calendar ID, by clicking on Settings Icon at the top right in your gmail account.
On the Settings page, scroll down to the Calendar you want to export. Select "Integrate calendar" from the left menu.
Then, copy the value of Calendar ID displayed in the right pane (calendar ID can be your email id also, like someone@gmail.com)
Step 2: Copy and paste your Calendar ID into the a B1 cell
Step 3: Specify the criteria to filter the calendar data:
- Start date (B2 cell) in the format mm/dd/yyyy hh:mm:ss
- End date (B3 cell) in the format mm/dd/yyyy hh:mm:ss
- Text to search (B4 cell)
Step 4: From the menu, click Extension -> App Script editor and then use the below code in "code.js"
function importGoogleCalendar() {
var sheet = SpreadsheetApp.getActiveSheet();
var calendarId = sheet.getRange('B1').getValue().toString();
var calendar = CalendarApp.getCalendarById(calendarId);
// Set filters
var startDate = sheet.getRange('B2').getValue();
var endDate = sheet.getRange('B3').getValue();
var searchText = sheet.getRange('B4').getValue();
// Print header
var header = [["Title", "Description", "Start", "End", "Duration"]];
var range = sheet.getRange("A6:E6");
range.setValues(header);
range.setFontWeight("bold")
// Get events based on filters
var events = (searchText == '') ? calendar.getEvents(startDate, endDate) : calendar.getEvents(startDate, endDate, {search: searchText});
// Display events
for (var i=0; i<events.length; i++) {
var row = i+7;
var details = [[events[i].getTitle(), events[i].getDescription(), events[i].getStartTime(), events[i].getEndTime(), '']];
range = sheet.getRange(row,1,1,5);
range.setValues(details);
// Format the Start and End columns
var cell = sheet.getRange(row, 3);
cell.setNumberFormat('mm/dd/yyyy hh:mm');
cell = sheet.getRange(row, 4);
cell.setNumberFormat('mm/dd/yyyy hh:mm');
// Fill the Duration column
cell = sheet.getRange(row, 5);
cell.setFormula('=(HOUR(D' + row + ')+(MINUTE(D' +row+ ')/60))-(HOUR(C' +row+ ')+(MINUTE(C' +row+ ')/60))');
cell.setNumberFormat('0.00');
}
}
The above code is based on the Cell information, which I have provided above, so it must be same as stated above, otherwise it will not work.
Step 6: Run the script from the Editor and when you will execute it for first time, grant access to execute the script.
That's it, once script is executed, you will see output in Google Sheets.
But I will suggest you to use first approach since it is much easier and doesn't require you to code anything.
You may also like to read:
Top 5 best CPU for 1440p gaming
Top 5 Best Game Recording Software for PC (FREE)
Best 5+ Alternative Operating Systems for Windows
