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
- 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:
