HOW TO: Google Sheets and Apps Script for time management

Danko — Sat, 29 May 2021 02:57:46 +0000

Apps Script or AppScript (as I like to call it from now on) by Wikipedia's 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!

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:

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:

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.

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:

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:

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!

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

HOW TO: Multiple .xlsx files to one .csv using R/RStudio

Danko — Sat, 29 May 2021 02:50:44 +0000

This how-to will help you to take data from multiple MS Excel files into one big CSV file using packages and a simple program in R.

First you must have installed R (RStudio is optional).
Once installed, run R and open a new console.
In the console, write the following to install and load the readxl package:

> install.package("readxl") # install the package
> library(readxl) # load the package

Make sure to set the working directory to the folder where your Excel files are allocated. Use setwd() function in the console to do this:

setwd("C:/your_path_goes_here/xlsx_folder")

To check if the previous step is correct, run getwd() in the console.
Now run the following subroutine:

# list of the names of the excel files in the working directoy
lst = list.files()
# create new data frame
df = data.frame()
# iterate over the names in the lists
for(table in lst){
  dataFromExcel <- read_excel(table)
  df <- rbind(df,dataFromExcel)
} 
write.csv(df, "data.csv")

This program will fetch the .xlsx files by their names, read their data and write it over dataFromExcel variable (you can read the read_excel() documentation to give specific information on how to read the file). Then the rbind() function will merge the rows from both data frames. This will loop over all the Excel files in the folder (Note: try to have ONLY Excel files in your folder).
Finally, the write.csv() function will create a .csv file from the data we just uploaded to our main data frame df with the name “data.csv” in the current working directory.

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

DEV Community: Danko

HOW TO: Google Sheets and Apps Script for time management

HOW TO: Multiple .xlsx files to one .csv using R/RStudio