编码级别:新手
时长:15 分钟
项目类型:通过自定义菜单实现自动化
目标
- 了解解决方案的用途。
- 了解 Apps 脚本服务在此解决方案中执行的操作。
- 设置环境。
- 设置脚本。
- 运行脚本。
关于此解决方案
跟踪客户花在项目上的时间。您可以在 Google 日历中记录与项目相关的时间,然后将其与 Google 表格同步,以创建出勤表或将您的活动导入其他出勤表管理系统。您可以按客户、项目和任务对时间进行分类。
运作方式
该脚本提供了一个边栏,供您选择要同步的日历、要同步的时间段,以及是否使用您在电子表格中输入的信息覆盖活动标题和说明。配置上述设置后,您便可以在信息中心内同步事件和查看活动。
该脚本会将您指定的日历和时间段内的活动从日历引入电子表格。您可以将客户、项目和任务添加到“类别”表格,然后在“时间”表格中相应地标记事件。这样,在查看信息中心表格时,您就可以按客户、项目和任务查看总时间。
Apps 脚本服务
此解决方案使用以下服务:
- HTML 服务 - 构建用于配置同步设置的边栏。
- 房源服务 - 存储用户在边栏中选择的设置。
- 日历服务 - 将活动信息发送到电子表格。
- 电子表格服务 - 将活动写入电子表格,如果已配置,则会将更新后的标题和说明信息发送到 Google 日历。
前提条件
如需使用此示例,您需要满足以下前提条件:
- Google 帐号(Google Workspace 帐号可能需要管理员批准)。
- 能够访问互联网的网络浏览器。
设置您的环境
如果您打算使用现有日历,则可以跳过此步骤。
- 前往 calendar.google.com。
- 在其他日历旁边,点击“添加其他日历”图标 add > 创建新日历。
- 为您的日历命名,然后点击创建日历。
- 将一些活动添加到日历。
设置脚本
点击以下按钮复制记录时间和活动示例电子表格。此解决方案的 Apps 脚本项目已附加到该电子表格中。
复制
运行脚本
同步日历活动
- 依次点击myTime > myTime。您可能需要刷新页面才能看到此自定义菜单。
出现提示时,为脚本授权。 如果 OAuth 同意屏幕显示 This app is not verify 警告,请依次选择 Advanced > Go to {Project Name} (unsafe),以继续操作。
再次点击myTime > Settings。
从可用日历列表中,选择您创建的日历以及您想要同步的任何其他日历。
配置其余设置,然后点击保存。
依次点击 myTime > 同步日历活动。
设置信息中心
- 转到类别表格。
- 添加客户、项目和任务。
- 前往营业时间表格。
- 对于每个已同步的事件,请选择客户、项目和任务。
- 前往信息中心表格。
- 第一部分提供每日总计。如需更新每日总计的日期列表,请更改单元格
A1
中的日期。 - 下一部分提供每周总计,与
A1
中选择的日期相对应。 - 后三个部分按任务、项目和客户提供了总体总计。
- 第一部分提供每日总计。如需更新每日总计的日期列表,请更改单元格
查看代码
要查看此解决方案的 Apps 脚本代码,请点击下面的查看源代码:
查看源代码
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, }); };
Page.html
<!DOCTYPE html> <!-- Copyright 2022 Google LLC 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 http://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. --> <html> <head> <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> <style> #main { display: none } #categories-as-item-title { display: none } #show_title_warning { display: none } #show_description_warning { display: none } .red { color: red; } .branding-below { bottom: 56px; top: 0; } input[type=number] { width: 50px; height: 15px; } </style> </head> <body> <div class="sidebar branding-below" id="wait"> Please wait... </div> <div class="sidebar branding-below" id="main"> <div class="block" id="checks"> <b>Synchronise calendars</b> <div> <span class="error" id="calendar-message"></span> </div> </div> <div class="block"> <b>Synchronisation period</b> <br>Synchronise from the last <input type="number" name="sync-from" id="sync-from"> days <br>Synchronise up to the coming <input type="number" name="sync-to" id="sync-to"> days </div> <div class="block"> <b>Update the calendar items</b><br> <input type="checkbox" id="is-update-calendar-item-title"> <label for="is-update-calendar-item-title">Overwrite the calendar item title</label> <span class="secondary" id="show_title_warning">The calendar title will be overwritten with the values in title column of the sheet</span> </div> <div id="categories-as-item-title"> <input type="checkbox" id="is-use-categories-as-item-title"> <label for="is-use-categories-as-item-title">Use categories as the calendar item title</label> </div> <div class="block"> <input type="checkbox" id="is-update-calendar-item-description"> <label for="is-update-calendar-item-description">Overwrite the calendar item description</label> <span class="secondary" id="show_description_warning">The calendar description will be overwritten with the values in description column of the sheet</span> </div> <div class="block"> <button class="blue" onClick="saveSettings()">Save</button> </div> <div class="block"> <span class="error" id="generic-error"></span> <span class="gray" id="generic-message"></span> </div> </div> <div class="sidebar bottom"> <span class="gray"> myTime v1.2.0</span> </div> </body> <script> // event handler for categrories document.getElementById('is-update-calendar-item-title').addEventListener('change', (event) => { if (event.target.checked) { document.getElementById('categories-as-item-title').style.display = "block"; document.getElementById('show_title_warning').style.display = "block"; } else { document.getElementById('categories-as-item-title').style.display = "none"; document.getElementById('is-use-categories-as-item-title').checked = false; document.getElementById('show_title_warning').style.display = "none"; } }) document.getElementById('is-update-calendar-item-description').addEventListener('change', (event) => { if (event.target.checked) { document.getElementById('show_description_warning').style.display = "block"; } else { document.getElementById('show_description_warning').style.display = "none"; } }) // generic error handler const onFailure = (error) => { console.debug(error); document.getElementById('generic-error').innerHTML = error.message; } // receiving the settings const onSuccessGetSettings = (settings) => { console.debug(settings); settings.calendarSettings.forEach((calendar, index) => { const div = document.createElement('div'); const check = document.createElement('input'); check.className = 'calendar-check'; check.className = 'calendar-check red'; check.type = 'checkbox'; check.id = 'calendar' + index; check.value = (calendar.id); check.name = (calendar.name); check.checked = (calendar.sync); const label = document.createElement('label') label.htmlFor = "calendar" + index; label.appendChild(document.createTextNode(calendar.name)); if (index == 0) { label.className = 'red'; } div.appendChild(check); div.appendChild(label); document.getElementById('checks').appendChild(div); }); document.getElementById('sync-from').value = settings.syncFrom || 31; document.getElementById('sync-to').value = settings.syncTo || 31; document.getElementById('is-update-calendar-item-title').checked = settings.isUpdateCalendarItemTitle; if (settings.isUpdateCalendarItemTitle) { document.getElementById('categories-as-item-title').style.display = "block"; document.getElementById('is-use-categories-as-item-title').checked = settings.isUseCategoriesAsCalendarItemTitle; document.getElementById('show_title_warning').style.display = "block"; } if (settings.isUpdateCalendarItemDescription) { document.getElementById('is-update-calendar-item-description').checked = settings.isUpdateCalendarItemDescription; document.getElementById('show_description_warning').style.display = "block"; } document.getElementById('wait').style.display = "none"; document.getElementById('main').style.display = "block"; } // receiving the settings saved confirmation const onSuccessSaveSettings = (msg) => { console.debug(msg); document.getElementById('generic-message').innerHTML = msg; } // save the settings const saveSettings = () => { document.getElementById('generic-message').innerHTML = ''; const checks = document.getElementsByClassName('calendar-check'); const calendarSettings = []; for (let check of checks) { if (!check.checked) { continue; } calendarSettings.push({ name: check.name, id: check.value, sync: check.checked }); } const settings = {}; settings.calendarSettings = calendarSettings; settings.syncFrom = document.getElementById('sync-from').value; settings.syncTo = document.getElementById('sync-to').value; settings.isUpdateCalendarItemTitle = document.getElementById('is-update-calendar-item-title').checked; if (settings.isUpdateCalendarItemTitle) { settings.isUseCategoriesAsCalendarItemTitle = document.getElementById('is-use-categories-as-item-title').checked; } if (!settings.isUpdateCalendarItemTitle) { settings.isUseCategoriesAsCalendarItemTitle = false; } settings.isUpdateCalendarItemDescription = document.getElementById('is-update-calendar-item-description').checked; console.debug(settings) google.script.run .withFailureHandler(onFailure) .withSuccessHandler(onSuccessSaveSettings) .saveSettings(settings); } // get the initial settings google.script.run .withFailureHandler(onFailure) .withSuccessHandler(onSuccessGetSettings) .getSettings(); </script> </html>
贡献者
此示例由 Google Cloud 架构师和 Google 开发者专家 Jasper Duizendstra 创建。您可以在 Twitter (@Duizendstra) 上关注 Jasper。
此示例由 Google 在 Google 开发者专家的帮助下进行维护。