PTO and HR systems in general can be pricey and a pain to customize. Fortunately, those of us in the Google Workspace ecosystem can create a robust, highly modular time off approval system without spending any extra money. In this GoogleFlows article we’ll be showing you how to build a comprehensive PTO system using the tools you’re already paying for.
Here’s a list of the tech we’ll use along with the purpose each item serves.
Google Forms - used to capture PTO requests
Google Sheets - the database for managers to track PTO
Apps Script - does the heavy lifting for automation
Gmail - where approvals will happen
How This Google PTO System Will Work
Feel free to tweak this to suit your process but this workflow for PTO should get you started:
Employee uses the Google Forms link to submit a PTO Request
Once complete, the manager receives a Gmail with an Approve or Deny button.
The manager either approves or denies the request.
Either way, the request status field is updated.
The employee receives an email with the manager’s decision.
Step 1: Build Your PTO Request Form
Create a new Google Form with the following fields:
Employee Name (Multiple Choice) - You’ll need exact matches here, so no free form.
Start Date (Date)
End Date (Date)
Hours Per Day (Text)
Notes (Paragraph, optional)
Feel free to add more, I know some organizations like to track the PTO type. Try to keep the number of fields to a minimum to increase adoption and reduce the complexity of the automations.
Step 2: Connect Your New Form to Google Sheets
In the Form editor perform the following actions:
Click Responses
Click the green Sheets icon
Create a new spreadsheet
Rename the response sheet to:
Requests
Now add these columns to the right of the form responses:
Request ID
Status
Decision By
Decision Timestamp
Token
The code we’re going to write in AppScripts will need these columns.
Step 3: Open Apps Script
From the Google Sheet:
Click Extensions → Apps Script
Rename the project to something like:
PTO Approval Automation
Get rid of the boilerplate code that is prepopulated.
Step 4: Add the Core Automation Script
Paste the following script. This handles:
Assigning IDs
Generating a secure token
Sending the manager approval email
/**
* GoogleFlows: Free PTO Approval System (Clean + Reliable)
*
* ✅ Installable onFormSubmit trigger
* ✅ Web App endpoint doGet for Approve/Deny
* ✅ Uses Script Properties (WEB_APP_URL, SPREADSHEET_ID)
* ✅ Uses openById (reliable for Web App + triggers)
*
* REQUIRED SCRIPT PROPERTIES:
* - SPREADSHEET_ID = 1qjpYUY7zR4nAR0IYjRA4aXEBoJMyNBLlGDur2ABlCCk
* - WEB_APP_URL = https://script.google.com/macros/s/AKfycbyt4AEP-makp0MN4L0GcZ0c-A_bpLiLs5soIAa50qG09KJXKKmmOgwx1Nw8FlhbKKgn/exec
*
* DEPLOYMENT (Web App):
* - Execute as: Me
* - Who has access: Anyone within domain (internal) OR Anyone (external approvers)
*
* TRIGGER:
* - Create an installable trigger for onFormSubmit (From spreadsheet -> On form submit)
*/
// =====================
// CONFIG
// =====================
const CFG_ = {
REQUESTS_SHEET_NAME: "Requests",
MANAGER_EMAIL_FALLBACK: "mirving@supereasycrm.com",
// Required columns on the form responses sheet (the sheet that receives submissions)
SUBMIT_REQUIRED_COLS: ["Request ID", "Status", "Token", "Employee Name", "Start Date", "End Date"],
// Required columns on the Requests sheet (approval datastore)
REQUESTS_REQUIRED_COLS: ["Request ID", "Token", "Status", "Decision By", "Decision Timestamp"],
// Optional columns (if present, we use them)
OPT_MANAGER_EMAIL_COL: "Manager Email",
OPT_EMPLOYEE_EMAIL_COL: "Email Address",
OPT_EMPLOYEE_NAME_COL: "Employee Name"
};
// =====================
// SCRIPT PROPERTIES HELPERS
// =====================
function props_() {
return PropertiesService.getScriptProperties();
}
function mustGetProp_(key) {
const val = props_().getProperty(key);
if (!val) throw new Error(`Missing Script Property: ${key}`);
return String(val).trim();
}
function getWebAppUrl_() {
return mustGetProp_("WEB_APP_URL").replace(/\/+$/, "");
}
function getSpreadsheetId_() {
return mustGetProp_("SPREADSHEET_ID");
}
// =====================
// SHEET HELPERS
// =====================
function getSpreadsheet_() {
return SpreadsheetApp.openById(getSpreadsheetId_());
}
function getHeaderMap_(sheet) {
const lastCol = sheet.getLastColumn();
if (lastCol < 1) throw new Error("Sheet has no columns.");
const headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0].map(h => String(h || "").trim());
const map = {};
headers.forEach((h, idx) => {
if (h) map[h] = idx + 1; // 1-based col
});
return { headers, map, lastCol };
}
function requireCols_(sheet, requiredCols) {
const { map } = getHeaderMap_(sheet);
const missing = requiredCols.filter(n => !map[n]);
if (missing.length) throw new Error(`Missing required column(s): ${missing.join(", ")}`);
return map;
}
function getRowObject_(sheet, row) {
const { headers, lastCol } = getHeaderMap_(sheet);
const values = sheet.getRange(row, 1, 1, lastCol).getValues()[0];
const obj = {};
headers.forEach((h, i) => {
if (h) obj[h] = values[i];
});
return obj;
}
// =====================
// URL HELPERS
// =====================
function makeActionUrl_(action, requestId, token) {
const base = getWebAppUrl_();
return `${base}?action=${encodeURIComponent(action)}&id=${encodeURIComponent(requestId)}&token=${encodeURIComponent(token)}`;
}
// =====================
// HTML / STRING SAFETY
// =====================
function escapeHtml_(s) {
return String(s || "")
.replace(/&/g, "&")
.replace(//g, ">")
.replace(/"/g, """)
.replace(/'/g, "'");
}
function safeStr_(v) {
return v == null ? "" : String(v);
}
// =====================
// MAIN: FORM SUBMIT HANDLER
// =====================
/**
* Installable trigger required:
* Triggers -> Add Trigger -> onFormSubmit -> From spreadsheet -> On form submit
*/
function onFormSubmit(e) {
if (!e || !e.range) throw new Error("onFormSubmit called without event object. Use an installable trigger.");
const submitSheet = e.range.getSheet();
const row = e.range.getRow();
// Ensure columns exist on the submission sheet
const colMap = requireCols_(submitSheet, CFG_.SUBMIT_REQUIRED_COLS);
// Generate IDs
const requestId = Utilities.getUuid();
const token = Utilities.getUuid();
// Write back to the same row
submitSheet.getRange(row, colMap["Request ID"]).setValue(requestId);
submitSheet.getRange(row, colMap["Status"]).setValue("Pending");
submitSheet.getRange(row, colMap["Token"]).setValue(token);
// Read row as an object for email template
const r = getRowObject_(submitSheet, row);
const approveUrl = makeActionUrl_("approve", requestId, token);
const denyUrl = makeActionUrl_("deny", requestId, token);
const employeeName = safeStr_(r["Employee Name"]);
const startDate = safeStr_(r["Start Date"]);
const endDate = safeStr_(r["End Date"]);
// Manager email (optional form field)
const submitHeaderMap = getHeaderMap_(submitSheet).map;
const managerEmailCol = submitHeaderMap[CFG_.OPT_MANAGER_EMAIL_COL];
const managerEmail = managerEmailCol
? safeStr_(submitSheet.getRange(row, managerEmailCol).getValue()).trim() || CFG_.MANAGER_EMAIL_FALLBACK
: CFG_.MANAGER_EMAIL_FALLBACK;
const html = `
You have a new PTO request from ${escapeHtml_(employeeName)}.
Dates: ${escapeHtml_(startDate)} to ${escapeHtml_(endDate)}
<a href="${approveUrl}"
style="display:inline-block;background:#16a34a;color:#fff;padding:10px 14px;border-radius:6px;text-decoration:none;">
Approve
</a>
<a href="${denyUrl}"
style="display:inline-block;background:#dc2626;color:#fff;padding:10px 14px;border-radius:6px;text-decoration:none;">
Deny
</a>
If the buttons don’t work, copy/paste this link:
${escapeHtml_(approveUrl)}
`;
GmailApp.sendEmail(
managerEmail,
"ACTION REQUIRED: PTO Request Approval",
"Please use an HTML-compatible email client.",
{ htmlBody: html }
);
}
Step 5: Create the Approval Endpoint
Add this below the existing code. This processes approve and deny clicks.
// =====================
// ENDPOINT HELPERS
// =====================
function getRequestsSheet_() {
const ss = getSpreadsheet_();
const sheet = ss.getSheetByName(CFG_.REQUESTS_SHEET_NAME);
if (!sheet) throw new Error(`Sheet "${CFG_.REQUESTS_SHEET_NAME}" not found.`);
return sheet;
}
function findRequestRow_(sheet, id, token) {
const map = requireCols_(sheet, ["Request ID", "Token"]);
const lastRow = sheet.getLastRow();
if (lastRow < 2) return -1;
const idCol = map["Request ID"];
const tokenCol = map["Token"];
const ids = sheet.getRange(2, idCol, lastRow - 1, 1).getValues();
const tokens = sheet.getRange(2, tokenCol, lastRow - 1, 1).getValues();
for (let i = 0; i < ids.length; i++) {
if (String(ids[i][0]) === String(id) && String(tokens[i][0]) === String(token)) {
return i + 2; // actual row number
}
}
return -1;
}
// =====================
// WEB APP ENDPOINT
// =====================
function doGet(e) {
const action = (e && e.parameter && e.parameter.action) ? String(e.parameter.action) : "";
const id = (e && e.parameter && e.parameter.id) ? String(e.parameter.id) : "";
const token = (e && e.parameter && e.parameter.token) ? String(e.parameter.token) : "";
if (!action || !id || !token) {
return HtmlService.createHtmlOutput(
`<h3>Missing parameters</h3><p>This approval link is incomplete.</p>`
);
}
if (action !== "approve" && action !== "deny") {
return HtmlService.createHtmlOutput(
`<h3>Invalid action</h3><p>Action must be approve or deny.</p>`
);
}
const sheet = getRequestsSheet_();
const map = requireCols_(sheet, CFG_.REQUESTS_REQUIRED_COLS);
const row = findRequestRow_(sheet, id, token);
if (row === -1) {
return HtmlService.createHtmlOutput(`Error
Invalid or expired request token.
`);
}
const newStatus = action === "approve" ? "Approved" : "Denied";
// Prevent double-decision
const currentStatus = safeStr_(sheet.getRange(row, map["Status"]).getValue()).trim();
if (currentStatus && currentStatus !== "Pending") {
return HtmlService.createHtmlOutput(
`Already decided
This request is already ${escapeHtml_(currentStatus)}.
`
);
}
// Update
sheet.getRange(row, map["Status"]).setValue(newStatus);
sheet.getRange(row, map["Decision Timestamp"]).setValue(new Date());
// NOTE: If the Web App executes as "Me", this is you (owner), not necessarily clicker.
sheet.getRange(row, map["Decision By"]).setValue(safeStr_(Session.getActiveUser().getEmail()));
// Optional notify employee if columns exist
const headerMap = getHeaderMap_(sheet).map;
const emailCol = headerMap[CFG_.OPT_EMPLOYEE_EMAIL_COL];
const nameCol = headerMap[CFG_.OPT_EMPLOYEE_NAME_COL];
if (emailCol) {
const employeeEmail = safeStr_(sheet.getRange(row, emailCol).getValue()).trim();
if (employeeEmail) {
const employeeName = nameCol ? safeStr_(sheet.getRange(row, nameCol).getValue()) : "there";
GmailApp.sendEmail(
employeeEmail,
`PTO Update: Your request was ${newStatus}`,
`Hi ${employeeName},\n\nYour PTO request has been ${newStatus.toLowerCase()}.\n`
);
}
}
return HtmlService.createHtmlOutput(
`<h2>Request Successfully ${newStatus}</h2><p>The spreadsheet has been updated.</p>`
);
}
Step 6: Deploy the Script
Click Deploy → New deployment
Type: Web app
Execute as: Me
Who has access: Anyone
Click Deploy
Authorize permissions
Copy the Web App URL. Apps Script uses this automatically for approval links.
Step 7: Store the Web App URL as a Script Property (Required)
Apps Script does not reliably expose the deployed Web App URL inside triggers like onFormSubmit. To ensure approval links are generated correctly every time, the Web App URL needs to be stored once and referenced dynamically.
In Apps Script:
Click Project Settings
Scroll to Script properties
Click Add script property
Add the following:
Property: WEB_APP_URL
Value: Paste the Web App URL you copied during deployment. Make sure it ends in /exec.
Save the property.
This step prevents approval links from breaking when the automation runs from a form submission trigger.
Step 6.6: Store the Spreadsheet ID as a Script Property (Required)
Because Web Apps do not always have access to the active spreadsheet context, the spreadsheet ID must also be stored explicitly.
In the same Script properties section:
Click Add script property
Add the following:
Property: SPREADSHEET_ID
Value: The spreadsheet ID found in the Google Sheets URL between /d/ and /edit.
Save the property.
This allows the approval endpoint to reliably access the Requests sheet when processing approve or deny actions.
Step 7: Add the Form Submit Trigger
In Apps Script:
Click Triggers
Add a trigger:
Function: onFormSubmit
Event source: From spreadsheet
Event type: On form submit
Hit save and be sure to remove popup blockers for this site.
Step 7a: Quick Configuration Verification (Optional but Recommended)
Before testing the full flow, it’s a good idea to confirm that the required script properties are present.
Run the following function once from the Apps Script editor:
function quickCheck_() {
const props = PropertiesService.getScriptProperties();
Logger.log("WEB_APP_URL =", props.getProperty("WEB_APP_URL"));
Logger.log("SPREADSHEET_ID =", props.getProperty("SPREADSHEET_ID"));
}Step 8: Test the Full Flow
Submit a PTO request using the form.
You should see:
Status set to Pending
Manager receives an email
Approve or Deny updates the sheet
Employee receives a response
Here’s what the manager receives:
Once they approve the request. They’ll see this screen.
Finally, the spreadsheet will update the Status column accordingly.
And that’s it! You now have a fully functional, free PTO system that lives within your Google Workspace. Approval requests are sent to managers and employees requesting time off are kept in the loop every step of the way. Your favorite pseudo database, Google Sheets will house all the requests and statuses, allowing for easy export, should the need arise. Pair this with an intranet service like Google Sites and you'll have a powerful suite of HR tools at your disposal without spending any extra cash.
