Bring your Google calendar into a spreadsheet

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 and rather than manual capturing it, I found that Sheets from Google with the App Script is really powerful thanks to the unified Google experience.

To bring in the info, I followed the following 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 → App Scripts
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 top update
8. Go to Insert → Drawing, and draw a button or icon and hit insert
9. On the button, click the 3 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

And 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(<VALUE>,"[h] \h\o\u\r\s m \m\i\n\u\t\e\s")