Automated work progress tracking

Tracking changes in Google Spreadsheets with Google Apps Script

Let’s say you’re keeping track of your team progress in Google Spreadsheets. Every individual checks a box or adds a row if work is completed. It would be handy if you could show how much of the total work is completed everyday/hour, how many new orders there are in your sheets or what the financial balance of the day. You can achieve these things with Google Apps Script, a Javascript-based programming language in Google office.

Steps

  1. Open the script editor by clicking on extra -> stript editor.

2. Create a file by pressing the + sign.

3. Give your script a name (e.g. trigger.gs).

4. In the script editor write a function that executes a time-based trigger:

You can change line 5 “everyHours(6)” to every minute or something similar:

  • everyDays(n): Specifies to run the trigger every n days.
  • everyHours(n): Specifies to run the trigger every n hours.
  • everyMinutes(n): Specifies to run the trigger every n minutes.
  • everyWeeks(n): Specifies to run the trigger every n weeks.

5. Create a second file and name it something like trackchanges.gs.

6. In the script editor write a function that contains the following:

7. Run the trigger.gs script by clicking on “run”.

8. Go to triggers, you can get there by clicking the clock on the left menubar.

9. You can see the trigger with the date and time started. (e.g. “Sep 24, 2021, 1:16:39 PM”).

Result

Example output with variable time triggers in Google Sheets converted to a CSV file:

--

--

MD, PhD with a heart for software development, www.kelvinkramp.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store