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.
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
- Click the button below to make a copy of the Collect and review
timesheets sample spreadsheet.
Make a copy - At the top of the spreadsheet, click Timesheets > Form setup. You might need to refresh the page for this custom menu to appear.
- When prompted, authorize the script.
- After you authorize the script, click Timesheets > Form setup again.
Step 2: Add data
- Click Tools > Manage form > Go to live form.
- Fill out and submit the form with test data.
Step 3: Manage approvals and notify employees
- Switch back to the spreadsheet.
- From the Form Responses sheet, click Timesheets > Column setup.
- In the Approval column, mark your sample responses as Approved or Not Approved.
- Click Timesheets > Notify employees.
- 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.