# ERP with Google Workspace and Google Appscripts with code samples.
Overview
An ERP (Enterprise Resource Planning) system built on Google Workspace can leverage the suite’s core apps—Sheets, Docs, Drive, Gmail, Calendar—and automate workflows with Google Apps Script. Below are common ERP modules and sample Apps Script snippets that illustrate how to connect them.
1. Inventory Management (Sheets + Drive)
Key idea: Store inventory data in a Google Sheet; use Apps Script to update stock levels when a purchase order is approved.
/**
* Decrease inventory when a PO is approved.
* Triggered from a Google Form submission or a custom menu.
*/
function processPurchaseOrder(e) {
const poSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('PurchaseOrders');
const invSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Inventory');
// Assume the form sends: PO_ID, ITEM_ID, QUANTITY
const poId = e.values[0];
const itemId = e.values[1];
const qty = Number(e.values[2]);
// Find the inventory row for the item
const invData = invSheet.getDataRange().getValues();
for (let i = 1; i < invData.length; i++) {
if (invData[i][0] === itemId) { // Column A = Item ID
const currentStock = Number(invData[i][2]); // Column C = Stock Qty
invSheet.getRange(i + 1, 3).setValue(currentStock - qty);
break;
}
}
// Mark PO as processed
const poRow = e.range.getRow();
poSheet.getRange(poRow, 5).setValue('Processed'); // Column E = Status
}Deploy: Attach processPurchaseOrder to a Form submit trigger or a custom menu item in the PO sheet.
2. Sales Order Entry (Forms + Sheets + Gmail)
Key idea: Capture sales orders via Google Form, store them in a Sheet, and automatically email an order confirmation.
function onSalesFormSubmit(e) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const orders = ss.getSheetByName('SalesOrders');
const row = e.range.getRow();
// Pull data from the form submission
const [orderId, clientEmail, product, qty] = e.values;
// Add a timestamp and status
orders.getRange(row, 6).setValue(new Date()); // Column F = Received
orders.getRange(row, 7).setValue('Pending'); // Column G = Status
// Build email body
const body = `
Hi,
Thank you for your order #${orderId}. Here are the details:
• Product: ${product}
• Quantity: ${qty}
We will notify you once the order is processed.
Best,
Sales Team
`;
// Send confirmation
GmailApp.sendEmail(clientEmail, `Order Confirmation #${orderId}`, body);
}Deploy: Set a Form submit trigger for onSalesFormSubmit.
3. Expense Reporting (Docs + Sheets + Drive)
Key idea: Employees fill a Google Form; a script generates a formatted expense report in Docs and saves it to a shared Drive folder.
function generateExpenseReport(e) {
const templateId = '1A2bC3dEfGhIjKlMnOpQrStUvWxYz'; // Docs template file ID
const folderId = '0B1cD2eF3gHiJkLmNoPqRsTuVwXyZ'; // Shared Drive folder
const [empName, date, category, amount, description] = e.values;
// Make a copy of the template
const copy = DriveApp.getFileById(templateId).makeCopy(`${empName} – Expense ${date}`);
const doc = DocumentApp.openById(copy.getId());
// Replace placeholders in the template
const body = doc.getBody();
body.replaceText('{{EMPLOYEE}}', empName);
body.replaceText('{{DATE}}', date);
body.replaceText('{{CATEGORY}}', category);
body.replaceText('{{AMOUNT}}', `$${Number(amount).toFixed(2)}`);
body.replaceText('{{DESCRIPTION}}', description);
doc.saveAndClose();
// Move to the shared folder
DriveApp.getFolderById(folderId).addFile(copy);
DriveApp.getRootFolder().removeFile(copy);
}Deploy: Attach to the Form submit trigger of the expense form.
4. Project Management Dashboard (Sheets + Calendar)
Key idea: Sync project milestones from a Sheet to Google Calendar events.
function syncMilestonesToCalendar() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const projSheet = ss.getSheetByName('Projects');
const cal = CalendarApp.getCalendarById('your-team@yourdomain.com');
const data = projSheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
const [projId, milestone, dueDate, status] = data[i];
if (status !== 'Scheduled') continue; // Only schedule once
// Create calendar event
cal.createAllDayEvent(`${projId} – ${milestone}`, new Date(dueDate));
// Mark as scheduled
projSheet.getRange(i + 1, 4).setValue('Scheduled');
}
}Deploy: Run manually or set a time‑driven trigger (e.g., daily).
5. Approval Workflow (Gmail + Sheets + Apps Script)
Key idea: Send an approval request email with “Approve” / “Reject” links that update a Sheet when clicked.
function sendApprovalRequest(row) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Approvals');
const [reqId, requester, amount, status] = sheet.getRange(row, 1, 1, 4).getValues()[0];
const approveUrl = ScriptApp.getService().getUrl() + `?action=approve&id=${reqId}`;
const rejectUrl = ScriptApp.getService().getUrl() + `?action=reject&id=${reqId}`;
const html = `
<p>Approval needed for request #${reqId}:</p>
<ul>
<li>Requester: ${requester}</li>
<li>Amount: $${amount}</li>
</ul>
<p>
<a href="${approveUrl}">Approve</a> |
<a href="${rejectUrl}">Reject</a>
</p>
`;
GmailApp.sendEmail('manager@yourdomain.com',
`Approval Request #${reqId}`,
'',
{htmlBody: html});
}
/**
* Web app entry point for approval links.
*/
function doGet(e) {
const id = e.parameter.id;
const action = e.parameter.action;
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Approvals');
const rows = sheet.getDataRange().getValues();
for (let i = 1; i < rows.length; i++) {
if (rows[i][0] == id) {
sheet.getRange(i + 1, 4).setValue(action === 'approve' ? 'Approved' : 'Rejected');
break;
}
}
return HtmlService.createHtmlOutput('Your response has been recorded.');
}Deploy: Publish the script as a Web app (execute as “Me”, accessible to “Anyone”). Call sendApprovalRequest from a button or trigger.
6. Consolidated Reporting (Data Studio / Looker Studio)
While Apps Script handles data capture, you can connect the Sheets directly to Looker Studio for visual dashboards. No code is required beyond sharing the Sheet with the reporting service.
Quick-start checklist
| Step | Action |
|---|---|
| 1 | Create master Sheets for Inventory, SalesOrders, Approvals, Projects. |
| 2 | Build Google Forms for PO entry, sales orders, and expenses. |
| 3 | Add Apps Script projects (one per module) and set appropriate triggers (form submit, time‑driven, web‑app). |
| 4 | Design Docs template for expense reports and store its file ID. |
| 5 | Set up a shared Calendar for project milestones. |
| 6 | Publish the approval web app and test the email links. |
| 7 | Connect Sheets to Looker Studio for dashboards. |
These snippets give a functional skeleton; you can extend them with validation, error handling, and role‑based access as needed for a production‑grade ERP built entirely on Google Workspace.
