💡 Como configurar os feriados via Planilha do Google

Bom dia, Boa Tarde, Boa Noite queridos Blippers, bão demais?

Hoje é meu aniversário hahaha mas eu preparei um presente para vocês que são parte tão especial da minha vida. :heart_eyes:

Fim de ano é uma época complicada né? Cada empresa acaba lidando com os feriados de uma maneira diferente e às vezes quem vai definir isso não é a mesma pessoa responsável pela plataforma, certo? Mas e se a gente fizesse igual o esquema da horas por planilha? Um feriado por planilha? Então cola comigo e vem fazer mágica!

Primeiro você vai precisar fazer uma cópia desta planilha aqui: Feriados Teste - Google Sheets

:bulb: Ela é bem intuitiva e fácil de preencher, lembrando que 0 é um dia comum e 1 um dia que você vai tratar como feriado.

Agora vamos fazer como no tutorial de horários, e dar acesso ao email que vai fazer a integração para nós como editor, o email é esse aqui: googlsheets@sheetsintegrations.iam.gserviceaccount.com

Feito isso, vamos fazer uma requisição do tipo GET no seguinte URL: https://googlesheets.cs.blip.ai/sheets/cod_da_sua_planilha/0?field=meses&value=

:bulb: Lembrando que o código da planilha é que fica entre as barras no meio da URL dela:

No meu caso 1thx1kIxSz8MYLesTaByRjdAvzhxgKEbKxBv9jdSnRMs, logo o URL ficaria https://googlesheets.cs.blip.ai/sheets/1thx1kIxSz8MYLesTaByRjdAvzhxgKEbKxBv9jdSnRMs/0?field=meses&value=

Feito isso, vamos salvar o status e o retorno da requisição:

Feito isso vamos usar um script para testar se hoje é feriado, que vai será:

    function run(json){

    response = JSON.parse(json)

    /* system date */

    let data = new Date()

    let isHoliday = new Boolean()

    /* object with local date and time */

    var currentDate = {

    year : data.getFullYear(), /* The getFullYear() method returns the year (four digits for dates between year 1000 and 9999) of the specified date.*/

    month : data.getMonth(), /* The getMonth() method returns the month (from 0 to 11) for the specified date, according to local time. */

    day : data.getDate(), /* The getDate() method returns the day of the month (from 1 to 31) for the specified date. */

    day_week : data.getDay(), /* The getDay() method returns the day of the week (from 0 to 6) for the specified date. */

    hour : data.getHours(), /* The getHours() method returns the hour (from 0 to 23) of the specified date and time. */

    minute : data.getMinutes() /* The getMinutes() method returns the minutes (from 0 to 59) of the specified date and time. */

    };

    /* configuration matrix with holidays */

    /* info -> 0 = is not holiday | 1 = is a holiday */

    let Holidays = [

    ['Months' , 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31],

    ['January' , response.response[0].a, response.response[0].b, response.response[0].c, response.response[0].d, response.response[0].e, response.response[0].f, response.response[0].g, response.response[0].h, response.response[0].i, response.response[0].j, response.response[0].k, response.response[0].l, response.response[0].m, response.response[0].n, response.response[0].o, response.response[0].p, response.response[0].q, response.response[0].r, response.response[0].s, response.response[0].t, response.response[0].u, response.response[0].v, response.response[0].w, response.response[0].x, response.response[0].y, response.response[0].z, response.response[0].aa, response.response[0].ab, response.response[0].ac, response.response[0].ad, response.response[0].ae],

    ['February' , response.response[1].a, response.response[1].b, response.response[1].c, response.response[1].d, response.response[1].e, response.response[1].f, response.response[1].g, response.response[1].h, response.response[1].i, response.response[1].j, response.response[1].k, response.response[1].l, response.response[1].m, response.response[1].n, response.response[1].o, response.response[1].p, response.response[1].q, response.response[1].r, response.response[1].s, response.response[1].t, response.response[1].u, response.response[1].v, response.response[1].w, response.response[1].x, response.response[1].y, response.response[1].z, response.response[1].aa, response.response[1].ab, response.response[1].ac, response.response[1].ad, response.response[1].ae],

    ['March' , response.response[2].a, response.response[2].b, response.response[2].c, response.response[2].d, response.response[2].e, response.response[2].f, response.response[2].g, response.response[2].h, response.response[2].i, response.response[2].j, response.response[2].k, response.response[2].l, response.response[2].m, response.response[2].n, response.response[2].o, response.response[2].p, response.response[2].q, response.response[2].r, response.response[2].s, response.response[2].t, response.response[2].u, response.response[2].v, response.response[2].w, response.response[2].x, response.response[2].y, response.response[2].z, response.response[2].aa, response.response[2].ab, response.response[2].ac, response.response[2].ad, response.response[2].ae],

    ['April' , response.response[3].a, response.response[3].b, response.response[3].c, response.response[3].d, response.response[3].e, response.response[3].f, response.response[3].g, response.response[3].h, response.response[3].i, response.response[3].j, response.response[3].k, response.response[3].l, response.response[3].m, response.response[3].n, response.response[3].o, response.response[3].p, response.response[3].q, response.response[3].r, response.response[3].s, response.response[3].t, response.response[3].u, response.response[3].v, response.response[3].w, response.response[3].x, response.response[3].y, response.response[3].z, response.response[3].aa, response.response[3].ab, response.response[3].ac, response.response[3].ad, response.response[3].ae],

    ['May' , response.response[4].a, response.response[4].b, response.response[4].c, response.response[4].d, response.response[4].e, response.response[4].f, response.response[4].g, response.response[4].h, response.response[4].i, response.response[4].j, response.response[4].k, response.response[4].l, response.response[4].m, response.response[4].n, response.response[4].o, response.response[4].p, response.response[4].q, response.response[4].r, response.response[4].s, response.response[4].t, response.response[4].u, response.response[4].v, response.response[4].w, response.response[4].x, response.response[4].y, response.response[4].z, response.response[4].aa, response.response[4].ab, response.response[4].ac, response.response[4].ad, response.response[4].ae],

    ['June' , response.response[5].a, response.response[5].b, response.response[5].c, response.response[5].d, response.response[5].e, response.response[5].f, response.response[5].g, response.response[5].h, response.response[5].i, response.response[5].j, response.response[5].k, response.response[5].l, response.response[5].m, response.response[5].n, response.response[5].o, response.response[5].p, response.response[5].q, response.response[5].r, response.response[5].s, response.response[5].t, response.response[5].u, response.response[5].v, response.response[5].w, response.response[5].x, response.response[5].y, response.response[5].z, response.response[5].aa, response.response[5].ab, response.response[5].ac, response.response[5].ad, response.response[5].ae],

    ['July' , response.response[6].a, response.response[6].b, response.response[6].c, response.response[6].d, response.response[6].e, response.response[6].f, response.response[6].g, response.response[6].h, response.response[6].i, response.response[6].j, response.response[6].k, response.response[6].l, response.response[6].m, response.response[6].n, response.response[6].o, response.response[6].p, response.response[6].q, response.response[6].r, response.response[6].s, response.response[6].t, response.response[6].u, response.response[6].v, response.response[6].w, response.response[6].x, response.response[6].y, response.response[6].z, response.response[6].aa, response.response[6].ab, response.response[6].ac, response.response[6].ad, response.response[6].ae],

    ['August' , response.response[7].a, response.response[7].b, response.response[7].c, response.response[7].d, response.response[7].e, response.response[7].f, response.response[7].g, response.response[7].h, response.response[7].i, response.response[7].j, response.response[7].k, response.response[7].l, response.response[7].m, response.response[7].n, response.response[7].o, response.response[7].p, response.response[7].q, response.response[7].r, response.response[7].s, response.response[7].t, response.response[7].u, response.response[7].v, response.response[7].w, response.response[7].x, response.response[7].y, response.response[7].z, response.response[7].aa, response.response[7].ab, response.response[7].ac, response.response[7].ad, response.response[7].ae],

    ['September' , response.response[8].a, response.response[8].b, response.response[8].c, response.response[8].d, response.response[8].e, response.response[8].f, response.response[8].g, response.response[8].h, response.response[8].i, response.response[8].j, response.response[8].k, response.response[8].l, response.response[8].m, response.response[8].n, response.response[8].o, response.response[8].p, response.response[8].q, response.response[8].r, response.response[8].s, response.response[8].t, response.response[8].u, response.response[8].v, response.response[8].w, response.response[8].x, response.response[8].y, response.response[8].z, response.response[8].aa, response.response[8].ab, response.response[8].ac, response.response[8].ad, response.response[8].ae],

    ['October' , response.response[9].a, response.response[9].b, response.response[9].c, response.response[9].d, response.response[9].e, response.response[9].f, response.response[9].g, response.response[9].h, response.response[9].i, response.response[9].j, response.response[9].k, response.response[9].l, response.response[9].m, response.response[9].n, response.response[9].o, response.response[9].p, response.response[9].q, response.response[9].r, response.response[9].s, response.response[9].t, response.response[9].u, response.response[9].v, response.response[9].w, response.response[9].x, response.response[9].y, response.response[9].z, response.response[9].aa, response.response[9].ab, response.response[9].ac, response.response[9].ad, response.response[9].ae],

    ['November' , response.response[10].a, response.response[10].b, response.response[10].c, response.response[10].d, response.response[10].e, response.response[10].f, response.response[10].g, response.response[10].h, response.response[10].i, response.response[10].j, response.response[10].k, response.response[10].l, response.response[10].m, response.response[10].n, response.response[10].o, response.response[10].p, response.response[10].q, response.response[10].r, response.response[10].s, response.response[10].t, response.response[10].u, response.response[10].v, response.response[10].w, response.response[10].x, response.response[10].y, response.response[10].z, response.response[10].aa, response.response[10].ab, response.response[10].ac, response.response[10].ad, response.response[10].ae],

    ['December' , response.response[11].a, response.response[11].b, response.response[11].c, response.response[11].d, response.response[11].e, response.response[11].f, response.response[11].g, response.response[11].h, response.response[11].i, response.response[11].j, response.response[11].k, response.response[11].l, response.response[11].m, response.response[11].n, response.response[11].o, response.response[11].p, response.response[11].q, response.response[11].r, response.response[11].s, response.response[11].t, response.response[11].u, response.response[11].v, response.response[11].w, response.response[11].x, response.response[11].y, response.response[11].z, response.response[11].aa, response.response[11].ab, response.response[11].ac, response.response[11].ad, response.response[11].ae]

    ];

    /* Adjusting the current month of the year to the Holidays table format */

    currentDate.month++

    /* checks if the current date is a holiday */

    if (Holidays[currentDate.month][currentDate.day] == 0){

    isHoliday = false /* --> is not a Holiday */

    }else{

    isHoliday = true /* --> is a Holiday */

    }

    return isHoliday

    }

:bulb: Não se esqueça de nas variáveis de entrada passar o retorna da requisição (no meu caso responsesheets) e de salvar o retorno do script (que no meu caso vai ser isHoliday):

Em seguida, basta tratar nas ações de saída do bloco:

Aqui tem o JSON para você testar e ser muito feliz, só pedir acesso e brincar à vontade: https://drive.google.com/file/d/1lT8-YqU3k909GZohQX2h6j4QBG77J_B-/view?usp=sharing

Feliz Natal Galerinha, juro que um dia trago o tanto de coisa que to devendo pra vocês, tem alguma mudanças vindo ai, quem sabe agora terei mais tempo pras DOCs, e juro que sempre que eu tiver uns trem desse eu compartilho, interajam ai no comentários caso tenham dúvidas, vamos nos ajudar a crescer cada vez mais BEJÃO AMO VOCÊS E REXPEITA QUE O PAI AGORA É 2.1

18 Curtidas

Que massa @Pedro_Lucas presentão ai pra nós. :blipinlove: :blipinlove: :star_struck: :star_struck:

5 Curtidas

Muito bom !!! Parabens @Pedro_Lucas !!!

3 Curtidas

Valeu por esse presente @Pedro_Lucas !
Ficou show de bola o modelo, desse jeito dá pra configurar feriado de olhos fechados e com as mãos pra trás! #ezpz

E aproveitando, parabéns por mais um ano de vida! :birthday::partying_face: tmj demais.

:blipinlove:

2 Curtidas