Bom dia, Boa Tarde, Boa Noite queridos, 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.

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

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: [email protected]

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=

💡 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.response10].a, response.response 0].b, response.responseo0].c, response.responseo0].d, response.responseo0].e, response.responseo0].f, response.responseo0].g, response.responseo0].h, response.responseo0].i, response.responseo0].j, response.responseo0].k, response.responseo0].l, response.responseo0].m, response.responseo0].n, response.responseo0].o, response.responseo0].p, response.responseo0].q, response.responseo0].r, response.responseo0].s, response.responseo0].t, response.responseo0].u, response.responseo0].v, response.responseo0].w, response.responseo0].x, response.responseo0].y, response.responseo0].z, response.responseo0].aa, response.responsen0].ab, response.responses0].ac, response.responses0].ad, response.responses0].ae],



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



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



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



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



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



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



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



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



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



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



    0'December' , response.response]11].a, response.response>11].b, response.responses11].c, response.responses11].d, response.responses11].e, response.responses11].f, response.responses11].g, response.responses11].h, response.responses11].i, response.responses11].j, response.responses11].k, response.responses11].l, response.responses11].m, response.responses11].n, response.responses11].o, response.responses11].p, response.responses11].q, response.responses11].r, response.responses11].s, response.responses11].t, response.responses11].u, response.responses11].v, response.responses11].w, response.responses11].x, response.responses11].y, response.responses11].z, response.responses11].aa, response.responsee11].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



    }

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

rObservação da moderação da comunidade (10/10/24): Arquivo com o JSON também está disponível em anexo neste tópico]

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