If you’re anything like me, you’ve probably got a few Google Sheets tabs open right now. It’s an ubiquitous tool used by businesses of all stripes and sizes. The flexibility of Sheets means it can do just about anything: act as a basic CRM, track finances, manage projects, analyze business data, and more. Personally, I use Google Sheets to track my marketing budget, customer notes, blog topics, partner opportunities, and growth data.
But if you’ve spent as much time in Sheets as I have, you’ve probably also run up against a key limitation: the data in your Sheet is confined to Google Sheets, and it’s hard to connect that with data in your other business systems, like a database or CRM.
You might want to connect a Google Sheet storing a list of user-requested features to user data from your MySQL database, so you can get more context on the user behavior driving these requests. I’d like to be able to connect my customer notes stored in Sheets with that customer’s profile data in my CRM. Usually, it would take some data engineering, SQL and/or complex configuration in a 3rd-party connector to do this.
I want to show you how Internal can connect your Google Sheets data with other data sources in just a few seconds. In fact, Internal can help you do even more with Google Sheets — it transforms your Sheet into a database, letting you build internal tools and workflows that utilize and interact with your data, completely configurable by non-technical users.
Connecting to a Google Sheet
It’s super quick to add your Google Sheet as a data source in Internal. Simply log in through Google SSO and allow Internal to access your Sheets data. You’ll then see a list of workbooks in your Google Account that you can connect. Select the one you want and that’s it -- you’re done.
Creating a relationship between Sheets data and other data
One big benefit of using Internal is the ability to easily join your Sheets data with data from other data sources without writing any SQL. Here’s how to set it up:
Let’s use the earlier example of wanting to connect a list of user-requested features (in Google Sheets) to user data stored in a database. Go to customize the record view for your user data, and then click “Insert a new section” at the bottom. Click on “+ Relationship” to define how these two datasets will be joined together. In this case, we’ll choose the PostgresDB data source and the table “Users” to be connected to the workbook Roadmap Features and the sheet “User Requests”. Then choose the common field, shared between both datasets, that will join them. In this case, it is the user email. Hit “Add” and boom — whenever you view a User record, you’ll see any feature requests from that user pulled in from Google Sheets.
This simple connection can add a lot of context to what was previously just a list of features requested by users. Now you can see what type of user made the request -- are they a mobile user, a power user, a paid user, a brand-new user, or any other kind of attribute that exists in your data.
Build an internal tool that interacts with Sheets and database data
Not only can you join your data sets, you can also build internal tools on top of that data. In Internal, Spaces are areas where you can mix and match components like buttons, forms, and tables to create tools. Here’s an example:
Let’s say you’re an on-demand scooter company, and you want to build an internal tool to make it easier to manage scooter maintenance. You store data about your Scooters, Users, and Rides in a MySQL database. You also have an operations team that stores Maintenance Log information for each scooter in a Google Sheet.
Let’s check out a fully-configured Scooter Maintenance tool that utilizes and interacts with data from Google Sheets and other sources:
You can see your Google Sheets maintenance log data right alongside your Scooters database data. What’s really cool is that these table components interact with each other — clicking on a row in the Scooter table will filter down the Maintenance Log table to only show results for that particular scooter. This means it takes just a click to verify when the scooter was last maintained, and to see maintenance details for that scooter.
You can also use this tool to interact with and update the underlying connected data. You can select a scooter in the Scooter table and click the “Deactivate” button at the top. This would change the scooter’s “Status” attribute in your database to “In Maintenance”, so that no more riders can request that particular scooter. Another button at the top lets you add a new Maintenance Log entry in your Google Sheet. Selecting a scooter and Clicking “Add Log” will open up a form allowing you to create a new log entry for that scooter.
This tool took just minutes to create — with zero code or SQL written. And it’s just as easy to connect other business apps, like Salesforce or Hubspot, so you can create tools with CRM data as well. Want to see how? Come talk to us. Or simply sign up for a free 14-day trial below.