Come estrarre le ore notturne e festive dal tuo Calendario con Apps Script

Pubblicato da Nicola Rainiero il 05-08-2019

È ora di aggiornare il mio script di Google Apps che si occupa di esportare gli eventi del calendario in un foglio di calcolo. Lo uso per confrontare le mie ore di lavoro con la busta paga mensile, quindi inserendo tutti i miei turni su Google Calendar ho automaticamente un foglio con la trascrizione di tutti i turni inseriti. Ho aggiunto come estrarre le ore notturne e festive e contare i giorni lavorativi in una serie di dati.

Questo è il terzo articolo sull'argomento e se vuoi saperne di più ti consiglio di leggere i post precedenti:

Nell'articolo metto in evidenza tre nuove funzionalità utili a:

  1. contare le ore notturne nell'evento del calendario, una volta definito l'intervallo delle ore (impostazione predefinita 22-6);
  2. contare le ore festive nell'evento del Calendario una volta definito l'elenco delle festività nazionali;
  3. contare i giorni lavorativi nell'intervallo definito di date.
Come estrarre le ore notturne e festive dal tuo Calendario con Apps Script

Impostazioni aggiunte

Per contare le ore notturne e festive è necessario definire quando il turno è notturno (in Italia dalle 22:00 alle 6:00) e quali giorni sono considerati festivi. Inoltre volevo dare la possibilità di mostrare o meno le colonne relative a questi conteggi riga per riga.

var night_timing = [22, 6]; // intervallo di orario notturno --> night_timing[0]
var night = 1; // per aggiungere la colonna delle ore notturne sul foglio si(1)/no(0)
var feast = 1; // per aggiungere la colonna delle ore festive sul foglio si(1)/no(0)
var feast_days = "\
DATE(YEAR(A1); 1; 1);\
DATE(YEAR(A1); 1; 6);\
DATE(YEAR(A1); 4; 21);\
DATE(YEAR(A1); 4; 22);\
DATE(YEAR(A1); 4; 25);\
DATE(YEAR(A1); 5; 1);\
DATE(YEAR(A1); 6; 2);\
DATE(YEAR(A1); 6; 13);\
DATE(YEAR(A1); 8; 15);\
DATE(YEAR(A1); 11; 1);\
DATE(YEAR(A1); 12; 8);\
DATE(YEAR(A1); 12; 25);\
DATE(YEAR(A1); 12; 26)\
"; // festività nazionali e locali scritte in ANNO/MESE/GIORNO

Quindi il codice legge l'ora di inizio e di fine, effettua i calcoli usando le formule dei fogli classici e stampa i risultati riga per riga.

Un esempio di un foglio generato dallo script
Un esempio di un foglio generato dallo script

Ore notturne

La formula è questa \( &=MOD(Di-Ci;1)*24-(Di<Ci)*(22-6)+MEDIAN(6;22;Di*24)-MEDIAN(Di*24;6;22))/24 \) e ho creato una variabile per memorizzarne l'incremento. Il codice è un po' troppo prolisso:

    // Codice per l'orario notturno
    sheet.getRange(i,totalColumns+1).setFormula('=HOUR(VALUE((MOD((TIME(HOUR(D' +i+ ');MINUTE(D' +i+ ');0))-(TIME(HOUR(C' +i+ ');MINUTE(C' +i+ ');0));1)*24'
                                                +'-((TIME(HOUR(D' +i+ ');MINUTE(D' +i+ ');0))<(TIME(HOUR(C' +i+ ');MINUTE(C' +i+ ');0)))*('+night_timing[0]+'-'+night_timing[1]+')'
                                                +'+MEDIAN('+night_timing[1]+';'+night_timing[0]+';(TIME(HOUR(C' +i+ ');'
                                                +'MINUTE(C' +i+ ');0))*24)-MEDIAN((TIME(HOUR(D' +i+ ');MINUTE(D' +i+ ');0))*24;'+night_timing[1]+';'+night_timing[0]+'))/24))+MINUTE(VALUE((MOD((TIME(HOUR(D' +i+ ');MINUTE(D' +i+ ');0))'
                                                +'-(TIME(HOUR(D' +i+ ');MINUTE(C' +i+ ');0));1)*24-((TIME(HOUR(D' +i+ ');MINUTE(D' +i+ ');0))<(TIME(HOUR(C' +i+ ');MINUTE(C' +i+ ');0)))*('+night_timing[0]+'-'+night_timing[1]+')'
                                                +'+MEDIAN('+night_timing[1]+';'+night_timing[0]+';(TIME(HOUR(C' +i+ ');'
                                                +'MINUTE(C' +i+ ');0))*24)-MEDIAN((TIME(HOUR(D' +i+ ');MINUTE(D' +i+ ');0))*24;'+night_timing[1]+';'+night_timing[0]+'))/24))/60').setNumberFormat('0.00').setHorizontalAlignment("center");
    total_night_hours = total_night_hours + sheet.getRange(i,totalColumns+1).getValue();
    // sheet.getRange(i,totalColumns+2).setValue(total_night_hours);
    // Codice per l'orario notturno

Ore festive

La formula è questa \( &=IF(NETWORKDAYS.INTL(Ai; Ai; "0000000";{'+ feast_days +'})=0;Ei;0) \) e ho creato una variabile per memorizzarne l'incremento. Il codice questa volta è più leggibile:

    // Codice per l'orario festivo
    sheet.getRange(i,totalColumns+2).setFormula('=IF(NETWORKDAYS.INTL(A' +i+ '; A' +i+ '; "0000000";{'+ feast_days +'})=0;E' +i+ ';0)').setHorizontalAlignment("center");
    total_feast_hours = total_feast_hours + sheet.getRange(i,totalColumns+2).getValue();
    // sheet.getRange(i,totalColumns+3).setValue(total_night_hours);
    // Codice per l'orario festivo

Giorni lavorati

La formula è questa \( &=COUNTUNIQUE(ABprima:ABultima) \) e ho salvato il valore da una cella che in seguito ho cancellato. Nota che ho riutilizzato la colonna che elabora il colore riga per riga. I

// Calcolo giorni lavorati
  sheet.getRange(totalRows+1,columnColorCalc).setFormula('=COUNTUNIQUE(AB3:AB' +totalRows+ ')').setNumberFormat('0');
  total_worked_days = sheet.getRange(totalRows+1,columnColorCalc).getValue();
  sheet.getRange(totalRows+1,columnColorCalc).clear(); // Cancella la cella dopo averne salvato il contenuto

Somme finali

Il codice è semplice e l'immagine seguente mostra come appariranno le somme finali:

sheet.getRange(totalRows+3,4).setValue('Σnotturne('+night_timing[0]+'-'+night_timing[1]+')=').setNumberFormat('0').setHorizontalAlignment("right");
sheet.getRange(totalRows+3,5).setValue(total_night_hours).setNumberFormat('0.00 \\o\\r\\e').setHorizontalAlignment("right"); // somma ore notturne

sheet.getRange(totalRows+4,4).setValue('Σfestive=').setNumberFormat('0').setHorizontalAlignment("right");
sheet.getRange(totalRows+4,5).setValue(total_feast_hours).setNumberFormat('0.00 \\o\\r\\e').setHorizontalAlignment("right"); // somma ore festive
  
sheet.getRange(totalRows+6,4).setValue('Σlavorati=').setNumberFormat('0').setHorizontalAlignment("right");
  if (total_worked_days == 1) {
    sheet.getRange(totalRows+6,5).setValue(total_worked_days).setNumberFormat('0 \\g\\i\\o\\r\\n\\o').setHorizontalAlignment("right"); // se 1 mostra il giorno lavorato
} else {
    sheet.getRange(totalRows+6,5).setValue(total_worked_days).setNumberFormat('0 \\g\\i\\o\\r\\n\\i').setHorizontalAlignment("right"); // somma giorni lavorati totali
Somme finali
Somme finali

Download

Puoi trovare e provare il mio script su GitHub, Code_en.gs è la versione inglese mentre Code_it.gs è quella italiana. Qui il link:



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.