Collect and review timesheets from employees

Coding level: Beginner
Duration: 15 minutes
Project type: Automation with a custom menu

About this solution

Collect employees' weekly timesheets in a Google Form. From Google Sheets, calculate employees' pay, approve or disapprove their timesheets, and send emails notifying them of their approval status.

Timesheet sample

How it works

The script creates a Google Form and links the responses to the spreadsheet. The script adds columns to the form responses sheet that calculate employees' total hours and weekly pay. Once a timesheet is reviewed, the script emails the employee the approval status.

Apps Script services

This solution uses the following services:

  • Spreadsheet Service: Receives form responses and writes columns to the sheet to manage approvals.
  • Forms Service: Creates a form for employees to enter their weekly timesheets.
  • Mail Service: Sends emails to employees to notify them when their timesheets have been approved or not.

Before you begin

To use this sample, you need the following prerequisites:

  • A Google Account (Google Workspace accounts might require administrator approval)
  • A web browser with access to the internet

Try it

Step 1: Set up the spreadsheet

  1. Click the button below to make a copy of the Collect and review timesheets sample spreadsheet.
    Make a copy
  2. At the top of the spreadsheet, click Timesheets > Form setup. You might need to refresh the page for this custom menu to appear.
  3. When prompted, authorize the script.
  4. After you authorize the script, click Timesheets > Form setup again.

Step 2: Add data

  1. Click Tools > Manage form > Go to live form.
  2. Fill out and submit the form with test data.

Step 3: Manage approvals and notify employees

  1. Switch back to the spreadsheet.
  2. From the Form Responses sheet, click Timesheets > Column setup.
  3. In the Approval column, mark your sample responses as Approved or Not Approved.
  4. Click Timesheets > Notify employees.
  5. Check your email to confirm you received an approval or rejection email.

Open the Apps Script project

The sample script's code includes in-line comments to help you understand how it works. To access the code, in the spreadsheet, click Extensions > Apps Script.

Contributors

This sample is maintained by Google with the help of Google Developer Experts.