published on in HowTo JavaScript
tags: google spreadsheet

Google Calendar and Google Apps Script

Manage Overtime With Google Calendar and Drive with Google Apps Script…

First of all, you have to know:

  • You can create events in Google Calendar
  • You can create recurring events in Google Calendar
  • You can create Spreadsheets in Google Drive
  • You can write scripts for you Spreadsheets in Google Drive
  • Your Google Apps Scripts can reach all of you data in other Google Documents

Create a Google Calendar event

You need to create a new Calendar and give a name like Overtime. When you or one of your collages works overtime then you create a new event.

New, dedicated calendar

When you entered the What and selected your Overtime calendar, click on the Edit event. You are done ;)

Google Drive - Spreadsheet

Now go to Google Drive and create a new Spreadsheet.

New Spreadsheet

Now you can create your template. I created something like this:

Template

Nice 🙂 It’s pretty cool. Each row start with a Month and each column start with a Name.

Write a Google Apps Script

Now we need to create a Script for this sheet with Script editor… under Tools menu.

Open Script Editor

Jump back to your Google Calendar and copy the ID of your recently created Calendar which look like this: fp__lotsofrandomdata__3c@group.calendar.google.com Copy that code (below) into your script editor.

var Configuration = {
  calendarId: 'fp__lotsofrandomdata__3c@group.calendar.google.com'
};

function getOvertime(startDate, endDate) {
  var calendar = CalendarApp.getCalendarById(Configuration.calendarId);
  var events = calendar.getEvents(startDate, endDate);

  var person = {};
  for(var i = 0, _l = events.length; i < _l; i++) {
    var hours = (events[i].getEndTime()-events[i].getStartTime())/1000/60/60;
    var title = events[i].getTitle();

    if (person.hasOwnProperty(title)) {
      person[title] += hours;
    } else {
      person[title] = hours;
    }
  }

  return person;
};

function getColIndexByName(colName) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numColumns = sheet.getLastColumn();
  var row = sheet.getRange(1, 1, 1, numColumns).getValues();
  for (i in row[0]) {
    var name = row[0][i];
    if (name == colName) {
      return parseInt(i) + 1;
    }
  }
  return -1;
};

function updateCurrentLine() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();

  var startDate = new Date(
    sheet.getRange(
      row,
      getColIndexByName("Month")
    ).getValue().replace(",", " 1,")
  );
  var endDate = new Date(
    startDate.getFullYear(),
    startDate.getMonth()+1,
    startDate.getDate()
  );

  var overtime = getOvertime(startDate, endDate);

  for (var key in overtime) {
    if (overtime.hasOwnProperty(key)) {
      var name = key;
      var field = sheet.getRange(row, getColIndexByName(name));
      if (field) {
        field.setValue(overtime[key]);
      }
    }
  }
};

function onOpen() {
  var subMenus = [
    {name:"UpdateCurrentLine", functionName: "updateCurrentLine"}
  ];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("YGenerator", subMenus);
};

Lets see what the hell is it.

First of all we need a helper function. This function receive us a column ID from a name which available in the first row (header).

We need a function to fetch overtime form our calendar. This function gets two parameters. First the start date and the second is the end date. So we have an interval. Gets our calendar form Google Calendar by its ID which defined in our Configuration object. Fetch events in the given interval then fetch all duration and makes an object with Name keys and Duration values in hours (float).

function getOvertime(startDate, endDate) {
  var calendar = CalendarApp.getCalendarById(Configuration.calendarId);
  var events = calendar.getEvents(startDate, endDate);

  var person = {};
  for(var i = 0, _l = events.length; i < _l; i++) {
    var hours = (events[i].getEndTime()-events[i].getStartTime())/1000/60/60;
    var title = events[i].getTitle();

    if (person.hasOwnProperty(title)) {
      person[title] += hours;
    } else {
      person[title] = hours;
    }
  }

  return person;
};

Our next function will call getOvertime and updates our sheet. Each row starts with a Month with Year, so we can generate startDate and endDate from this.

function updateCurrentLine() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();

var startDate = new Date(
    sheet.getRange(
      row,
      getColIndexByName("Month")
    ).getValue().replace(",", " 1,")
  );
  var endDate = new Date(
    startDate.getFullYear(),
    startDate.getMonth()+1,
    startDate.getDate()
  );

var overtime = getOvertime(startDate, endDate);

for (var key in overtime) {
    if (overtime.hasOwnProperty(key)) {
      var name = key;
      var field = sheet.getRange(row, getColIndexByName(name));
      if (field) {
        field.setValue(overtime[key]);
      }
    }
  }
};

Each sheet has a few automatically checked function like onOpen. This event will called when you open your document. We can create a function to create a new menu item 🙂

function onOpen() {
  var subMenus = [
    {name:"UpdateCurrentLine", functionName: "updateCurrentLine"}
  ];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("YGenerator", subMenus);
};

Done. If you reopen your sheet then you see a new menu item. Now if you stay on a line but not the header and click on your new meny item and UpdateCurrentLine submenu then this awesome script starts to run. Now you can see a perfectly filled row.

Updated row