How to Use Google Apps Script to Automatically Sync Google Calendar Events to Sheets

The Need for Calendar Analytics

Google Calendar is excellent for scheduling, but it is terrible for analytics. If a consulting firm wants to calculate exactly how many hours its employees spent in “Client Meetings” versus “Internal Reviews” over the past month, they cannot easily extract that data from the Calendar interface. To perform historical analysis, calculate billable hours, or generate timesheets, the calendar data must be extracted into a structured database.

Using Google Apps Script, you can build an automated bridge between Google Calendar and Google Sheets. This script will query your calendar, extract the details of every event within a specific timeframe (Title, Start Time, End Time, Description), and log them cleanly into a spreadsheet.

Step 1: Set Up the Google Sheet

The script needs a structured destination to dump the data.

1. Create a new Google Sheet.
2. Name the first tab “CalendarData”.
3. In Row 1, create your headers:
   • A1: Event Title
   • B1: Start Date/Time
   • C1: End Date/Time
   • D1: Duration (Hours)
   • E1: Location/Link

Step 2: Write the Apps Script

Now we will write the code to fetch the calendar data.

1. In the Google Sheet, click on Extensions > Apps Script.
2. Delete the default code and paste the following JavaScript:

function exportCalendarToSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CalendarData");
  
  // Clear any old data below the headers to prevent duplicates on rerun
  var lastRow = sheet.getLastRow();
  if(lastRow > 1) {
    sheet.getRange(2, 1, lastRow - 1, 5).clearContent();
  }

  // Get the default calendar for the logged-in user
  var calendar = CalendarApp.getDefaultCalendar();
  
  // Define the date range (e.g., the last 30 days)
  var today = new Date();
  var thirtyDaysAgo = new Date();
  thirtyDaysAgo.setDate(today.getDate() - 30);
  
  // Fetch all events within this date range
  var events = calendar.getEvents(thirtyDaysAgo, today);
  
  // Prepare an array to hold the data before writing to the sheet (faster performance)
  var exportData = [];
  
  // Loop through every event found
  for (var i = 0; i < events.length; i++) {
    var event = events[i];
    
    var title = event.getTitle();
    var startTime = event.getStartTime();
    var endTime = event.getEndTime();
    var location = event.getLocation();
    
    // Calculate the duration in hours
    var durationMs = endTime.getTime() - startTime.getTime();
    var durationHours = durationMs / (1000 * 60 * 60);
    
    // Add this event's data as a new row in our array
    exportData.push([title, startTime, endTime, durationHours, location]);
  }
  
  // If we found events, write the entire array to the sheet in one batch
  if (exportData.length > 0) {
    sheet.getRange(2, 1, exportData.length, exportData[0].length).setValues(exportData);
  }
}

Understanding the Code

• CalendarApp.getDefaultCalendar() connects to the primary calendar of whoever runs the script. (You can also fetch shared calendars by using CalendarApp.getCalendarById('[email protected]')).
• The script calculates a 30-day window. You can adjust this to pull future events (e.g., the next 7 days).
• It uses a batch operation (setValues) at the very end to write all the data at once, which is significantly faster than writing row-by-row in Google Sheets.

Click the Save (floppy disk) icon.

Step 3: Run and Authorize

To test the script:

1. Click the Run button at the top of the editor.
2. Google will block the script and demand authorization because it is attempting to read your private calendar data.
3. Click Review Permissions, select your Google Account, click Advanced, and click Go to script.
4. Click Allow.

Return to your Google Sheet. It should now be perfectly populated with the last 30 days of your calendar history, including a mathematically precise calculation of the hours spent in each meeting.

Step 4: Automate the Sync

To ensure your spreadsheet is always up to date, you can configure the script to run automatically every night.

1. In the Apps Script editor, click the Clock icon (Triggers) on the left sidebar.
2. Click + Add Trigger in the bottom right.
3. Choose the function exportCalendarToSheet.
4. Set the event source to Time-driven.
5. Set the type of time based trigger to Day timer.
6. Select Midnight to 1am.
7. Click Save.

Conclusion

By treating Google Calendar as a raw data source and piping it into Google Sheets via Apps Script, you unlock profound analytics capabilities. You can now build PivotTables, generate automated invoicing based on meeting durations, and visualize exactly where your company’s time is being spent.

RELATED POSTS

How to Force Google Sheets to Automatically Recalculate Formulas Every Minute

How to Use Google Forms Branching Logic for Custom Surveys

How to Automatically Send an Email from Google Sheets When a Cell Value Changes Using Apps Script

How to Use the Google Sheets COUNTIFS Function for Multiple Conditions

How to Use the Google Sheets SPLIT Function to Separate Text