Using Google Calendar, Sheets, and Apps Script to Track your Consulting Work

Side Hustle Toolkit — Part 1

Have a side hustle, but finding it difficult to keep track of your work?

In this article we’ll explore how to track consulting time as events in Google Calendar, record and summarize key event data and hours worked in a single Google Sheet, and automate updating the summary Sheet with Apps Script.

Recording Work with Google Calendar

Create a Calendar dedicated to Consulting Work

The first thing we need to do is create a dedicated calendar that we’ll use to track our work. While this solution will rely on having a Google (Workspace or Gmail) account, I’ve found it best to create a dedicated Gmail account for my consulting work. For this example, we’ll use the account that I just created for the fictional company Cypress Creek Consulting — cypresscreekconsulting@gmail.com

Let’s head on over to Google Calendar and create a new calendar. You can use the instructions linked here, or use the simple instructions below:

• Open Google Calendar
• Click the Settings (gear) icon, Add calendar, then Create new calendar

• Fill in Name and Description, then click Create Calendar

You’ll receive a confirmation prompt at the bottom of the screen with a link to finish configuring the calendar. Otherwise, a new Consulting Calendar option will appear on the left menu under Settings for my calendars. Click either option to proceed to the next step.

At a minimum, we’ll need to grab the unique address assigned to this calendar by scrolling down to Integrate calendar and copying the full address under Calendar ID. Keep this address handy, we’ll need it a few more times…

Additionally, scroll up to update the following option:

• Auto-Accept Invitations = “Automatically add all invitations to this calendar”

Finally, add the address that we copied above to your address book or Google Contacts on whichever Google accounts you’ll use to track your work. For example, I’ve added a dedicated contact entry for Cypress Creek Consulting’s calendar to both my personal gmail.com (myfullname@gmail.com) account as well as the fictional cypresscreekconsulting@gmail.com account. Be sure to name it something obvious, like “Consulting Calendar” so it will be easy to find in the subsequent steps.

Develop a Standard for Recording Consulting Work

Probably the part of this solution that requires the most attention is determining a standard for how you will track your work. Since we will be using Google Calendar and creating a calendar event to track our work and hours, standards and naming conventions will relate to options in Google Calendar. I’ve gone through several iterations and have found that the following standards work best for me.

• If you have multiple clients, create a new Consulting Calendar per client to keep work separate. You may wish to name the calendars differently for each client such as Globex Consulting Calendar and Springfield Consulting Calendar
• For each project, standardize on a project name or code that will be included in the title line of every calendar event that we create, surrounded by brackets. For example [consulting-project-1]
• It may be helpful to maintain a Google Doc with all of your projects and a brief description of each — including the project code defined above and any project-specific information such as the project contact, scope of work, and even the hourly rate quoted to the customer
• Following the project name, the title of each event should also include a very short summary of the work. For example
  [consulting-project-1]Register Domain Names
• In the description field of the calendar event, include a more verbose description of the work completed or even a bulleted list. For example:
  - Created account for customer on register.com
  - Registered foo.com and bar.net
  - Created A records for customer’s web server 10.1.1.10
• Optionally, add a location where this work was performed. This information may be helpful if you need to report on travel.

Recording Consulting Work as a Calendar Event

Using the standards that we defined above, let’s create a new event in Google Calendar.

• For each event, the start and end times should correspond to the amount of time that you spent working on that part of the project.
• Finally, and most importantly, add the Consulting Calendar address or contact that we just created to the “Add guests” field.

Click Save to store our first event.

Calculating Hours in Google Sheets

Now that we’ve created a method for recording our consulting work using events in Google Calendar, we’ll move on to calculating or weekly or monthly hours worked, per project, so we can easily generate invoices and get paid ;)

Create a Google Sheet to Summarize Hours Worked

Start by creating a new Google Sheet that we’ll use to collect information from Google Calendar. Again, if you are working with multiple clients, it’ll be best to have a 1:1 mapping between each Google Calendar and Google Sheet for each customer. For example, if you have 3 customers, it’ll be best to have 3 separate Google Calendars and 3 separate Google Sheets mapped to each other:

• Globex Consulting Calendar : Globex Consulting Sheet
• Springfield Calendar: Springfield Consulting Sheet
• Capitol City Consulting Calendar : Capitol City Consulting Sheet

In your new Google Sheet, go to the bottom tab Sheet 1 and rename it Events

There are a few other housekeeping tasks that we can take care of now as well. Starting with cell A1 and going to the right (B1, C1, etc), let’s insert the following column headers:

• title
• start_time
• end_time
• location
• description

Highlight the columns for B & C, then in the top menu select
Format → Number → Date Time

Automating Calendar → Sheets with Apps Script

Now that we’ve prepared our spreadsheet, we’ll need to create a script that automatically populates it with event data from Google Calendar. In the Google Sheet menu, select Extensions, then Apps Script. A new tab will appear with a new Apps Script project.

Create an Apps Script project

In the Untitled Project tab that we just opened from Google Sheets, start by giving your project a meaningful name such as Update from Consulting Calendar

In the large box on the screen, REPLACE all of the text (starting with function myFunction) with the following code:

function get_Events(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Events");
var cal = CalendarApp.getOwnedCalendarById("XXXX@group.calendar.google.com");
var events = cal.getEvents(new Date("12/01/2021 12:00 AM"),new Date("12/31/2025 12:00 AM"));
for (var i= 0;i<events.length;i++){
  var title = events[i].getTitle();
  var start_time = events[i].getStartTime();
  var end_time = events[i].getEndTime();
  var location = events[i].getLocation();
  var description = events[i].getDescription();
sheet.getRange(i+2,1).setValue(title);
  sheet.getRange(i+2,2).setValue(start_time);
  sheet.getRange(i+2,3).setValue(end_time);
  sheet.getRange(i+2,4).setValue(location);
  sheet.getRange(i+2,5).setValue(description);
}
Logger.log("Events have been added to the spreadsheet")
}

Before saving our code, copy the address of the calendar that we created earlier and paste it into the code above, replacing XXXX@group.calendar.google.com

Click the save (disk) icon (next to Run) in the top menu.

Once saved, click the Run icon. The first time that you run the script, you’ll pop-up message Authorization required and asking you to Review permissions. Completed the steps that prompt you to verify the app. You may need to click Advanced and Go to Update from Consulting Calendar (unsafe).

Finally, click Allow

Under the Execution log section, you should see the confirmation message of:
Info — Events have been added to the spreadsheet

Go back to your Google Sheet to verify that you are seeing the event that we created earlier. Notice that our events start on row 2, leaving row 1 for our column headers.

Creating an Apps Script Trigger

To automatically update your Google Sheet each time that you add a calendar event, we’ll need to create a trigger. In the leftmost AppsScript menu, select the alarm clock icon for Triggers. At the bottom right of the page, click the blue +Add Trigger button. In the pop-up window, select the following values:

• Choose which function to run: get_Events
• Choose which deployment should run: Head
• Select event source: From calendar
• Enter calendar details: Calendar updated
• Calendar owner email: paste the Calendar ID address that we previously created <replace_this>@group.calendar.google.com

Once you’ve updated the values above, click Save. You should now see a new trigger with the Last Run and Error Rate values empty.

Test the entire workflow

Let’s make sure everything is working together! Go back to Google Calendar and create a new event, being sure that you add the Consulting Calendar as a guest to the calendar event. Don’t forget the standard conventions that we defined earlier. My test event looks something like the following:

Did the Apps Script trigger fire? Refresh the Apps Script Trigger dashboard.

Did a new event get added to our Google Sheet? Refresh the Google Sheet.

We now have a fully integrated solution to track our consulting work from Calendar Events in Google Calendar which is automatically recorded to a Google Sheet where we can summarize and calculate hours worked by project.

What’s Next?

Stay tuned for Part 2 — Automating invoice creation with Google Data Studio.