How to alternate colours when changes day | Rainnic in the Clouds

How to alternate colours when changes day

Submitted by Nicola Rainiero on 2018-07-02 (last updated on 2018-07-03)

I am continuing the development of my script to auto-transfer Calendar entries into a spreadsheet. In this article I show you a convenient function to change the colour of the row when changes the day of the event, useful when for example you have more than one shifts and you want to improve the readability of the table.

How to alternate colours when changes day

 

In comparison to the my original script that even now I use because it is more complete, I am looking for simplify the output, in order to avoid a lot of columns that do some calculations. In this case I use a formula to convert the date in an integer:

=(DATE(YEAR(i3);MONTH(i3);DAY(i3))-DATE(YEAR(i3);1;0))

Integer that I use to make a comparison whith the previous one, so I change the colour if it is not equal or mantain the same if equal. This is the code:

// Variables used to alternate colours
var columnColorCalc = 28;
var color = firstColor;
var FirstWorkingDay = sheet.getRange(firstRowDate,columnColorCalc).setFormula('=(DATE(YEAR(A' +firstRowDate+ ');MONTH(A' +firstRowDate+ ');DAY(A' +firstRowDate+ '))-DATE(YEAR(A' +firstRowDate+ ');1;0))').getValue();

// Code to improve the stilish of the table
for (var i=firstRowDate; i <= totalRows; i+=1){
    sheet.getRange(i,1).setNumberFormat("-DD/MM-").setHorizontalAlignment("center");
    sheet.getRange(i,3,totalRows,2).setNumberFormat("HH:mm");

    // Code to alternate colours
    var workingDay = sheet.getRange(i,columnColorCalc).setFormula('=(DATE(YEAR(A' +i+ ');MONTH(A' +i+ ');DAY(A' +i+ '))-DATE(YEAR(A' +i+ ');1;0))').getValue();
    if( FirstWorkingDay == workingDay ){
        sheet.getRange(i, 1, 1, totalColumns).setBackground(color);
    } else if (color == firstColor) { var FirstWorkingDay = sheet.getRange(i,columnColorCalc).setFormula('=(DATE(YEAR(A' +i+ ');MONTH(A' +i+ ');DAY(A' +i+ '))-DATE(YEAR(A' +i+ ');1;0))').getValue(); var color = secondColor; sheet.getRange(i, 1, 1, totalColumns).setBackground(color);
    } else if (color == secondColor) { var FirstWorkingDay = sheet.getRange(i,columnColorCalc).setFormula('=(DATE(YEAR(A' +i+ ');MONTH(A' +i+ ');DAY(A' +i+ '))-DATE(YEAR(A' +i+ ');1;0))').getValue(); var color = firstColor; sheet.getRange(i, 1, 1, totalColumns).setBackground(color);
    }
     // Code to alternate colours

}

Once obtained the formatting, I have instructed the script to delete the column with another for loop:

// Clear columns added for calculations
for (var i=firstRowDate; i <= totalRows; i+=1){
    // The column used to change the colors
    sheet.getRange(i,columnColorCalc).clear();
}

I realise that is a really forcing solution, but I haven't yet found how to save a variable without using the two functions: getRange and setFormula. If someone more expert than me can help, I will sure appreciate!

This is the final result:

Final result of application of colours when changes day
In the top the events taken from Calendar, at the bottom the result in the spreadsheet

Download

You can find and test my Script on GitHub at the following link:

If you want to install it, you can follow my previous article 5 steps to auto-transfer your Calendar entries into a spreadsheet or see the video wiht the entire process step by step:

P.S. This is the first video with my voice, so I want to apologize for my bad grammar, pronunciation and some embarrassment.



Related Content:

Nicola Rainiero

A civil geotechnical engineer with the ambition to facilitate own work with free software for a knowledge and collective sharing. Also, I deal with green energy and in particular shallow geothermal energy. I have always been involved in web design and 3D modelling.

Add new comment

The content of this field is kept private and will not be shown publicly.

Plain text

  • No HTML tags allowed.
  • Web page addresses and email addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.