Does a department in your company lend items internally? Perhaps you have a library in your school that lends books to students. Or an IT department that lends computer equipment to employees. Maybe you run a hire business where you rent items to customers, such as bicycles, tools, or clothing?
Whatever type of business, institute, or organization you work in, tracking lent items is an essential task — but it’s often done manually or using cumbersome IT systems.
Keeping tabs on exactly what stock you currently have in your inventory, what has been lent out (and to whom) helps you to calculate if you have enough equipment for your business, to recognize if certain items are going unused, and to help you trace missing or overdue items that have not been returned.
You could purchase specialist lending software, or ask your IT team to build you a tracking system, but you can actually do it yourself using the tool you already know and use: spreadsheets. Here I’ll show you how to create a reliable inventory lending system in Google Sheets, using an automated template.
Why choose Google Sheets to manage inventory lending?
- Flexible: Google Sheets lets you build a tailor-made inventory lending system that you can adapt yourself. There’s no coding or expensive software required.
- Accessible: Because they’re online, Google Sheets files can be accessed by everyone in the team anywhere, at any time.
- Compatible with everything: If you use other software packages in your company, you can easily integrate them with Google Sheets. This enables you to merge data from other departments.
- Easy to use: There’s no training required as you’re probably already familiar with spreadsheets. The system is simple to maintain without specialist IT support.
Looking for a basic inventory lending template instead?
If you work alone with a small list of items, try our basic inventory lending template. It doesn’t have any Sheetgo connections, but it provides a simple way to check items in and out in one single file. Get a copy here.
How to get started
Sheetgo’s spreadsheet-based workflow template is a pre-built system for inventory lending that can be used by businesses or schools renting or loaning equipment of any kind. The template gives managers a reliable way to track inventory levels while providing library or shop staff with a simple way to check items in and out.
Get an at-a-glance overview of items currently on loan in the dashboard:
What’s a spreadsheet-based workflow template?
A standard spreadsheet template is usually one single file containing pre-built tables, charts, and formulas but a Sheetgo workflow template is an interconnected system. It consists of several Google Sheets that exchange data between them via Sheetgo connections. This allows different colleagues and departments to share data without working in the same spreadsheet.
A spreadsheet-based workflow template gives you:
- Organization and data privacy: Each file in the template is dedicated to one single task. Team members enter information into their own spreadsheet and that data flows directly into the manager’s inventory lending dashboard.
- Scalability: Google Sheets has a powerful sharing function but a lot of people working in the same file can lead to large and heavy spreadsheets, as well as mistakes. Storing data in separate, but connected, spreadsheets ensures that the system runs smoothly. This template provides a starting point: if you want to add more data inputs to the template in the future, you can simply connect more files to the inventory lending workflow.
- Integration: With a spreadsheet-based workflow you can merge data from different software, as well as other teams and departments. In a couple of clicks, you can connect your inventory workflow to your sales workflow to generate reports for management.
Quickly see who has borrowed which items in the inventory matrix tab:
What you get with this template:
When you install the Sheetgo inventory lending template, three separate Google Sheets files are saved to your Google Drive:
- A check-in spreadsheet where the person(s) responsible records which items have been returned.
- A check-out spreadsheet where the person(s) responsible registers the items which have been lent, rented or borrowed.
- An inventory lending master sheet for the manager with automated dashboards providing an overview of current stock levels. Data flows into this file from the check-in and check-out spreadsheets via Sheetgo connections.
How to get the template:
Click on the blue button below to install the inventory lending template:
Workflow template to record borrowed or returned items and track your inventory.
- Login to Sheetgo with your Google, Microsoft, or Dropbox account.
- Click Install template.
- Wait a few moments while Sheetgo creates copies of the files and connects them.
The inventory lending master sheet opens inside Sheetgo.
To open the Check-in or Check-out spreadsheets, click the Workflow icon in the sidebar and double click on either file.
How to use the inventory lending workflow template
First of all, familiarize yourself with each of the spreadsheets so you can see the format of the template. You will notice that each file contains sample data, which you or your colleagues can simply delete or overwrite.
Step 1: Fill out the items database
In the Inventory lending master sheet, go to the tab Item Database (Input). Replace the sample data with your inventory list. Enter all the items you have available for lending.
Here I’ve entered a list of sports equipment to lend at my summer camp.
Step 2: Run the workflow
This template contains pre-built Sheetgo connections that transfer data back and forth between the files. Now that you’ve entered your inventory list into the master sheet, it’s time to transfer that data to the input sheets (Check-in and Check-out).
Click on Workflow on the menu bar on the right-hand side of the screen. You will see blue arrows between the files: these are your connections.
Click Run in the left-hand corner of the screen. This will update the connections, sending fresh data to all the spreadsheets.
Let’s take a look.
If I open the Check-out Inventory file and go to the Check-out tab I can see that the dropdown menu now contains the list of sports equipment from my master sheet.
Step 3: Clear the sample data
The Check-in and Check-out spreadsheets also contain sample data. Once you’ve seen how the template works, you can delete this information.
Step 4: Share the input spreadsheets
Share the Check-in and Check-out spreadsheets with the colleagues responsible for entering and managing that data.
Do this by simply clicking the Share button.
Step 5: Start using the system
In the Check-out spreadsheet, your colleague(s) can start checking items out to borrowers. They should enter a check-out date, the name of the borrower, select the item name from the dropdown menu, and then enter the number of items borrowed.
Update the connections by clicking Run.
When items are returned, they should be logged in the Check-in spreadsheet.
You will see that the name of the borrower and the item appears automatically in the Name of borrower and item name column in the Check-in tab.
Step 6: Automate the inventory lending template
Once your inventory lending template is set up and your colleagues are entering data into their spreadsheets on a regular basis, you can automate the workflow.
Automation ensures you’ve always got the latest inventory data in the management dashboards, without having to open a spreadsheet or open Sheetgo.
Open Sheetgo, then open the Inventory Lending workflow from your workflows list on the left.
Click Automate next to the Run button and select how often you want the data to be updated: from once an hour to once a month.
Optional: extend your inventory lending system
Once your inventory management workflow is up and running, you can expand it by adding more source files and Sheetgo connections.
Because the template is based in Google Sheets, it’s pretty flexible. You can add data inputs from Google Forms, connect to a barcode scanner, or link to workflows from other departments, such as sales or finance.
To add more connections, open the Inventory Lending workflow in Sheetgo and click Connect on the floating menu bar. Get tutorials and guidance in the Sheetgo support center.
If you’re looking for a more complex inventory management system in Google Sheets or you’d like us to build a custom system for you, book a call with our automation experts.
Share with your manager
If you lend items out but you’re not responsible for inventory management as a whole, share this article with your manager so they can set up the workflow.