Record time and activities with Google Sheets, Calendar and Apps Script
Keeping a record of time spent on tasks can be a challenge. Should you update daily, weekly, make notes, use a spreadsheet or just a piece of paper? The dreaded monthly email, please enter your time, it is the end of the month and we need to send the invoices. I have been in situations where I needed to enter the time in three systems, one of them being an terminal based program.
Keeping the records in sync, find a frictionless way to record daily time became important to me. The first iteration of getting control over the process was maintaining my own spreadsheet, in my own format, and at the end of the week just copy paste the data into all the systems. Some optimization was possible, once the data was organised in the sheet I could order it in a way to make the copy paste action easier.
There was still lots of room for further optimization, mainly by entering the time easier and defer some of the bulk work to a later point in time. Google Calendar is great to use as the place to input time. Create a secondary calendar and start recording time.
Most of the interface ito write time is already there. Just add a title that is enough to remember what the record is about. Then at the end of the week, open a google sheet and with a single action all entries are imported into the time spreadsheet. The records can use some structuring so we can report on project, customer and task.
Once the customer, project and tasks are selected the description can be copied and maybe cleaned up a bit. At this point the time records are structured and can be used in other processes.
The QUERY function is a great way to build the dashboard. Or make a quick and dirty overview of the hours spend on Project X.
Interested? Here is how to set up the sheet.
Disclaimer, some technical knowledge is required to set the sheet up. The code is open and there are examples in the sheet. If you get stuck feel free to ask me for help in the comments.
The setup part one, first create your secondary calendar where you will enter the time. There is a plus sign just below “other calendars”. Then select the “Create new calendar” option
Give it a nice name and click on the blue button.
Last step for this process, copy the id of the calendar, we need it later
Part two, make a copy of the sheet. In the configuration tab you should paste the id of the calendar you created in step one.
At this point the sheet should work. This is a nice moment to enter some test entries in the calendar, maybe some projects on the code page and play a bit with the sheet to get a feeling on how it works. The menu option myTime will trigger the synchronisation. It will ask for an authorisation the first time. The code is open and explained in the rest of the article.
How it works
The menu option is added via the onOpen Action. The myTime object is constructed using the Crockford’s 2014 object creation pattern mentioned in this talk and in JavaScript: The Good Parts.
The myTime function is kept simple. The usage of setFormulaR1C1 is important to enter the formulas in the sheet. The function uses the Higher-order functions some and forEach to handle the looping in an elegant way.
What’s next
There is still room for optimization, think AI/ML to translate the description into the customer, project and task. Or simply implement a basic #tag system. Automate imports in others systems, or use the calendar to plan your time and then convert the planning into time written. There is more on the list, for now, as is, this sheet saves me time and provides me with a clear overview of time spend.
