本頁面由 Cloud Translation API 翻譯而成。

記錄時間與活動；在 Google 日曆和 Google 試算表中進行活動

程式設計層級：新手
時間長度：15 分鐘
專案類型：使用自訂選單進行自動化作業

目標

瞭解解決方案的功能。
瞭解 Apps Script 服務在解決方案中的功能。
設定環境。
設定指令碼。
執行指令碼。

認識這項解決方案

追蹤客戶在專案上花費的時間。您可以在 Google 日曆中記錄專案相關時間，然後與 Google 試算表保持同步，以便建立出勤表，或是將活動匯入其他出勤表管理系統。您可以依客戶、專案和工作將時間分類。

日曆和試算表中的活動

運作方式

指令碼會提供側欄，可讓您選取要同步處理的日曆、同步處理時間範圍，以及是否要以在試算表中輸入的資訊覆寫事件名稱和說明。完成這些設定後，您就可以在資訊主頁上同步處理事件和查看活動。

這個指令碼會將您從 Google 日曆指定的日曆和時間範圍的活動傳入試算表。您可以將客戶、專案和工作新增至「類別」工作表，然後在「小時」工作表中為事件加上標記。如此一來，您檢視「資訊主頁」工作表時，就可以依客戶、專案和工作查看總時間。

Apps Script 服務

這項解決方案使用下列服務：

HTML 服務：建構用於調整同步處理設定的側欄。
屬性服務：儲存使用者在側欄中選取的設定。
日曆服務：將事件資訊傳送至試算表。
試算表服務：將活動寫入試算表，如果已設定，會將最新的標題和說明資訊傳送至日曆。

必要條件

如要使用這個範例，您必須具備以下必要條件：

Google 帳戶 (Google Workspace 帳戶可能需要取得管理員核准)。
可連上網際網路的網路瀏覽器。

設定環境

如果您打算使用現有日曆，可以略過這個步驟。

前往 calendar.google.com。
在「其他日曆」旁邊，依序按一下「新增其他日曆」 >「建立新日曆」。
命名日曆，然後按一下「建立日曆」。
在日曆中新增一些活動。

設定指令碼

點選下方按鈕，複製「Record time and activity」範例試算表。這項解決方案的 Apps Script 專案已附加到試算表。
建立副本

執行指令碼

同步處理日曆活動

依序點選「myTime」myTime>「Settings」myTime。您可能需要重新整理頁面，才能看到這個自訂選單。
出現提示訊息時，請授權執行指令碼。如果 OAuth 同意畫面顯示警告「This app has not verification」(這個應用程式尚未驗證)，請依序選取「Advanced」(進階) >「Go to {Project Name} (unsafe)」 (前往 {Project Name} (不安全))。
再次依序點選「myTime」myTime>「Settings」myTime。
在可用日曆清單中，選取您建立的日曆及任何其他要同步處理的日曆。
調整其他設定，然後按一下「儲存」。
依序點選「myTime」myTime>「Sync Calendar events」myTime。

設定資訊主頁

前往「類別」工作表。
新增客戶、專案和工作。
前往「營業時間」工作表。
針對每個已同步處理的活動，選取客戶、專案和工作。
前往「資訊主頁」工作表。
- 第一個部分會提供每日總計。如要更新每日總計的日期清單，請變更儲存格 A1 中的日期。
- 下一節提供每週總計，且對應於 A1 中選取的日期。
- 最後三個部分提供工作、專案和客戶的整體總數，

查看程式碼

如要查看這項解決方案的 Apps Script 程式碼，請點選下方的「查看原始碼」：

查看原始碼

Code.gs

solutions/automations/calendar-timesheet/Code.js

前往 GitHub 查看

// 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