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, click Review permissions and 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.