Bring your Google calendar into a spreadsheet

Published on 27 June 2025 • Estimated reading time: 2 minutes

When it comes to spreadsheets, Excel kicks ass—like it is massively more powerful than anything else out there—but I have recently had to pull Google Calendar info into a spreadsheet. Rather than manual capture, I found that Sheets from Google with App Script is really powerful thanks to the unified Google experience. To bring in the info, I followed these steps:

1. Create a new spreadsheet (I used the awesome https://sheets.new URL to do that).

2. In the spreadsheet, add your start and end dates for the range you want to import. I put start in A1 and end in B1.

3. Click Extensions → Apps Script.

4. In the Code.gs file, drop the following code in:

   // Configuration constants
   // change these as needed
   const START_DATE_CELL = 'A1';
   const END_DATE_CELL = 'B1';
   const HEADER_ROW = 3;
   const HEADER_COL = 2;
   
   // do not change these
   const DATA_START_ROW = HEADER_ROW + 1;
   const NUM_COLS = 3;
   
   function calendar_update() {
       // your calendar email address here
       var mycal = Session.getActiveUser().getEmail();
       var cal = CalendarApp.getCalendarById(mycal);
       var sheet = SpreadsheetApp.getActiveSheet();
   
       // Clear existing data rows
       var currentRow = DATA_START_ROW;
       while (true) {
           var checkRange = sheet.getRange(currentRow, HEADER_COL, 1, NUM_COLS);
           var values = checkRange.getValues()[0];
           var hasData = values.some(cell => cell !== '' && cell !== null && cell !== undefined);
           if (!hasData) { break; }
           checkRange.clearContent();
           currentRow++;
       }
   
       // put dates here
       var events = cal.getEvents(
           sheet.getRange(START_DATE_CELL).getValue(),
           sheet.getRange(END_DATE_CELL).getValue(),
           { search: '-project123' }
       );
   
       var header = [['Date', 'Event Title', 'Duration']];
       var range = sheet.getRange(HEADER_ROW, HEADER_COL, 1, NUM_COLS);
       range.setValues(header);
   
       var rowIndex = DATA_START_ROW;
       for (const event of events) {
           if (event.getTitle() === 'Busy' || event.getTitle() === 'WFH' || event.getMyStatus() === CalendarApp.GuestStatus.NO) {
               continue;
           }
           var duration = (event.getEndTime() - event.getStartTime()) / 3600000;
           var details = [[event.getStartTime(), event.getTitle(), duration]];
           var range = sheet.getRange(rowIndex, HEADER_COL, 1, 3);
           range.setValues(details);
           rowIndex++;
       }
   }
   

5. Set the config at the top of the script and hit Save:

   const START_DATE_CELL = 'A1'; // this is where you specified the inclusive start date to pull from
   const END_DATE_CELL = 'B1'; // this is where you specified the exclusive end date to pull to
   const HEADER_ROW = 3; // the row for where the header for the table will be
   const HEADER_COL = 1; // this is the column where the first part of the header is (A = 1, B = 2, etc...)
   

6. Save and run. You will be asked for auth—this is a one-time approval.

7. The content will be in the sheet now! But let’s make it easy to update.

8. Go to Insert → Drawing, and draw a button or icon, then hit Insert.

9. On the button, click the three dots (⋮) and select Assign Script.

10. For Which script do you want to assign?, put in calendar_update and click OK.

Now you can click that button at any time, and it will update.

As a final awesome trick, you may wish to convert something like 0.25 to a human-readable 15 minutes. I use the formula:

=TEXT(A1,"[h]\h\o\u\r\s m\m\i\n\u\t\e\s")