This guide is for people that want to elevate their data collection and aggregation within Google Workspace (formerly G Suite) by using the popular Google Sheet third party tool – Sheetgo.
This guide will layout when you would be most likely to use Sheetgo and the differences between Sheetgo and other native Google Workspace solutions for data transfer. It will help determine what you might need to change with your current processes and workflows to make sure that Sheetgo helps your data workflows, not hinders them.
- Aggregating data
- The problem with current solutions
- Why Sheetgo?
- Example Solutions using Sheetgo
- Project Management
- Creating your first workflow
Sheetgo is a cloud-based automation tool that sits on top of Google Workspace (formerly G Suite), and it allows users to transfer data between Google Sheets. Before Sheetgo was ever created, the CEO, Yannick Rault van der Vaart and the CTO Gomes da Silva were working on an ERP system that was based purely on Google Sheets. Sound familiar? Maybe your organisation is ‘hacking’ Google Sheets to run an inventory system, or you’re using Google Sheets to manage your company financials.
Google Sheets are great because they are extremely easy to use and require no active maintenance. Normally, ERP’s and CRM’s have to have a team of IT professionals behind them to operate and support the system, whereas a single Google Sheet can be operated by anyone with a small degree of technological competence.
One drawback to sheets is that they are one-dimensional. A single sheet can have a select number of editors, viewers and commenters. What happens when you don’t want the Spanish sales team to see the German Sales statistics? Well, we have to split the Sales sheet into two! But now how do we aggregate them to get over-arching sales metrics? Well, we have to import the data from the German Sales Sheet and the Spanish Sales Sheet into a Master Sales Sheet! We’re effectively creating a Google Sheet pseudo-database. This is exactly where Sheetgo shines.
Transferring data between spreadsheets is the act of importing data from one spreadsheet to another. This functionality is pivotal for many organisations and especially for those with larger data pipelines. Below is an example of how a data connection may look like.
You can do this using the ImportRange function on Google Sheets, but that comes with its own caveats which are discussed below. Sheetgo aims to make data pipelining easier, cleaner and more robust.
The problem with current solutions
Google Sheets only has one way to import data from another Sheet, this is called the ImportRange function. This function acts as a one-way connector between two Google Sheets. In the example above, there would be two ImportRange queries in Sheet C; one connecting to Sheet A, and the other connecting to Sheet B. ImportRange is fantastic in its simplicity, but that becomes its main problem.
Here are some of its limitations:
- Just as the name suggests, you can only import a range. This means that if your dataset grows, you will need to adjust the range to import every time.
- There can only be 50 simultaneous ImportRange functions within a spreadsheet. This is a hard limit.
- ImportRange updates around every 30 seconds. This means It’s impossible to get ‘end of month’ aggregations of your data. It’s always live.
- It’s difficult to troubleshoot. As there’s no indication when a connection breaks, you have to manually crawl through your dataset to find errors.
- It doesn’t transfer formatting. This is a big one especially if the source sheets encode data in formatting. For example, if your inventory field turns blue when you are expecting a delivery of that item, ImportRange will not transfer the colour.
- You can only connect to Google Sheet files. This means if you upload .xlsx, .csv’s or odf, you won’t be able to import from those.
- And lastly, there are no in-built filtering options. This means that any sort of data manipulation will have to be completed manually.
If you think your spreadsheet-connecting needs are fulfilled despite these caveats, you can take a read of this fantastic blog by Ben Collins, a community known Sheets guru, in which he goes into detail about how you can use the ImportRange function.
If you are connecting spreadsheets often and the reliability of the datalinks is becoming more of a concern and perhaps a managerial nightmare, or if you’re connecting more than 10 sheets at a time, Cobry would wholeheartedly recommend a data transfer solution, such as Sheetgo.
As one of the founders of Sheetgo said: “Sheetgo cuts down on the hours employees use to trifle through data and makes everyone’s lives easier.” So if you don’t want to read further, I’ll give you a TL;DR: If you want to cut down on managing your spreadsheets and allow for more control between the connections, use Sheetgo.
- As mentioned previously, the ImportRange function can only import a range, whereas, with Sheetgo, you can import an entire tab or workbook. You have much larger security with the data that you’re connecting.
- As your organisations and workflows grow, you may find yourself hitting the hard cap of 50 spreadsheets connected. Sheetgo has no such restrictions.
- If you have Excel files or CSV/TSV’s to connect, it’s game over for ImportRange. Sheetgo can connect to multiple tabular data file formats.
More data control
- A big feature of Sheetgo is that you can not only pull data from multiple spreadsheets into one, you can actually distribute data from a single spreadsheet into many.
- Sheetgo also offers filtering, so if it’s only a subset of data that you want to be connected, you can do it natively within the tool.
- Preservation of formatting. If your formatting (colour, font…) is important to preserve during transfer, fear not – it’s simply a tick box within Sheetgo.
Greater data visibility
A great feature of Sheetgo is its user interface. Managing data pipelines that span lots of spreadsheets becomes a job in itself using ImportRange. With Sheetgo, you can manage and view all of your connections at a glance:
And if you’re still not convinced, take a look at this blog by Sheetgo themselves on the differences between their solution and ImportRange.
Example Solutions using Sheetgo
If you’ve read down this far, you’ve probably already got an idea as to how Sheetgo can help you and your organisation. That being said, if you’re still looking for inspiration here are some example workflows and solutions that may get your spreadsheeting neurons firing. Note that all of the examples discussed below are taken from the amazing Sheetgo template library.
Inventory Management using Sheetgo
You can monitor stock levels with an inventory management system based on Google Sheets. For example, let’s say a restaurant has 4 locations – each with their own inventory. Two Google Forms would be created for each location – “Check-in” and “Check-out” – each with their own spreadsheet. We can connect these two forms into a location-specific sheet, and then tie all the locations together to get a managerial-style sheet with information from all 4 locations. Here’s what the data connector may look like:
You can track your financial data across multiple sites or services using Sheetgo too. Imagine you’re a manufacturer that has to make a bill of materials for every item you produce, and you need to know the manufacturing cost associated with every item. Simple. A Google Sheet is made for every item, and Sheetgo comes in to aggregate all parts to an overall bill-of-materials. This way, we can track the finances on a part-by-part basis, and get sophisticated analytics and reports using the aggregated Google Sheet while maintaining the separation of the sheets for the individual parts.
Project Management using Sheetgo
I think you know where we’re going with this one now. A sheet for each project, each containing it’s own information relating to this specific project. But oh wait – we don’t have a view of all of our projects. How are we going to track project completion dates per region if we don’t have data that shows us projects across all projects? Sheetgo to the rescue! With Sheetgo we can aggregate data across all projects to give us an overarching view. Easy!
Creating your first Sheetgo workflow
Now that you know what Sheetgo is and the different organisational problems it can solve, let’s dive into how you can go and create your first workflow.
- Head on over to app.sheetgo.com
- Sign in with your Google account.
Perfect! Now we need to create a new workflow.
Let’s use a template. We’re going to use the inventory management one, but they are all very similar.
Congratulations, you’ve made your first connection! Let’s dissect the screen here for a second. You can do multiple things in this view.
On the left, you can edit your spreadsheets natively within Sheetgo. If you avert your attention to the right, you can view and edit the current connections between spreadsheets; this is very useful when visualising a large amount of connected data.
And that’s it! You’ve made your first workflow!
From here you can add data to the “Check-out” and “Check-in” spreadsheets, and whenever you run the workflow it’ll be aggregated into the “Inventory Management” spreadsheet. You can add scheduled runs which will automatically do this for you, and you can also add more spreadsheets if your inventory needs are greater than this workflow.
As trusted Sheetgo partners, Cobry has data experts on hand to help you with your data systems and processes. We can also aid in deploying Sheetgo and making it work for your organisation. Ready to get started with Sheetgo? Get in touch.