The CData Excel Add-In for Google Calendar enables you to edit and save Google Calendar data directly from Excel. This article explains how to transfer data from Excel to Google Calendar. This technique is useful if you want to work on Google Calendar data in Excel and update changes, or if you have a whole spreadsheet you want to import into Google Calendar. In this example, you will use the VacationCalendar table; however, the same process will work for any table that can be retrieved by the CData Excel Add-In.
Establish a Connection
If you have not already done so, create a new Google Calendar connection by clicking From Google Calendar on the ribbon.
You can connect to Google APIs on behalf of individual users or on behalf of a domain. Google uses the OAuth authentication standard. See the "Getting Started" section of the help documentation for a guide.
Retrieve Data from Google Calendar
To insert data into Google Calendar, you will first need to retrieve data from the Google Calendar table you want to add to. This links the Excel spreadsheet to the Google Calendar table selected: After you retrieve data, any changes you make to the data are highlighted in red.
- Click the From Google Calendar button on the CData ribbon. The Data Selection wizard is displayed.
- In the Table or View menu, select the VacationCalendar table.
- In the Maximum Rows menu, select the number of rows you want to retrieve. If you want to insert rows, you need to retrieve only one row. The Query box will then display the SQL query that corresponds to your request.
- In the Sheet Name box, enter the name for the sheet that will be populated. By default the add-in will create a new sheet with the name of the table.
Insert Rows to Google Calendar
After retrieving data, you can add data from an existing spreadsheet in Excel.
- In a cell after the last row, enter a formula referencing the corresponding cell from the other spreadsheet; for example, =MyVacationCalendarSheetInExcel!A1. Settings.
- In the menu on the left, click Import & Export.
- Click Select file from your computer and select the file you exported. The file should end in ".ics" or ".csv."
- Choose which calendar to add the imported events to.
- By default, events are imported into your primary calendar.
- Click Import.
If you have a .zip file, find it on your computer and open it. You'll find .ics files for each of your calendars. Take the individual files out of the .zip file, and import each .ics file individually.
If you import repeat events from a .csv file, they might not show up that way. They'll be on your calendar as a series of one-time events.
Advanced: Create or edit .csv or iCal files before you import
Create or edit a .csv file
You can import .csv (comma separated values) files into Google Calendar. If you get an error when you import a .csv file, you might be able to fix the formatting.
Open a .csv file or create one with a spreadsheet editor like Google Sheets.
The first row of your new spreadsheet includes headers like “Subject” and “Start Date.” To import into Google Calendar, the headers must be in English. For the correct headers you need to import into Calendar, go to the list below.
Each row below the header row represents an event. For example:
SubjectStart dateStart timeFinal exam05/30/202010:00 AMWhen you're done, save the file as a .csv file. Then, to import the file into Google Calendar, follow the steps in “Import events into Google Calendar.”
Format headers & events in .csv files
Only the first 2 headers in this list are required. The rest are optional.
Important: The headers must be in English as shown in this article. If any event details have commas (like the location example given), you can include them with quotation marks around the text.
Subject
(Required) The name of the event
Example: Final examStart Date
(Required) The first day of the event
Example: 05/30/2020Start date0
The time the event begins
Example: Start date1Start date2
The last day of the event
Example: 05/30/2020Start date4
The time the event ends
Example: Start date5Start date6
Whether the event is an all-day event. - If it’s an all-day event, enter
Start date7. - If it isn’t an all-day event, enter
Start date8.
Example: Start date8
Start time0
Description or notes about the event
Example: Start time1Start time2
The location for the event
Example: Start time3Start time4
Whether the event should be marked private.- If it’s private, enter
Start date7. - If it isn’t private, enter
Start date8.
Example: Start date7
These examples would create the following event:
- Event: "Final exam" on May 30, 2020 10:00 AM–1:00 PM
- Location: "Columbia, Schermerhorn 614"
- Description: "50 multiple choice questions and two essay questions"
- Private: Private event
Create or edit an iCalendar file
iCalendar files are a standard calendar format used to transfer calendar data. If you get an error when you import an iCalendar file (.ics), you might be able to fix the formatting.
- With a text editor application that can save .ics files, open an .ics file or create one.
- Format your file with the guidelines below.
- You can export an .ics file from Google Calendar to use as an example.
- When you're done, save the file as an .ics file.
Format iCalendar files
The first line in an iCalendar file must always be the header Start time8. Other header information, such as Start time9 and Final exam0{enter ID information here}Final exam1, must follow this header. The last line of the file must be the footer Final exam2. Between these lines, enter all the events in the calendar. Each event must be between Final exam3 and Final exam4 lines.
If you must manually edit an iCalendar file, make sure that each file contains the header and footer. If you're not sure where your header ends, copy and paste the text until one line above Final exam3. This location is where your header ends and your event data starts.
Here's what an iCalendar file looks like. An iCalendar file can also have more information, but these are the required parts.
Can I link Excel spreadsheet to Google Calendar?
The Google Calendars Excel Add-In is a powerful tool that allows you to connect with live data from Google Calendars, directly from Microsoft Excel. Use Excel to access Calendar data like Calendars, Events, Attendees, etc.
Can you Import a spreadsheet into Google Calendar?
Go to 'Settings and Sharing' again. Click 'Import' Locate the CSV file you exported on your local computer. Select the correct calendar you want to import into (by default it will select your main calendar)
