收集并检查员工的出勤表

编码水平:初级
时长:15 分钟
项目类型:使用自定义菜单实现自动化

目标

  • 了解解决方案的功能。
  • 了解 Apps 脚本服务在解决方案中的作用。
  • 设置脚本。
  • 运行脚本。

关于此解决方案

使用 Google 表单收集员工的每周出勤表。在 Google 表格中,计算员工薪酬,批准或否决员工的出勤表,并发送电子邮件通知他们审批结果。

工时表示例

运作方式

该脚本会创建一个表单,并将回答链接到电子表格。该脚本会向表单回答工作表中添加用于计算员工总工时和每周薪资的列。出勤表经过审核后,脚本会通过电子邮件将审批状态发送给员工。

Apps 脚本服务

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

  • 电子表格服务 - 接收表单响应并将列写入工作表以管理审批。
  • 表单服务 - 创建一个表单,供员工输入每周出勤表。
  • 邮件服务 - 向员工发送电子邮件,通知他们出勤表是否已获批。

前提条件

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

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

设置脚本

创建 Apps 脚本项目

  1. 点击以下按钮,复制收集和审核工作表示例电子表格。此解决方案的 Apps 脚本项目已附加到电子表格。
    制作副本
  2. 在复制的电子表格中,依次点击工作表 > 表单设置。您可能需要刷新页面才能看到此自定义菜单。
  3. 根据提示为脚本授权。 如果 OAuth 权限请求页面显示警告此应用未经过验证,请继续操作,依次选择高级 > 前往“{项目名称}”(不安全)

  4. 授权脚本后,再次点击工作表 > 表单设置

添加数据

  1. 依次点击工具 > 管理表单 > 前往实时表单
  2. 填写并提交包含测试数据的表单。

运行脚本

  1. 切换回电子表格。
  2. 表单回答工作表中,依次点击工作表 > 列设置
  3. 审批列中,将您的样本回答标记为已批准未批准
  4. 依次点击工作表 > 通知员工
  5. 查看您的电子邮件,确认您是否收到了批准或拒绝电子邮件。

查看代码

如需查看此解决方案的 Apps 脚本代码,请点击下方的查看源代码

查看源代码

Code.gs

solutions/automations/timesheets/Code.js
// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/timesheets

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

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

// Global variables representing the index of certain columns.
let COLUMN_NUMBER = {
  EMAIL: 2,
  HOURS_START: 4,
  HOURS_END: 8,
  HOURLY_PAY: 9,
  TOTAL_HOURS: 10,
  CALC_PAY: 11,
  APPROVAL: 12,
  NOTIFY: 13,
};

// Global variables:
let APPROVED_EMAIL_SUBJECT = 'Weekly Timesheet APPROVED';
let REJECTED_EMAIL_SUBJECT = 'Weekly Timesheet NOT APPROVED';
let APPROVED_EMAIL_MESSAGE = 'Your timesheet has been approved.';
let REJECTED_EMAIL_MESSAGE = 'Your timesheet has not been approved.';

/** 
 * Creates the menu item "Timesheets" for user to run scripts on drop-down.
 */
function onOpen() {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu('Timesheets')
      .addItem('Form setup', 'setUpForm')
      .addItem('Column setup', 'columnSetup')
      .addItem('Notify employees', 'checkApprovedStatusToNotify')
      .addToUi();
}

/** 
 * Adds "WEEKLY PAY" column with calculated values using array formulas. 
 * Adds an "APPROVAL" column at the end of the sheet, containing 
 * drop-down menus to either approve/disapprove employee timesheets.  
 * Adds a "NOTIFIED STATUS" column indicating whether or not an
 * employee has yet been e mailed.
 */
function columnSetup() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let lastCol = sheet.getLastColumn();
  let lastRow = sheet.getLastRow();
  let frozenRows = sheet.getFrozenRows();
  let beginningRow = frozenRows + 1;
  let numRows = lastRow - frozenRows;

  // Calls helper functions to add new columns.
  addCalculatePayColumn(sheet, beginningRow);
  addApprovalColumn(sheet, beginningRow, numRows);
  addNotifiedColumn(sheet, beginningRow, numRows);
}

/**
 * Adds TOTAL HOURS and CALCULATE PAY columns and automatically calculates
 * every employee's weekly pay.
 *
 * @param {Object} sheet Spreadsheet object of current sheet.
 * @param {Integer} beginningRow Index of beginning row.
 */
function addCalculatePayColumn(sheet, beginningRow) {
  sheet.insertColumnAfter(COLUMN_NUMBER.HOURLY_PAY);
  sheet.getRange(1, COLUMN_NUMBER.TOTAL_HOURS).setValue('TOTAL HOURS');
  sheet.getRange(1, COLUMN_NUMBER.CALC_PAY).setValue('WEEKLY PAY');

  // Calculates weekly total hours.
  sheet.getRange(beginningRow, COLUMN_NUMBER.TOTAL_HOURS)
      .setFormula('=ArrayFormula(D2:D+E2:E+F2:F+G2:G+H2:H)');
  // Calculates weekly pay.
  sheet.getRange(beginningRow, COLUMN_NUMBER.CALC_PAY)
      .setFormula('=ArrayFormula(I2:I * J2:J)');
}

/**
 * Adds an APPROVAL column allowing managers to approve/
 * disapprove of each employee's timesheet.
 *
 * @param {Object} sheet Spreadsheet object of current sheet.
 * @param {Integer} beginningRow Index of beginning row.
 * @param {Integer} numRows Number of rows currently in use.
 */
function addApprovalColumn(sheet, beginningRow, numRows) {
  sheet.insertColumnAfter(COLUMN_NUMBER.CALC_PAY);
  sheet.getRange(1, COLUMN_NUMBER.APPROVAL).setValue('APPROVAL');

  // Make sure approval column is all drop-down menus.
  let approvalColumnRange = sheet.getRange(beginningRow, COLUMN_NUMBER.APPROVAL,
      numRows, 1);
  let dropdownValues = ['APPROVED', 'NOT APPROVED', 'IN PROGRESS'];
  let rule = SpreadsheetApp.newDataValidation().requireValueInList(dropdownValues)
      .build();
  approvalColumnRange.setDataValidation(rule);
  approvalColumnRange.setValue('IN PROGRESS');
}

/**
 * Adds a NOTIFIED column allowing managers to see which employees
 * have/have not yet been notified of their approval status.
 *
 * @param {Object} sheet Spreadsheet object of current sheet.
 * @param {Integer} beginningRow Index of beginning row.
 * @param {Integer} numRows Number of rows currently in use.
 */
function addNotifiedColumn(sheet, beginningRow, numRows) {
  sheet.insertColumnAfter(COLUMN_NUMBER.APPROVAL); // global
  sheet.getRange(1, COLUMN_NUMBER.APPROVAL + 1).setValue('NOTIFIED STATUS');

  // Make sure notified column is all drop-down menus.
  let notifiedColumnRange = sheet.getRange(beginningRow, COLUMN_NUMBER.APPROVAL
      + 1, numRows, 1);
  dropdownValues = ['NOTIFIED', 'PENDING'];
  rule = SpreadsheetApp.newDataValidation().requireValueInList(dropdownValues)
      .build();
  notifiedColumnRange.setDataValidation(rule);
  notifiedColumnRange.setValue('PENDING');  
}

/**
 * Sets the notification status to NOTIFIED for employees
 * who have received a notification email.
 *
 * @param {Object} sheet Current Spreadsheet.
 * @param {Object} notifiedValues Array of notified values.
 * @param {Integer} i Current status in the for loop.
 * @parma {Integer} beginningRow Row where iterations began.
 */
function updateNotifiedStatus(sheet, notifiedValues, i, beginningRow) {
  // Update notification status.
  notifiedValues[i][0] = 'NOTIFIED';
  sheet.getRange(i + beginningRow, COLUMN_NUMBER.NOTIFY).setValue('NOTIFIED');
}

/** 
 * Checks the approval status of every employee, and calls helper functions
 * to notify employees via email & update their notification status.
 */
function checkApprovedStatusToNotify() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let lastRow = sheet.getLastRow();
  let lastCol = sheet.getLastColumn();
  // lastCol here is the NOTIFIED column.
  let frozenRows = sheet.getFrozenRows();
  let beginningRow = frozenRows + 1;
  let numRows = lastRow - frozenRows;

  // Gets ranges of email, approval, and notified values for every employee.
  let emailValues = sheet.getRange(beginningRow, COLUMN_NUMBER.EMAIL, numRows, 1).getValues();
  let approvalValues = sheet.getRange(beginningRow, COLUMN_NUMBER.APPROVAL,
      lastRow - frozenRows, 1).getValues();
  let notifiedValues = sheet.getRange(beginningRow, COLUMN_NUMBER.NOTIFY, numRows,
      1).getValues();

  // Traverses through employee's row.
  for (let i = 0; i < numRows; i++) {
    // Do not notify twice.
    if (notifiedValues[i][0] == 'NOTIFIED') {
      continue;
    }
    let emailAddress = emailValues[i][0];
    let approvalValue = approvalValues[i][0];

    // Sends notifying emails & update status.
    if (approvalValue == 'IN PROGRESS') {
      continue;
    } else if (approvalValue == 'APPROVED') {
      MailApp.sendEmail(emailAddress, APPROVED_EMAIL_SUBJECT, APPROVED_EMAIL_MESSAGE);
      updateNotifiedStatus(sheet, notifiedValues, i, beginningRow);
    } else if (approvalValue == 'NOT APPROVED') {
      MailApp.sendEmail(emailAddress,REJECTED_EMAIL_SUBJECT, REJECTED_EMAIL_MESSAGE);
      updateNotifiedStatus(sheet, notifiedValues, i, beginningRow);
    }  
  }
}

/** 
 * Set up the Timesheets Responses form, & link the form's trigger to 
 * send manager an email when a new request is submitted.
 */
function setUpForm() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet();
  if (sheet.getFormUrl()) {
    let msg = 'Form already exists. Unlink the form and try again.';
    SpreadsheetApp.getUi().alert(msg);
    return;
  }

  // Create the form.
  let form = FormApp.create('Weekly Timesheets')
      .setCollectEmail(true)
      .setDestination(FormApp.DestinationType.SPREADSHEET, sheet.getId())
      .setLimitOneResponsePerUser(false);
  form.addTextItem().setTitle('Employee Name:').setRequired(true);
  form.addTextItem().setTitle('Monday Hours:').setRequired(true);
  form.addTextItem().setTitle('Tuesday Hours:').setRequired(true);
  form.addTextItem().setTitle('Wednesday Hours:').setRequired(true);
  form.addTextItem().setTitle('Thursday Hours:').setRequired(true);
  form.addTextItem().setTitle('Friday Hours:').setRequired(true);
  form.addTextItem().setTitle('HourlyWage:').setRequired(true);

  // Set up on form submit trigger.
  ScriptApp.newTrigger('onFormSubmit')
      .forForm(form)
      .onFormSubmit()
      .create(); 
}

/**
 * Handle new form submissions to trigger the workflow.
 *
 * @param {Object} event Form submit event
 */
function onFormSubmit(event) {
  let response = getResponsesByName(event.response);

  // Load form responses into a new row.
  let row = ['New',
    '',
    response['Emoloyee Email:'],
    response['Employee Name:'],
    response['Monday Hours:'],
    response['Tuesday Hours:'],
    response['Wednesday Hours:'],
    response['Thursday Hours:'],
    response['Friday Hours:'],
    response['Hourly Wage:']];
  let sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.appendRow(row);
}

/**
 * Converts a form response to an object keyed by the item titles. Allows easier
 * access to response values.
 *
 * @param {FormResponse} response
 * @return {Object} Form values keyed by question title
 */
function getResponsesByName(response) {
  let initialValue = {
    email: response.getRespondentEmail(),
    timestamp: response.getTimestamp(),
  };
  return response.getItemResponses().reduce(function(obj, itemResponse) {
    let key = itemResponse.getItem().getTitle();
    obj[key] = itemResponse.getResponse();
    return obj;
  }, initialValue);
}

贡献者

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

后续步骤