Level coding: Pemula
Durasi: 15 menit
Jenis project: Otomatisasi dengan menu kustom
Tujuan
- Pahami fungsi solusi tersebut.
- Pahami apa yang dilakukan layanan Apps Script dalam
solusi tersebut.
- Menyiapkan lingkungan Anda.
- Menyiapkan skrip.
- Jalankan skrip.
Tentang solusi ini
Melacak waktu yang dihabiskan pada proyek untuk pelanggan. Anda dapat mencatat waktu terkait project di Google Kalender, lalu menyinkronkannya dengan Google Spreadsheet untuk membuat timesheet atau mengimpor aktivitas Anda ke sistem pengelolaan timesheet lain. Anda dapat mengategorikan waktu Anda berdasarkan pelanggan, proyek, dan tugas.
Cara kerjanya
Skrip ini menyediakan sidebar yang memungkinkan Anda memilih kalender yang akan disinkronkan, periode waktu yang akan disinkronkan, dan apakah akan menimpa judul dan
deskripsi acara dengan informasi yang dimasukkan dalam spreadsheet. Setelah setelan tersebut
dikonfigurasi, Anda dapat menyinkronkan peristiwa dan melihat aktivitas di dasbor.
Skrip ini membawa acara dari kalender dan jangka waktu yang Anda tentukan dari
Kalender ke spreadsheet. Anda dapat menambahkan pelanggan, project, dan tugas ke sheet kategori, lalu memberi tag pada peristiwa yang sesuai di sheet hours.
Dengan cara ini, saat melihat sheet dashboard, Anda dapat melihat total waktu berdasarkan pelanggan, project, dan tugas.
Layanan Apps Script
Solusi ini menggunakan layanan berikut:
- Layanan HTML–Membuat sidebar yang digunakan untuk
mengonfigurasi setelan sinkronisasi.
- Layanan properti–Menyimpan setelan
yang dipilih pengguna di sidebar.
- Layanan Kalender–Mengirim
informasi acara ke spreadsheet.
- Layanan spreadsheet–Menulis acara
ke spreadsheet, dan jika dikonfigurasi, mengirimkan informasi judul dan deskripsi
yang diperbarui ke Kalender.
Prasyarat
Untuk menggunakan contoh ini, Anda memerlukan prasyarat berikut:
- Akun Google (akun Google Workspace mungkin
memerlukan persetujuan administrator).
- Browser web dengan akses ke internet.
Menyiapkan lingkungan Anda
Jika berencana menggunakan kalender yang sudah ada, Anda dapat melewati langkah ini.
- Buka calendar.google.com.
- Di samping Kalender lainnya, klik Tambahkan kalender lain add
> Buat kalender baru.
- Beri nama kalender Anda dan klik Buat kalender.
- Tambahkan beberapa acara ke kalender.
Menyiapkan skrip
Klik tombol berikut untuk membuat salinan spreadsheet contoh Record time and activities. Project Apps Script untuk solusi
ini dilampirkan ke
spreadsheet.
Buat salinan
Jalankan skrip:
Menyinkronkan acara kalender
- Klik myTime > Setelan. Anda mungkin
perlu memuat ulang halaman agar menu kustom ini muncul.
Saat diminta, izinkan skrip.
Jika layar izin OAuth menampilkan peringatan, Aplikasi ini tidak diverifikasi,
lanjutkan dengan memilih Lanjutan >
Buka {Project Name} (tidak aman).
Klik myTime > Settings lagi.
Dari daftar kalender yang tersedia, pilih kalender yang Anda buat dan kalender lain yang ingin disinkronkan.
Konfigurasikan setelan lainnya, lalu klik Simpan.
Klik myTime > Sync kalender
events.
Menyiapkan dasbor
- Buka sheet Kategori.
- Tambahkan pelanggan, project, dan tugas.
- Buka sheet Jam buka.
- Untuk setiap acara yang disinkronkan, pilih pelanggan, project, dan tugas.
- Buka sheet Dashboard.
- Bagian pertama memberikan total harian. Guna memperbarui daftar tanggal untuk
total harian, ubah tanggal dalam sel
A1
.
- Bagian berikutnya memberikan total mingguan dan sesuai dengan tanggal
yang dipilih di
A1
.
- Tiga bagian terakhir memberikan total keseluruhan berdasarkan tugas, proyek, dan
pelanggan.
Meninjau kode
Guna meninjau kode Apps Script untuk solusi ini, klik
Lihat kode sumber di bawah:
Melihat kode sumber
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,
});
};
Kontributor
Contoh ini dibuat oleh Jasper Duizendstra, Arsitek Google Cloud dan Pakar
Developer Google. Temukan Jasper di Twitter @Duizendstra.
Contoh ini dikelola oleh Google dengan bantuan Pakar Google Developers.
Langkah berikutnya