编码水平:初级
时长:15 分钟
项目类型:使用自定义菜单实现自动化
目标
- 了解解决方案的功能。
- 了解 Apps 脚本服务在解决方案中的作用。
- 设置环境。
- 设置脚本。
- 运行脚本。
关于此解决方案
跟踪为客户处理项目所花费的时间。您可以在 Google 日历中记录与项目相关的时间,然后将其与 Google 表格同步,以创建时间表或将活动导入其他时间表管理系统。您可以按客户、项目和任务对时间进行分类。
运作方式
该脚本提供了一个边栏,可用于选择要同步的日历和时间段,以及是否用电子表格中输入的信息覆盖活动标题和说明。配置完这些设置后,您就可以同步活动并在信息中心查看您的活动了。
该脚本会将您在日历中指定的日历和时间段中的活动导入到电子表格中。您可以将客户、项目和任务添加到类别工作表中,然后在工时工作表中相应地标记活动。 这样,当您查看信息中心工作表时,便可按客户、项目和任务查看总时间。
Apps 脚本服务
此解决方案使用以下服务:
- HTML 服务 - 构建用于配置同步设置的边栏。
- 属性服务 - 存储用户在边栏上选择的设置。
- 日历服务 - 将活动信息发送到电子表格。
- 电子表格服务 - 将活动写入电子表格,并在配置后将更新的标题和说明信息发送到 Google 日历。
前提条件
如需使用此示例,您需要满足以下前提条件:
- Google 账号(Google Workspace 账号可能需要管理员批准)。
- 可访问互联网的网络浏览器。
设置环境
如果您计划使用现有日历,则可以跳过此步骤。
- 前往 calendar.google.com。
- 在其他日历旁边,点击“添加其他日历”图标 add > 创建新日历。
- 为日历命名,然后点击创建日历。
- 向日历中添加一些活动。
设置脚本
点击以下按钮,复制记录时间和活动示例电子表格。此解决方案的 Apps 脚本项目已附加到电子表格中。
制作副本
运行脚本
同步日历活动
- 依次点击 myTime > 设置。您可能需要刷新页面才能看到此自定义菜单。
根据提示为脚本授权。 如果 OAuth 权限请求页面显示警告此应用未经过验证,请继续操作,依次选择高级 > 前往“{项目名称}”(不安全)。
再次点击 myTime > 设置。
从可用日历列表中,选择您创建的日历以及要同步的任何其他日历。
配置其余设置,然后点击保存。
依次点击 myTime > 同步日历活动。
设置信息中心
- 前往类别工作表。
- 添加客户、项目和任务。
- 前往工时工作表。
- 为每个已同步的活动选择客户、项目和任务。
- 前往信息中心工作表。
- 第一部分提供每日总计。如需更新每日总计的日期列表,请更改单元格
A1
中的日期。 - 下一部分提供每周总数,对应于
A1
中选择的日期。 - 最后三个部分按任务、项目和客户提供总计。
- 第一部分提供每日总计。如需更新每日总计的日期列表,请更改单元格
查看代码
如需查看此解决方案的 Apps 脚本代码,请点击下方的查看源代码:
查看源代码
Code.gs
solutions/automations/calendar-timesheet/Code.js
// 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
solutions/automations/calendar-timesheet/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 上关注 Jasper (@Duizendstra)。
此示例由 Google 在 Google 开发者专家的帮助下维护。