Nível de programação: iniciante
Duração: 15 minutos
Tipo de projeto: automação com um menu personalizado
Objetivos
- Entenda o que a solução faz.
- Entenda o que os serviços do Apps Script fazem na solução.
- Configurar o ambiente.
- Configure o script.
- Execute o script.
Sobre esta solução
Controle o tempo gasto em projetos para os clientes. Você pode registrar seu
tempo relacionado ao projeto no Google Agenda e sincronizá-lo com o Planilhas Google para
criar uma planilha de horários ou importar sua atividade para outro sistema de gerenciamento
de planilhas. Você pode categorizar seu tempo por cliente, projeto e tarefa.
Como funciona
O script fornece uma barra lateral que permite selecionar as agendas que serão sincronizadas, o período de sincronização e se você quer substituir os títulos e as descrições dos eventos pelas informações inseridas na planilha. Depois que essas configurações
forem definidas, será possível sincronizar eventos e ver suas atividades em um painel.
O script traz eventos das agendas e do período especificado do Google Agenda para a planilha. É possível adicionar clientes, projetos e tarefas à página categories e marcar os eventos na planilha hours.
Dessa forma, ao visualizar a página do painel, você pode ver o tempo total por cliente, projeto e tarefa.
Serviços do Apps Script
Essa solução usa os seguintes serviços:
- Serviço HTML: cria a barra lateral usada para
definir configurações de sincronização.
- Serviço de propriedades: armazena as configurações
que o usuário seleciona na barra lateral.
- Serviço de agenda: envia as informações do evento para a planilha.
- Serviço de planilha: grava os eventos na planilha e, se configurado, envia informações atualizadas sobre o título e a descrição para o Agenda.
Pré-requisitos
Para usar esta amostra, você precisa dos seguintes pré-requisitos:
- Uma Conta do Google (as contas do Google Workspace podem
exigir a aprovação do administrador).
- Um navegador da Web com acesso à Internet.
configure seu ambiente
Se você planeja usar uma agenda existente, pule esta etapa.
- Acesse calendar.google.com.
- Ao lado de Outras agendas, clique em Adicionar outras agendas add
> Criar nova agenda.
- Dê um nome à agenda e clique em Criar agenda.
- Adicione alguns eventos à agenda.
Configurar o script
Clique no botão a seguir para fazer uma cópia da planilha de exemplo Record time and Activities. O projeto do Apps Script para esta solução está anexado à planilha.
Fazer uma cópia
Executar o script
Sincronizar eventos da agenda
- Clique em myTime > Configurações. Talvez
seja necessário atualizar a página para que esse menu personalizado apareça.
Quando solicitado, autorize o script.
Se a tela de permissão OAuth exibir o aviso Este app não foi verificado,
continue selecionando Avançado >
Ir para {Nome do projeto} (não seguro).
Clique em myTime > Configurações novamente.
Na lista de agendas disponíveis, selecione a que você criou e
outras que quiser sincronizar.
Defina o restante das configurações e clique em Salvar.
Clique em myTime > Sincronizar eventos da
agenda.
Configurar o painel
- Acesse a página Categorias.
- Adicione clientes, projetos e tarefas.
- Acesse a página Horário de funcionamento.
- Para cada evento sincronizado, selecione o cliente, o projeto e a tarefa.
- Acesse a página Painel.
- A primeira seção fornece os totais diários. Para atualizar a lista de datas para os totais diários, altere a data na célula
A1
.
- A próxima seção apresenta os totais semanais e corresponde à data
selecionada em
A1
.
- As três últimas seções fornecem os totais gerais por tarefa, projeto e cliente.
Revisar o código
Para revisar o código do Apps Script para esta solução, clique em
Ver código-fonte abaixo:
Acessar código-fonte
Code.gs
// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/calendar-timesheet
/*
Copyright 2022 Jasper Duizendstra
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
https://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
/**
* Runs when the spreadsheet is opened and adds the menu options
* to the spreadsheet menu
*/
const onOpen = () => {
SpreadsheetApp.getUi()
.createMenu('myTime')
.addItem('Sync calendar events', 'run')
.addItem('Settings', 'settings')
.addToUi();
};
/**
* Opens the sidebar
*/
const settings = () => {
const html = HtmlService.createHtmlOutputFromFile('Page')
.setTitle('Settings');
SpreadsheetApp.getUi().showSidebar(html);
};
/**
* returns the settings from the script properties
*/
const getSettings = () => {
const settings = {};
// get the current settings
const savedCalendarSettings = JSON.parse(PropertiesService.getScriptProperties().getProperty('calendar') || '[]');
// get the primary calendar
const primaryCalendar = CalendarApp.getAllCalendars()
.filter((cal) => cal.isMyPrimaryCalendar())
.map((cal) => ({
name: 'Primary calendar',
id: cal.getId()
}));
// get the secondary calendars
const secundaryCalendars = CalendarApp.getAllCalendars()
.filter((cal) => cal.isOwnedByMe() && !cal.isMyPrimaryCalendar())
.map((cal) => ({
name: cal.getName(),
id: cal.getId()
}));
// the current available calendars
const availableCalendars = primaryCalendar.concat(secundaryCalendars);
// find any calendars that were removed
const unavailebleCalendars = [];
savedCalendarSettings.forEach((savedCalendarSetting) => {
if (!availableCalendars.find((availableCalendar) => availableCalendar.id === savedCalendarSetting.id)) {
unavailebleCalendars.push(savedCalendarSetting);
}
});
// map the current settings to the available calendars
const calendarSettings = availableCalendars.map((availableCalendar) => {
if (savedCalendarSettings.find((savedCalendar) => savedCalendar.id === availableCalendar.id)) {
availableCalendar.sync = true;
}
return availableCalendar;
});
// add the calendar settings to the settings
settings.calendarSettings = calendarSettings;
const savedFrom = PropertiesService.getScriptProperties().getProperty('syncFrom');
settings.syncFrom = savedFrom;
const savedTo = PropertiesService.getScriptProperties().getProperty('syncTo');
settings.syncTo = savedTo;
const savedIsUpdateTitle = PropertiesService.getScriptProperties().getProperty('isUpdateTitle') === 'true';
settings.isUpdateCalendarItemTitle = savedIsUpdateTitle;
const savedIsUseCategoriesAsCalendarItemTitle = PropertiesService.getScriptProperties().getProperty('isUseCategoriesAsCalendarItemTitle') === 'true';
settings.isUseCategoriesAsCalendarItemTitle = savedIsUseCategoriesAsCalendarItemTitle;
const savedIsUpdateDescription = PropertiesService.getScriptProperties().getProperty('isUpdateDescription') === 'true';
settings.isUpdateCalendarItemDescription = savedIsUpdateDescription;
return settings;
};
/**
* Saves the settings from the sidebar
*/
const saveSettings = (settings) => {
PropertiesService.getScriptProperties().setProperty('calendar', JSON.stringify(settings.calendarSettings));
PropertiesService.getScriptProperties().setProperty('syncFrom', settings.syncFrom);
PropertiesService.getScriptProperties().setProperty('syncTo', settings.syncTo);
PropertiesService.getScriptProperties().setProperty('isUpdateTitle', settings.isUpdateCalendarItemTitle);
PropertiesService.getScriptProperties().setProperty('isUseCategoriesAsCalendarItemTitle', settings.isUseCategoriesAsCalendarItemTitle);
PropertiesService.getScriptProperties().setProperty('isUpdateDescription', settings.isUpdateCalendarItemDescription);
return 'Settings saved';
};
/**
* Builds the myTime object and runs the synchronisation
*/
const run = () => {
'use strict';
myTime({
mainSpreadsheetId: SpreadsheetApp.getActiveSpreadsheet().getId(),
}).run();
};
/**
* The main function used for the synchronisation
* @param {Object} par The main parameter object.
* @return {Object} The myTime Object.
*/
const myTime = (par) => {
'use strict';
/**
* Format the sheet
*/
const formatSheet = () => {
// sort decending on start date
hourSheet.sort(3, false);
// hide the technical columns
hourSheet.hideColumns(1, 2);
// remove any extra rows
if (hourSheet.getLastRow() > 1 && hourSheet.getLastRow() < hourSheet.getMaxRows()) {
hourSheet.deleteRows(hourSheet.getLastRow() + 1, hourSheet.getMaxRows() - hourSheet.getLastRow());
}
// set the validation for the customers
let rule = SpreadsheetApp.newDataValidation()
.requireValueInRange(categoriesSheet.getRange('A2:A'), true)
.setAllowInvalid(true)
.build();
hourSheet.getRange('I2:I').setDataValidation(rule);
// set the validation for the projects
rule = SpreadsheetApp.newDataValidation()
.requireValueInRange(categoriesSheet.getRange('B2:B'), true)
.setAllowInvalid(true)
.build();
hourSheet.getRange('J2:J').setDataValidation(rule);
// set the validation for the tsaks
rule = SpreadsheetApp.newDataValidation()
.requireValueInRange(categoriesSheet.getRange('C2:C'), true)
.setAllowInvalid(true)
.build();
hourSheet.getRange('K2:K').setDataValidation(rule);
if(isUseCategoriesAsCalendarItemTitle) {
hourSheet.getRange('L2:L').setFormulaR1C1('IF(OR(R[0]C[-3]="tbd";R[0]C[-2]="tbd";R[0]C[-1]="tbd");""; CONCATENATE(R[0]C[-3];"|";R[0]C[-2];"|";R[0]C[-1];"|"))');
}
// set the hours, month, week and number collumns
hourSheet.getRange('P2:P').setFormulaR1C1('=IF(R[0]C[-12]="";"";R[0]C[-12]-R[0]C[-13])');
hourSheet.getRange('Q2:Q').setFormulaR1C1('=IF(R[0]C[-13]="";"";month(R[0]C[-13]))');
hourSheet.getRange('R2:R').setFormulaR1C1('=IF(R[0]C[-14]="";"";WEEKNUM(R[0]C[-14];2))');
hourSheet.getRange('S2:S').setFormulaR1C1('=R[0]C[-3]');
};
/**
* Activate the synchronisation
*/
function run() {
console.log('Started processing hours.');
const processCalendar = (setting) => {
SpreadsheetApp.flush();
// current calendar info
const calendarName = setting.name;
const calendarId = setting.id;
console.log(`processing ${calendarName} with the id ${calendarId} from ${syncStartDate} to ${syncEndDate}`);
// get the calendar
const calendar = CalendarApp.getCalendarById(calendarId);
// get the calendar events and create lookups
const events = calendar.getEvents(syncStartDate, syncEndDate);
const eventsLookup = events.reduce((jsn, event) => {
jsn[event.getId()] = event;
return jsn;
}, {});
// get the sheet events and create lookups
const existingEvents = hourSheet.getDataRange().getValues().slice(1);
const existingEventsLookUp = existingEvents.reduce((jsn, row, index) => {
if (row[0] !== calendarId) {
return jsn;
}
jsn[row[1]] = {
event: row,
row: index + 2
};
return jsn;
}, {});
// handle a calendar event
const handleEvent = (event) => {
const eventId = event.getId();
// new event
if (!existingEventsLookUp[eventId]) {
hourSheet.appendRow([
calendarId,
eventId,
event.getStartTime(),
event.getEndTime(),
calendarName,
event.getCreators().join(','),
event.getTitle(),
event.getDescription(),
event.getTag('Client') || 'tbd',
event.getTag('Project') || 'tbd',
event.getTag('Task') || 'tbd',
(isUpdateCalendarItemTitle) ? '' : event.getTitle(),
(isUpdateCalendarItemDescription) ? '' : event.getDescription(),
event.getGuestList().map((guest) => guest.getEmail()).join(','),
event.getLocation(),
undefined,
undefined,
undefined,
undefined
]);
return true;
}
// existing event
const exisitingEvent = existingEventsLookUp[eventId].event;
const exisitingEventRow = existingEventsLookUp[eventId].row;
if (event.getStartTime() - exisitingEvent[startTimeColumn - 1] !== 0) {
hourSheet.getRange(exisitingEventRow, startTimeColumn).setValue(event.getStartTime());
}
if (event.getEndTime() - exisitingEvent[endTimeColumn - 1] !== 0) {
hourSheet.getRange(exisitingEventRow, endTimeColumn).setValue(event.getEndTime());
}
if (event.getCreators().join(',') !== exisitingEvent[creatorsColumn - 1]) {
hourSheet.getRange(exisitingEventRow, creatorsColumn).setValue(event.getCreators()[0]);
}
if (event.getGuestList().map((guest) => guest.getEmail()).join(',') !== exisitingEvent[guestListColumn - 1]) {
hourSheet.getRange(exisitingEventRow, guestListColumn).setValue(event.getGuestList().map((guest) => guest.getEmail()).join(','));
}
if (event.getLocation() !== exisitingEvent[locationColumn - 1]) {
hourSheet.getRange(exisitingEventRow, locationColumn).setValue(event.getLocation());
}
if(event.getTitle() !== exisitingEvent[titleColumn - 1]) {
if(!isUpdateCalendarItemTitle) {
hourSheet.getRange(exisitingEventRow, titleColumn).setValue(event.getTitle());
}
if(isUpdateCalendarItemTitle) {
event.setTitle(exisitingEvent[titleColumn - 1]);
}
}
if(event.getDescription() !== exisitingEvent[descriptionColumn - 1]) {
if(!isUpdateCalendarItemDescription) {
hourSheet.getRange(exisitingEventRow, descriptionColumn).setValue(event.getDescription());
}
if(isUpdateCalendarItemDescription) {
event.setDescription(exisitingEvent[descriptionColumn - 1]);
}
}
return true;
};
// process each event for the calendar
events.every(handleEvent);
// remove any events in the sheet that are not in de calendar
existingEvents.every((event, index) => {
if (event[0] !== calendarId) {
return true;
};
if (eventsLookup[event[1]]) {
return true;
}
if (event[3] < syncStartDate) {
return true;
}
hourSheet.getRange(index + 2, 1, 1, 20).clear();
return true;
});
return true;
};
// process the calendars
settings.calendarSettings.filter((calenderSetting) => calenderSetting.sync === true).every(processCalendar);
formatSheet();
SpreadsheetApp.setActiveSheet(hourSheet);
console.log('Finished processing hours.');
}
const mainSpreadSheetId = par.mainSpreadsheetId;
const mainSpreadsheet = SpreadsheetApp.openById(mainSpreadSheetId);
const hourSheet = mainSpreadsheet.getSheetByName('Hours');
const categoriesSheet = mainSpreadsheet.getSheetByName('Categories');
const settings = getSettings();
const syncStartDate = new Date();
syncStartDate.setDate(syncStartDate.getDate() - Number(settings.syncFrom));
const syncEndDate = new Date();
syncEndDate.setDate(syncEndDate.getDate() + Number(settings.syncTo));
const isUpdateCalendarItemTitle = settings.isUpdateCalendarItemTitle;
const isUseCategoriesAsCalendarItemTitle = settings.isUseCategoriesAsCalendarItemTitle;
const isUpdateCalendarItemDescription = settings.isUpdateCalendarItemDescription;
const startTimeColumn = 3;
const endTimeColumn = 4;
const creatorsColumn = 6;
const originalTitleColumn = 7;
const originalDescriptionColumn = 8;
const clientColumn = 9;
const projectColumn = 10;
const taskColumn = 11;
const titleColumn = 12;
const descriptionColumn = 13;
const guestListColumn = 14;
const locationColumn = 15;
return Object.freeze({
run: run,
});
};
Colaboradores
Essa amostra foi criada por Jasper Duizendstra, arquiteto do Google Cloud e especialista do Google Developers. Encontre o Jasper no Twitter @Duizendstra.
Esta amostra é mantida pelo Google com a ajuda de especialistas do Google Developers.
Próximas etapas