How I Cut a 4-Hour Procurement Process to 3 Minutes Using Google Apps Script

In Feb 2025, our office admin, Meera, walked into my room with a stack of papers that looked like a mini-mountain.“Dilip… these are the procurement requests for this month. Can you please help? The approvals are stuck again.”Every month, it was the sam…


This content originally appeared on Level Up Coding - Medium and was authored by Dilip Kashyap

In Feb 2025, our office admin, Meera, walked into my room with a stack of papers that looked like a mini-mountain.

“Dilip… these are the procurement requests for this month. Can you please help? The approvals are stuck again.”

Every month, it was the same drama:
purchase requisitions → approvals → vendor selection → drafting purchase orders → sending emails → storing PDFs → status tracking.

Nothing was standardised.
Everything was done manually.
And the worst part?

A single procurement cycle took almost 4 hours.

Four. Hours.

One day, while watching Meera manually compare vendor prices across spreadsheets, I realized:
This process is screaming for automation.

That night, I built a quick Google Apps Script prototype — and the next morning, we tested the first automated flow.

The result?

🔥 A full procurement cycle completed in under 3 minutes.
🔥 Zero manual vendor matching.
🔥 Automatic PO PDF generation.
🔥 Email + Drive workflow fully automated.
🔥 Real-time dashboard for pending & approved orders.

This is the exact workflow I am going to show you today.

🚀 What We’re Building (End-to-End Procurement Automation)

A fully automated procurement system using Google Sheets + Google Forms + Apps Script + Gmail + Drive:

Features

  • Purchase Request Form
  • Multi-level Approval (Manager → Finance → Director)
  • Auto Vendor Matching from Database
  • PO PDF Generation
  • Email Notifications (Request/Approval/Rejection)
  • Auto-Organized Drive Folders
  • Dashboard showing status + metrics

By the end, you’ll have a system where:

➡️ Employees submit requests
➡️ Approvers get email links to approve/reject
➡️ Script picks vendors
➡️ Automatically generates a Purchase Order PDF
➡️ Stores everything neatly in Drive
➡️ Updates dashboard in real time

Let’s start.

🧩 Step 1 — Why Automate Procurement?

Manual procurement suffers from:

❌ Long approval cycles
❌ Data inconsistencies
❌ Missing communication trail
❌ Errors in vendor selection
❌ Delays in order processing

Automation fixes all of these:

✓ Standardized inputs
✓ No more missing approvals
✓ Rule-based vendor matching
✓ Auto-generated POs
✓ Clean audit trail
✓ Real-time dashboard

Pro tip:
Even small organizations benefit massively from procurement automation. You don’t need an SAP or Oracle ERP — Apps Script is enough.

📝 Step 2 — Setup Requirements

1. Google Form (Purchase Request)

Fields to include:

  • Employee Name
  • Department
  • Item / Service
  • Quantity
  • Estimated Cost
  • Urgency
  • Preferred Vendor (optional)
  • Attachment upload (quotations etc.)

👉 This populates a Google Sheet called Purchase_Requests.

2. Vendor Database Sheet

A second sheet called Vendors with columns:

  • Vendor Name
  • Category
  • Item
  • Average Price
  • Email
  • Rating

This is used for automatic vendor matching.

3. Purchase Orders Folder in Drive

Create a Drive folder named Purchase Orders.
Get its folder ID.

{{PO_NUMBER}}
{{DATE}}
{{VENDOR_NAME}}
{{ITEM}}
{{QUANTITY}}
{{PRICE}}
{{REQUESTED_BY}}

Apps Script will replace these dynamically and export a PDF.

⚙️ Step 3 — The Apps Script Workflow

Open your Purchase Request sheet → Extensions → Apps Script.

We’ll build the workflow in 5 modules:

  1. Auto-trigger when a form is submitted
  2. Multi-level approval logic
  3. Vendor matching
  4. Generate PO PDF
  5. Email + Drive automation

Let’s go step-by-step.

📌 Step 4 — Form Submission Trigger

This will add a status and generate a unique Request ID.

function onFormSubmit(e) {
  const sheet = e.source.getSheetByName("Purchase_Requests");
  const row = e.range.getRow();
  
  const id = "REQ-" + new Date().getTime();
  sheet.getRange(row, sheet.getLastColumn() - 1).setValue(id);  
  sheet.getRange(row, sheet.getLastColumn()).setValue("Pending Manager Approval");

  sendApprovalEmail(row);
}

Common mistake:
Don’t hardcode column numbers — always use lastColumn to avoid breaking when new fields are added.

📧 Step 5 — Send Email to Manager for Approval

Manager gets a link with Approve / Reject buttons.

function sendApprovalEmail(row) {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Purchase_Requests");
  const data = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];

  const approveUrl = ScriptApp.getService().getUrl() + "?id=" + data[10] + "&action=approve&level=manager";
  const rejectUrl  = ScriptApp.getService().getUrl() + "?id=" + data[10] + "&action=reject&level=manager";

  const emailBody = `
    <p>A new purchase request requires your approval.</p>
    <p><b>Item:</b> ${data[2]}</p>
    <p><b>Cost:</b> ${data[4]}</p>
    <br>
    <a href="${approveUrl}">Approve</a> | 
    <a href="${rejectUrl}">Reject</a>
  `;

  GmailApp.sendEmail("manager@company.com", "New Purchase Request", "", {htmlBody: emailBody});
}

Pro tip:
Use the Web App version of Apps Script to process approval links.

⚡ Step 6 — Approval Processing Logic

Your Web App handler:

function doGet(e) {
  const { id, action, level } = e.parameter;
  const sheet = SpreadsheetApp.getActive().getSheetByName("Purchase_Requests");

  const data = sheet.getDataRange().getValues();
  let rowIndex = data.findIndex(r => r[10] == id);

  if (rowIndex == -1) return HtmlService.createHtmlOutput("Request not found");

  if (action === "approve") {
    if (level === "manager") {
      sheet.getRange(rowIndex+1, sheet.getLastColumn()).setValue("Pending Finance Approval");
      sendFinanceApproval(id);
    } 
    else if (level === "finance") {
      sheet.getRange(rowIndex+1, sheet.getLastColumn()).setValue("Pending Director Approval");
      sendDirectorApproval(id);
    } 
    else if (level === "director") {
      sheet.getRange(rowIndex+1, sheet.getLastColumn()).setValue("Approved");
      autoSelectVendor(id);
    }
  } 
  else {
    sheet.getRange(rowIndex+1, sheet.getLastColumn()).setValue("Rejected by " + level);
  }

  return HtmlService.createHtmlOutput("Action submitted successfully.");
}

🔍 Step 7 — Automatic Vendor Matching

This matches based on item category.

function autoSelectVendor(id) {
  const ss = SpreadsheetApp.getActive();
  const reqSheet = ss.getSheetByName("Purchase_Requests");
  const vendorSheet = ss.getSheetByName("Vendors");

  const reqData = reqSheet.getDataRange().getValues();
  const rowIndex = reqData.findIndex(r => r[10] == id);
  const item = reqData[rowIndex][2];

  const vendorData = vendorSheet.getDataRange().getValues();
  const matched = vendorData.filter(v => v[2] == item);
  const selected = matched.sort((a,b) => a[3] - b[3])[0];

  reqSheet.getRange(rowIndex+1, 12).setValue(selected[0]);   // Vendor Name
  reqSheet.getRange(rowIndex+1, 13).setValue(selected[3]);   // Price

  generatePO(id);
}

Common mistake:
Vendor matching conditions must be clear. Add fallback vendor if no exact match.

📄 Step 8 — Generate Purchase Order PDF

function generatePO(id) {
  const ss = SpreadsheetApp.getActive();
  const reqSheet = ss.getSheetByName("Purchase_Requests");
  const templateId = "DOC_TEMPLATE_ID";
  const folderId = "PO_FOLDER_ID";
  
  const data = reqSheet.getDataRange().getValues();
  const rowIndex = data.findIndex(r => r[10] == id);

  const doc = DriveApp.getFileById(templateId).makeCopy("PO_" + id);
  const docId = doc.getId();
  const body = DocumentApp.openById(docId).getBody();

  body.replaceText("{{PO_NUMBER}}", id);
  body.replaceText("{{ITEM}}", data[rowIndex][2]);
  body.replaceText("{{QUANTITY}}", data[rowIndex][3]);
  body.replaceText("{{PRICE}}", data[rowIndex][13]);
  body.replaceText("{{VENDOR_NAME}}", data[rowIndex][12]);
  body.replaceText("{{REQUESTED_BY}}", data[rowIndex][0]);

  DocumentApp.openById(docId).saveAndClose();

  const pdf = DriveApp.getFileById(docId).getAs("application/pdf");
  DriveApp.getFolderById(folderId).createFile(pdf);

  sendPOEmail(id);
}

📬 Step 9 — Email PO to Vendor + Store in Drive

function sendPOEmail(id) {
  const ss = SpreadsheetApp.getActive();
  const reqSheet = ss.getSheetByName("Purchase_Requests");
  const vendorSheet = ss.getSheetByName("Vendors");

  const data = reqSheet.getDataRange().getValues();
  const rowIndex = data.findIndex(r => r[10] == id);

  const vendorName = data[rowIndex][12];
  const vendorEmail = vendorSheet.getRange( vendorSheet.createTextFinder(vendorName).findNext().getRow(), 5).getValue();

  GmailApp.sendEmail(
    vendorEmail,
    "Purchase Order " + id,
    "Dear Vendor, please find attached the purchase order.",
    {
      attachments: [DriveApp.getFilesByName("PO_" + id).next().getAs("application/pdf")]
    }
  );
}

📊 Step 10 — Dashboard

Use another sheet called Dashboard.

Include:

  • Pending approvals
  • Requests by department
  • Total spend
  • Vendor distribution
  • Average approval time

Use:

COUNTIF(), COUNTIFS(), QUERY(), Pivot Tables, and Charts.

Screenshot idea:
📸 Dashboard showing pie charts, approval bars, and metrics.

💡 What This Workflow Changed For Me

After deploying this system:

  • Meera stopped carrying paper files
  • Managers approved requests from mobile
  • Finance had real-time cost visibility
  • Vendors received POs instantly
  • The audit trail became crystal clear

Most importantly —

⚡ Procurement time dropped from 4 hours to 3 minutes.

It completely changed the efficiency of our internal operations.

🚀 3 More Automations You Can Build Next

1. Budget Monitoring Automation

Auto-track department spend vs. monthly limits.

2. Delivery Tracking Workflow

Vendor → delivery → GRN → invoice → payment cycle automation.

3. Vendor Performance Dashboard

Track fulfillment time, price fluctuations, service quality.

👋 Final Note

If you want your operations team (or yourself) to get more done with less stress…

Automate one workflow at a time.
Start small.
Scale smart.

👉 Follow me for more Google Apps Script + AI automation tutorials.

Let me know if you want a downloadable template (Sheets + Doc + Script).

Ready to unlock the FULL power of Google Workspace? 💡
📘 Your shortcut to automation mastery is here!
“Google Apps Script: A Beginner’s Guide” isn’t just an ebook — it’s your secret weapon.

How I Cut a 4-Hour Procurement Process to 3 Minutes Using Google Apps Script was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.


This content originally appeared on Level Up Coding - Medium and was authored by Dilip Kashyap


Print Share Comment Cite Upload Translate Updates
APA

Dilip Kashyap | Sciencx (2025-11-25T14:51:49+00:00) How I Cut a 4-Hour Procurement Process to 3 Minutes Using Google Apps Script. Retrieved from https://www.scien.cx/2025/11/25/how-i-cut-a-4-hour-procurement-process-to-3-minutes-using-google-apps-script/

MLA
" » How I Cut a 4-Hour Procurement Process to 3 Minutes Using Google Apps Script." Dilip Kashyap | Sciencx - Tuesday November 25, 2025, https://www.scien.cx/2025/11/25/how-i-cut-a-4-hour-procurement-process-to-3-minutes-using-google-apps-script/
HARVARD
Dilip Kashyap | Sciencx Tuesday November 25, 2025 » How I Cut a 4-Hour Procurement Process to 3 Minutes Using Google Apps Script., viewed ,<https://www.scien.cx/2025/11/25/how-i-cut-a-4-hour-procurement-process-to-3-minutes-using-google-apps-script/>
VANCOUVER
Dilip Kashyap | Sciencx - » How I Cut a 4-Hour Procurement Process to 3 Minutes Using Google Apps Script. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2025/11/25/how-i-cut-a-4-hour-procurement-process-to-3-minutes-using-google-apps-script/
CHICAGO
" » How I Cut a 4-Hour Procurement Process to 3 Minutes Using Google Apps Script." Dilip Kashyap | Sciencx - Accessed . https://www.scien.cx/2025/11/25/how-i-cut-a-4-hour-procurement-process-to-3-minutes-using-google-apps-script/
IEEE
" » How I Cut a 4-Hour Procurement Process to 3 Minutes Using Google Apps Script." Dilip Kashyap | Sciencx [Online]. Available: https://www.scien.cx/2025/11/25/how-i-cut-a-4-hour-procurement-process-to-3-minutes-using-google-apps-script/. [Accessed: ]
rf:citation
» How I Cut a 4-Hour Procurement Process to 3 Minutes Using Google Apps Script | Dilip Kashyap | Sciencx | https://www.scien.cx/2025/11/25/how-i-cut-a-4-hour-procurement-process-to-3-minutes-using-google-apps-script/ |

Please log in to upload a file.



There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.

Related Posts