HOW TO: Google Sheets and Apps Script for time management
Tags: tutorial, javascript, appscript, googlesheets
Published: 2021-05-29 02:57:46

![Google Apps Script platform](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2kwmt2wohi34xt6ln5hh.png)

*Apps Script* or *AppScript* (as I like to call it from now on) by [Wikipedia's](https://es.wikipedia.org/wiki/Google_Apps_Script) definition is

> *A scripting platform developed by Google for light-weight application development in the Google Workspace platform [...] It is based on __JavaScript__ 1.6, but also includes some portions of 1.7 and 1.8 and a subset of the ECMAScript 5 API. Apps Script projects run server-side on Google's infrastructure.*

In other words, with this platform you can run *JavaScript-like* code inside your other Google applications (like Calendar, Docs, Drive, Gmail, Sheets, and Slides) in order to automate certain tasks just by running a script. It is a tool so powerful [you can even build Web Apps!](https://developers.google.com/apps-script/guides/web)

In this tutorial you will learn how to use it to build a dynamic time-management schedule using Google Sheets:

* Open a new Google Sheets and create your schedule in a way where every row represents a time lapse that fits your needs. Here is an example of mine:

![Schedule Example](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z3wgz8pv550lilxuh8fw.png)

Is important to note that __for every type of activity I have a distinctive color associated to it__ (STUDY = YELLOW, PERSONAL = GREEN, etc). Also, by looking at the image you can tell that every activity in this particular schedule takes 1.5 hours, this is just to make the maths easier after.

* Once the schedule is done, create a table next to your schedule just like this:

![table example](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hinzwe07zm2knsffkn3g.png)

As you can see, I used the exact same colors to map the activites. The goal here will be to create a program capable of __counting the different `MODULES` in the schedule by color so we can automatically calculate how much time is spent in a given activity__, that's why is important to stablish your time lapses beforehand. This table (specifically the column `ACTIVITY`) will serve as a reference map for the colors and activities in the script.

* Now, go to the `Tools` tab in the sheet document and select `Script Editor`. This will automatically open the AppScript platform. Then, go to `File` section, click on the `+` button and select `Apps Script` to create a new script for your sheet. This is when the fun starts.

* Once that is completed, the structure of the script is very simple: first, we create a function that is capable of counting the number of modules in the schedule given a specific color.

```javascript
function countBackgrounds(colorReference) {
 var book = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = book.getActiveSheet();
 var range_input = sheet.getRange("B2:H38"); /* The reference where the Schedule is stored */
 var cell_colors = range_input.getBackgroundColors(); /* Fetches all the colors in that range */
 var color = colorReference;
 var count = 0; /* counter */
 /* cell_colors is a matrix of colors */
 for(var r = 0; r < cell_colors.length; r++) {
   for(var c = 0; c < cell_colors[r].length; c++) {
     if(cell_colors[r][c] == color) { /* If there's a match adds 1 to the counter */
       count = count + 1;
     }
   }
 }
  return count
}
```
* The next step is to fetch the color references we are going to give to `countBackgrounds` as argument. This is can be done with these lines of code:

```javascript
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var results = ["K2", "K3", "K4","K5"]; /* The reference where the total countings will be written */
var colors = sheet.getRange("J2:J5").getBackgrounds(); /* The reference of the colors we are using */

for(var i in colors){
  sheet.getRange(results[i]).setValue(countBackgrounds(colors[i].toString()));
}
```
* Now you are ready to run your script and see what happens but, before your script actually executes, Google will ask your permit for reading and modifying your Google Sheets data. After doing that (and your math for the time conversions) you will have a table looking like this:

![table full](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ukay3ka61zl0txyd6rix.png)

Cool, right? You can add a *Pie Chart* or a *Bar Chart* that references this table and you will have a complete time-management schedule!

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sz11l9ok3zid7rc29hr7.png)

Don't forget to give a ❤️ if you found this useful :)