How to alternate colours when changes day

Pubblicato da Nicola Rainiero il 02-07-2018 (aggiornato il 03-07-2018)

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.



Potrebbero interessarti anche:

Nicola Rainiero

Ingegnere civile specializzato in geotecnica con l'ambizione di facilitare la propria attività lavorativa usando e creando software libero per un sapere condiviso e collettivo. Mi occupo anche di energie rinnovabili ed in particolare di geotermia a bassa entalpia. Sono da sempre appassionato di web design e modellazione 3D.

Aggiungi un commento

Il contenuto di questo campo è privato e non verrà mostrato pubblicamente.

Plain text

  • Nessun tag HTML consentito.
  • Indirizzi web ed indirizzi e-mail diventano automaticamente dei link.
  • Linee e paragrafi vanno a capo automaticamente.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.