Собрать и усилить; просматривать табели учета рабочего времени сотрудников

Уровень кодирования : начинающий
Продолжительность : 15 минут
Тип проекта : Автоматизация с пользовательским меню

Цели

• Поймите, что делает решение.
• Понять, какие функции выполняют службы Apps Script в рамках решения.
• Настройте сценарий.
• Запустите скрипт.

Об этом решении

Собирайте еженедельные табели учета рабочего времени сотрудников с помощью формы в Google Формах. В Google Таблицах вы можете рассчитывать заработную плату сотрудников, утверждать или отклонять их табели учета рабочего времени и отправлять им электронные письма с уведомлением о статусе утверждения.

Как это работает

Скрипт создаёт форму и привязывает ответы к электронной таблице. Скрипт добавляет столбцы в таблицу ответов формы, в которых рассчитывается общее количество отработанных часов и еженедельная заработная плата сотрудников. После проверки табеля учета рабочего времени скрипт отправляет сотруднику по электронной почте информацию о статусе утверждения.

Службы скриптов приложений

Это решение использует следующие сервисы:

• Служба электронных таблиц — получает ответы на формы и записывает столбцы в таблицу для управления утверждениями.
• Служба форм – создает форму, с помощью которой сотрудники могут вводить свои еженедельные табели учета рабочего времени.
• Почтовая служба — отправляет электронные письма сотрудникам с уведомлением об утверждении или отклонении их табелей учета рабочего времени.

Предпосылки

Для использования этого образца вам необходимы следующие предварительные условия:

• Учетная запись Google (для учетных записей Google Workspace может потребоваться одобрение администратора).
• Веб-браузер с доступом в Интернет.

Настройте сценарий

Создайте проект Apps Script

1. Нажмите следующую кнопку, чтобы создать копию таблицы «Сбор и проверка табелей учета рабочего времени» . Проект Apps Script для этого решения прикреплён к таблице.
   Сделать копию
2. В скопированной таблице выберите «Табель учета рабочего времени» > «Настройка формы» . Возможно, вам потребуется обновить страницу, чтобы это меню появилось.

3. При появлении запроса авторизуйте скрипт. Если на экране согласия OAuth отображается предупреждение « Это приложение не проверено» , выберите «Дополнительно» > «Перейти к {Имя проекта} (небезопасно)» .

4. После авторизации скрипта снова нажмите «Табель учета рабочего времени» > «Настройка формы» .

Добавить данные

1. Нажмите Инструменты > Управление формой > Перейти к активной форме .
2. Заполните и отправьте форму с тестовыми данными.

Запустить скрипт

1. Вернитесь к электронной таблице.
2. На листе «Ответы на формы» нажмите «Табели учета рабочего времени» > «Настройка столбцов» .
3. В столбце «Утверждение» отметьте свои примеры ответов как «Утверждено» или «Не утверждено» .
4. Нажмите Табели учета рабочего времени > Уведомить сотрудников .
5. Проверьте свою электронную почту, чтобы убедиться, что вы получили письмо с одобрением или отклонением.

Проверьте код

Чтобы просмотреть код Apps Script для этого решения, нажмите «Просмотреть исходный код» ниже:

Посмотреть исходный код

Код.gs

решения/автоматизация/табели учета рабочего времени/Code.js

Посмотреть на GitHub

// 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.
const 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:
const APPROVED_EMAIL_SUBJECT = "Weekly Timesheet APPROVED";
const REJECTED_EMAIL_SUBJECT = "Weekly Timesheet NOT APPROVED";
const APPROVED_EMAIL_MESSAGE = "Your timesheet has been approved.";
const REJECTED_EMAIL_MESSAGE = "Your timesheet has not been approved.";

/**
 * Creates the menu item "Timesheets" for user to run scripts on drop-down.
 */
function onOpen() {
  const 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() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastCol = sheet.getLastColumn();
  const lastRow = sheet.getLastRow();
  const frozenRows = sheet.getFrozenRows();
  const beginningRow = frozenRows + 1;
  const 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.
  const approvalColumnRange = sheet.getRange(
    beginningRow,
    COLUMN_NUMBER.APPROVAL,
    numRows,
    1,
  );
  const dropdownValues = ["APPROVED", "NOT APPROVED", "IN PROGRESS"];
  const 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.
  const notifiedColumnRange = sheet.getRange(
    beginningRow,
    COLUMN_NUMBER.APPROVAL + 1,
    numRows,
    1,
  );
  const dropdownValues = ["NOTIFIED", "PENDING"];
  const 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() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();
  // lastCol here is the NOTIFIED column.
  const frozenRows = sheet.getFrozenRows();
  const beginningRow = frozenRows + 1;
  const numRows = lastRow - frozenRows;

  // Gets ranges of email, approval, and notified values for every employee.
  const emailValues = sheet
    .getRange(beginningRow, COLUMN_NUMBER.EMAIL, numRows, 1)
    .getValues();
  const approvalValues = sheet
    .getRange(beginningRow, COLUMN_NUMBER.APPROVAL, lastRow - frozenRows, 1)
    .getValues();
  const 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;
    }
    const emailAddress = emailValues[i][0];
    const approvalValue = approvalValues[i][0];

    // Sends notifying emails & update status.
    if (approvalValue === "IN PROGRESS") {
    } 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() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  if (sheet.getFormUrl()) {
    const msg = "Form already exists. Unlink the form and try again.";
    SpreadsheetApp.getUi().alert(msg);
    return;
  }

  // Create the form.
  const 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) {
  const response = getResponsesByName(event.response);

  // Load form responses into a new row.
  const 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:"],
  ];
  const 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) {
  const initialValue = {
    email: response.getRespondentEmail(),
    timestamp: response.getTimestamp(),
  };
  return response.getItemResponses().reduce((obj, itemResponse) => {
    const key = itemResponse.getItem().getTitle();
    obj[key] = itemResponse.getResponse();
    return obj;
  }, initialValue);
}

Авторы

Этот образец поддерживается компанией Google с помощью экспертов Google Developer Experts.

Следующие шаги

• Пользовательские меню в Google Workspace
• Расширить Google Таблицы