Incorrect orders, shortages, and disruptions to the supply chain are all results of poor communication and inefficient process issues. If you own a small business, you probably know that such mismanagement can lead to excessive spending and stockpiling (and a negative impact on your cash flow).
Tools for purchase control and procurement can be expensive and overly complicated for small businesses. But instead of using a loose system for managing purchase orders and procurement, consider Google Sheets. It’s a free and user-friendly tool that makes your own purchase order templates and management system.
In this article, we’ll show you how to create a purchase order template and we will also provide you with a free template for purchase orders.
Check Out: Super Easy-to-Learn Udemy Courses to Master Google Sheets – at a major discount!
Table of Contents
What Is a Purchase Order Document?
Simply put: A purchase order document is the buyer’s contract when purchasing goods from the seller.
When a company places an order with its suppliers or vendors, the purchasing department issues a commercial source document known as a purchase order. The document contains information about the items to be purchased, including:
- Types
- Quantities
- Prices
- Date of Purchase
- Payment terms
Benefits of Using a Google Sheets Purchase Order Template
Spreadsheet programs like Google Sheets are designed to create a purchase order template and modify it to your requirements. There are numerous benefits, including:
Accessibility
As Google Sheets is cloud-based, you can access purchase order data from any location – at any time – as long as you have internet access. In addition, you won’t have to spend money on unnecessary and expensive software.
Flexibility
Using Google Sheets, you can easily create a system that is specific to your business. You can modify it and expand it over time – without IT support.
Ease of Use
Using Google Sheets doesn’t require extensive training. It’s fairly simple to understand, even for beginners. If there’s a topic that’s a little difficult to understand, great resources and documentation are readily available. In addition, Google Sheets is also resistant to change and simple to maintain.
Compatibility
Companies today use various softwares to help with sales, advertisement, project management, etc. By using Google Sheets (and various Plugin options available), it’s simple to integrate your data with/from various software/applications. Google Sheets makes it a breeze to import data from various parts of the business and keep it centralized.
Google Sheets is also OS agnostic, meaning that it’s supported by all major operating systems such as iOS, Windows, and Linux.
A Simple Guide to Making a Purchase Order Template in Google Sheets
You can create a purchase order template in Google Sheets for business by following these steps:
Step 1: Open Google Sheets
Search on your browser and open Google Sheets from your device.
Step 2: Choose a PO Template
Select Purchase Order Template from the Google Sheets gallery.
NOTE: Be sure to give a name to this PO template, so it’s easier to locate on your device.
Step 3: Create Categories
From the bottom-left corner of your screen, click on the “+” to create new sheets and rename them, according to your needs. You can always change these names at any point by right-clicking and selecting “rename.”
We recommend adding the following sheets:
- POS
- ITEMS
- VENDORS
- PO LOG
- SETTINGS
- PRINT PO (which will contain the final purchase order that you can print and/or share digitally).
A work order template in Google Sheets should look something like this:
Step 4: Populate Each Sheet
PO LOGS Sheet
In the PO LOGS sheet, store all the relevant details about your purchase order: PO number, vendor name, issue date, shipment date, total purchase order, etc.
VENDOR Sheet
Your VENDOR sheet should include vendor details: company name, street address, city, state, ZIP code, phone number, email address, tax rate, etc.
ITEMS Sheet
In your ITEMS sheet, add descriptions about your items such as item number, description, unit price, etc.
POS Sheet
The POS is the most important of all because this is where you will compile all the necessary data required for the purchase order. This sheet should have the following rows:
STEP 5: Data Validation
To simplify data entry, add data validation rules to the following rows:
VENDORS
Add a data validation rule to this row to be able to select a vendor from the vendors’ sheet.
Step 1: Go to Data and then click on Data Validation
Step 2: Click Add rule from the data validation menu and select the cell where you want to add the rule. In this case, cell B6 in the POS sheet. Select the criteria as drop-down (from a range).
Step 3: Select the range as VENDORS!A2:A4 (since this is the range with the vendors’ names).
This is how your drop-down should look once added:
SHIP DATE
Add a data validation rule to this row to ensure that the value entered is a valid date.
Step 1: Go to Data validation again and add another rule.
Step 2: Select the cell range, and choose the criteria as Is valid date.
ITEMS
Add a data validation rule to this row to be able to select an item from the items sheet.
Step 1: Go to Data validation and add another rule.
Step 2: Select the cell where you want to add the drop-down, in this case, cell B13. Select the criteria as Drop-down (from a range).
Step 3: Select the range as ITEMS!A2:A4 since this is the range with the item names.
Here’s a look at the drop-down once added:
Step 6: Add an Items Table to the POS Sheet
Add an items table to the POS sheet with the following headers:
Step 7: Create Buttons
Now, you’ll create buttons and assign functions from Google Script to perform certain actions.
- Under Insert, select drawing, choose the bevel icon and draw a rectangle.
- Click on it to enter text and label the button as “Create PO”. After labeling the button, save and close the drawing menu.
- The button will now appear on the relevant sheet and you can place it wherever you want.
Follow the same steps to create two more buttons called Clear and Add Item. The Clear button will clear any previously entered items in the items table, and the Add Item button will add a new item to this table.
Step 8: Assign Functions to Buttons
In order to assign functions to these buttons, you’ll have to add a Google Apps script to the sheet.
From the Extension option in the menu bar, click on Apps script. Rename your project as “Purchase Order”.
In the new project, create a file and name it purchase-order.gs. Then, copy and paste the code (written below) into this file and hit the save option from the script menu bar.
The following code contains three functions:
- addItem: This function adds the item you have selected in the ITEMS dropdown, and the quantity entered in the QUANTITY row below it, to the items table.
- createPO: This function creates a purchase order with the items in the items table and the data that you have added to the VENDOR, INVOICE #, SHIP DATE, SHIP VIA, TERMS, and SHIP @ HANDLING rows.
- clearCells: This function clears all previously entered items from the items table.
```
function
function getActiveSheet(sheetName) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
return ss.getSheetByName(sheetName);
}
function findValue(sheet, value, columnIndex) {
var lastRow = sheet.getLastRow();
for (var i = 2; i <= lastRow; i++) {
if (value == sheet.getRange(i, columnIndex).getValue()) {
return i;
}
}
return null;
}
function addItem() {
var poSheet = getActiveSheet("POS");
var itemSheet = getActiveSheet("ITEMS");
var lastRowPO = poSheet.getLastRow() + 1;
var part = poSheet.getRange('B13').getValue();
var quantity = poSheet.getRange('B14').getValue();
var itemRow = findValue(itemSheet, part, 1);
var description = itemSheet.getRange(itemRow, 2).getValue();
var unitCost = itemSheet.getRange(itemRow, 3).getValue();
poSheet.getRange(lastRowPO, 1).setValue(part);
poSheet.getRange(lastRowPO, 2).setValue(description);
poSheet.getRange(lastRowPO, 3).setValue(quantity);
poSheet.getRange(lastRowPO, 4).setValue(unitCost).setNumberFormat("$#,###.00");
}
function clearCells() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("POS");
sheet.getRange("A17:D100").clearContent();
}
function createPO() {
var poSheet = getActiveSheet("POS");
var vendorSheet = getActiveSheet("VENDORS");
var settingSheet = getActiveSheet("SETTINGS");
var printSheet = getActiveSheet("PRINT PO");
var name = poSheet.getRange(6, 2).getValue();
var invoiceNumber = poSheet.getRange(7, 2).getValue();
var shipDate = poSheet.getRange(8, 2).getValue();
var shipVia = poSheet.getRange(9, 2).getValue();
var terms = poSheet.getRange(10, 2).getValue();
var shipAndHandle = poSheet.getRange(11, 2).getValue();
var poNumber = settingSheet.getRange(1, 2).getValue();
var nextPONumber = poNumber + 1;
settingSheet.getRange(1, 2).setValue(nextPONumber);
var vendorRow = findValue(vendorSheet, name, 1);
var companyName = vendorSheet.getRange(vendorRow, 2).getValue();
var streetAddress = vendorSheet.getRange(vendorRow, 3).getValue();
var city = vendorSheet.getRange(vendorRow, 4).getValue();
var state = vendorSheet.getRange(vendorRow, 5).getValue();
var zip = vendorSheet.getRange(vendorRow, 6).getValue();
var phoneNumber = vendorSheet.getRange(vendorRow, 7).getValue();
var email = vendorSheet.getRange(vendorRow, 8).getValue();
var taxRate = vendorSheet.getRange(vendorRow, 9).getValue();
var currentDate = new Date();
var currentMonth = currentDate.getMonth() + 1;
var currentYear = currentDate.getFullYear();
var date = currentMonth.toString() + '/' + currentDate.getDate().toString() + '/' + currentYear.toString();
var lastRowPrint = printSheet.getLastRow();
var rowCountToDelete = 0;
for (var i = 27; i <= lastRowPrint; i++) {
if (printSheet.getRange(i, 6).getValue() != 'Subtotal') {
rowCountToDelete++;
} else {
break;
}
}
}
```
From the new script file that you have created, copy the name of each function and assign that to its respective button. For example:
- The function AddItem should be assigned to the Add Item button
- The function createPo should be assigned to the Create PO button
- The function clearCells should be assigned to the Clear button
Because Google Sheets only accepts an exact match to run a script, it’s recommended to copy the function name from the script itself.
In order to assign a function/script to a button, bring your cursor to the button and click on the three dots on the top-left corner of the button. From there, select Assign a script, and paste the function name that you copied from the script.
Note: When you click on a button for the first time, it will take you through some security measures. These safety measures will require you to provide the script with access to your Google Account. Once you have done this, you will be able to use the script without requiring authentication again.
Step 9: Hit the Create PO Button
Once you’ve added all the vendor items, hit the Create PO button and it should create the Google Sheets Purchase order form for you!
This means that your simple purchase order template in Google Sheets is now modified to create hassle-free purchase order documents.
What Will Your Google Sheets Order Form Look Like?
After you have entered all the relevant data and clicked on the Create PO button, this is how your Google Sheets order form should look:
Additional Resources
In addition to a purchase order template, your business may have other items that need to be tracked. For businesses small and large, our list of resources will help you set up complete tracking mechanisms in Google Sheets – for every important aspect of your business:
- Track Inventory of your Business
- Use our existing inventory and sales tracking template for additional convenience
Conclusion
A formal purchase control system can help prevent communication issues and other process inefficiencies – but it can feel overwhelming to design and implement one from scratch.
Whether you use our outlined steps or use our free purchase order template, it’s never been easier to create a purchase order in Google Sheets!
Related:
