Automated work progress tracking

Tracking changes in Google Spreadsheets with Google Apps Script

 

Introduction

Let’s say you’re keeping track of your work progress as a team in Google Spreadsheets. Every individual checks a box or adds a row if work is completed. It would be handy if you could display 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.

Question

How can we automatically update the tracking process of work being completed in Google Spreadsheets?

Method

  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:

 

function setupTrigger() {
  Logger.log("@")
  ScriptApp.newTrigger('myFunction')
    .timeBased()
    .everyHours(6)
    .create();
}

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:

 

function myFunction() {
  // Define the active spreadsheet as variable "ss"
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Define the sheet from where you want to track changes as "sheet"
  let sheet = ss.getSheetByName("Table");
  
  // Define the sheet in which you want to display the changes as "sheet2"
  let sheet2 = ss.getSheetByName("Overview graphs Dutch");
  
  // Define the sheet values you want to track by specifying the row,column, array-width and array-length
  let x1 = sheet.getSheetValues(2, 7, 1, 1);
  let x2 = sheet.getSheetValues(2, 6, 1, 1);
  let x3 = sheet.getSheetValues(3, 6, 1, 1);

  // Create a date and timestamp variable
  var date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy");
  var time = new Date();
  var timestamp = time.toLocaleTimeString();

  // Create a print statment to see if you retrieving the correct values
  Logger.log(x1, x2, x3,date, time, timestamp)
  
  // Appends a new row with 5 columns to the bottom of the
  // spreadsheet containing the values in the array
  sheet2.appendRow([date, x1[0][0], x2[0][0], x3[0][0], timestamp]);
}

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

The output converted to a CSV file: