在 Google 日历和 Google 表格中记录时间和活动

编码级别:新手
时长:15 分钟
项目类型:通过自定义菜单实现自动化

目标

  • 了解解决方案的用途。
  • 了解 Apps 脚本服务在此解决方案中执行的操作。
  • 设置环境。
  • 设置脚本。
  • 运行脚本。

关于此解决方案

跟踪客户花在项目上的时间。您可以在 Google 日历中记录与项目相关的时间,然后将其与 Google 表格同步,以创建出勤表或将您的活动导入其他出勤表管理系统。您可以按客户、项目和任务对时间进行分类。

Google 日历和 Google 表格中的活动

运作方式

该脚本提供了一个边栏,供您选择要同步的日历、要同步的时间段,以及是否使用您在电子表格中输入的信息覆盖活动标题和说明。配置上述设置后,您便可以在信息中心内同步事件和查看活动。

该脚本会将您指定的日历和时间段内的活动从日历引入电子表格。您可以将客户、项目和任务添加到“类别”表格,然后在“时间”表格中相应地标记事件。这样,在查看信息中心表格时,您就可以按客户、项目和任务查看总时间。

Apps 脚本服务

此解决方案使用以下服务:

  • HTML 服务 - 构建用于配置同步设置的边栏。
  • 房源服务 - 存储用户在边栏中选择的设置。
  • 日历服务 - 将活动信息发送到电子表格。
  • 电子表格服务 - 将活动写入电子表格,如果已配置,则会将更新后的标题和说明信息发送到 Google 日历。

前提条件

如需使用此示例,您需要满足以下前提条件:

  • Google 帐号(Google Workspace 帐号可能需要管理员批准)。
  • 能够访问互联网的网络浏览器。

设置您的环境

如果您打算使用现有日历,则可以跳过此步骤。

  1. 前往 calendar.google.com
  2. 其他日历旁边,点击“添加其他日历”图标 > 创建新日历
  3. 为您的日历命名,然后点击创建日历
  4. 将一些活动添加到日历。

设置脚本

点击以下按钮复制记录时间和活动示例电子表格。此解决方案的 Apps 脚本项目已附加到该电子表格中。
复制

运行脚本

同步日历活动

  1. 依次点击myTime > myTime。您可能需要刷新页面才能看到此自定义菜单。
  2. 出现提示时,为脚本授权。 如果 OAuth 同意屏幕显示 This app is not verify 警告,请依次选择 Advanced > Go to {Project Name} (unsafe),以继续操作。

  3. 再次点击myTime > Settings

  4. 从可用日历列表中,选择您创建的日历以及您想要同步的任何其他日历。

  5. 配置其余设置,然后点击保存

  6. 依次点击 myTime > 同步日历活动

设置信息中心

  1. 转到类别表格。
  2. 添加客户、项目和任务。
  3. 前往营业时间表格。
  4. 对于每个已同步的事件,请选择客户、项目和任务。
  5. 前往信息中心表格。
    • 第一部分提供每日总计。如需更新每日总计的日期列表,请更改单元格 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 (@Duizendstra) 上关注 Jasper。

此示例由 Google 在 Google 开发者专家的帮助下进行维护。

后续步骤