Are you looking to build apps using your Google Sheets data? Businesses of all stripes and sizes use Google Sheets to do just about anything: act as a basic CRM, track finances, manage projects, analyze business data, and more. But there are limitations to using Google Sheets: you’re stuck with a spreadsheet form-factor, it doesn’t behave like a true database, and the data in your sheet is confined to Google Sheets, making it difficult to connect with other data sources. 

Internal provides a fast and code-free way to build functional and secure business apps with Google Sheets. You can create any dashboard or front-end that you want using pre-built components. It transforms your sheet into a database, allowing you to build any internal tool or workflow on top of your Sheets data. And Internal also gives you the power to combine your Google Sheets data with data coming from databases like PostgreSQL or business apps like Salesforce - so you can build truly powerful tools that work across all your systems. 

How to get data from Google Sheets

Getting data from Google Sheets into Internal is super simple and takes just a few seconds. 

1. In Internal, access the Company Settings in the left-hand navigation and then click on the Data & Functions tab. 
2. Click on the button to “Add Data Source”.
3. Click on the Google Sheets icon and then the “Continue with Google” button. You’ll be asked to authenticate through Google - sign into the account that has the Google Sheets you want to work with. 
4. Choose the Google Sheets that you want to connect to.
5. (Optional) Choose the tabs within each workbook that you want to automatically add as a new Space. Internal will automatically generate a Space with a data viewer (table) so you can see your data without any additional work. 

How to convert your Google Sheet into a database

The great thing about using Internal is that once you connect your Google Sheet, you can start using it just like a database. Internal will automatically generate CRUD (create, read, update, and delete) functions for you to interact with your data, just like any other database. However, since Google Sheets is not a true database, there are a few things to look out for when you’re using a Sheet in Internal:

1. Internal will add a new field named “_row_number_” that delineates which row that particular data record resides in. Since there may not necessarily be a field serving as a unique identifier for each record, this field acts as that unique id for your Google Sheets data. 
2. Ensure the first row of your Google Sheet is a header that contains the column names for your data. The first row should not contain data values. 
3. Ensure that all column names in the header are unique (no duplicates). 
4. There should be no blank values in the header, every column with a value has a column name in the first row.

Following these steps allows you to transform your Google Sheet into a database that you can build on top of. 

How to create business apps with Google Sheets

Once you’ve got your Google Sheets data connected and set up, creating apps with your spreadsheet data is easy. Internal’s visual app builder lets you create internal tools without code, simply by dragging and dropping prebuilt components and configuring them. 

Here’s how to get started building apps on Google Sheets:

1. Click on + Create New Space in the left sidebar. This space will house the internal tool you’re building with Google Sheets. 
2. Click the Components icon in the top-left to view the 25+ available components to create your tool. Drag-and-drop your desired components into your blank space. 
3. If you want to display data from your Google Sheet, you can use a Table component, a Card List component, or a Detail component. Select the name of your Google Sheet as your data source, and then choose the “List” function (for table and card lists) or the “Get” function (for detail component). You can then configure how your data is displayed by hiding, rearranging, removing and adding fields (columns). Click on a field to bring up options to add icons, pillboxes or links in your data. 
4. If you want to add, edit, or delete data in your Google Sheet, you can use a Form component, a Pop-up Form, or a Button. Select the name of your Google Sheet as your data source, and then choose either the “Insert”, “Update” or “Delete” function - depending on what you want this component to do. Click on a field to bring up options for how you want the field’s value to be determined - setting a custom value, leaving it for the user to input, or even pre-filling it with data from another component. 
5. There’s a lot of other components and functionality to explore within Internal, including Images, Charts, File Uploaders, JSON Viewer, Tag Selectors, and more. Check out our documentation to learn about all that you can do. 

‍
Check out our tutorial below for a step-by-step guide on building apps with Google Sheets.

‍

How to create a Google Sheets dashboard

Want to create a dashboard using your Google Sheets data? With Internal, dashboards can be created as easily as any other internal tool or business app. Often, you’ll want to make use of the Chart and Stat component in Internal so your dashboard can display data in a visual and appealing way. 

Graphing Google Sheets data with the Chart component

The Chart component lets you visualize data in either a bar chart, line graph, or pie chart. Drag a chart component into your Space. Then configure your component by choosing the Google Sheets data source and then the “List” function for the sheet whose data you want to chart. 

For bar charts and line graphs, you can choose how you want your data to be grouped on the x-axis (by value or by time-period) and how you want y-axis values determined (count, sum, or aggregated value). You can also graph multiple lines or bars on your chart. 

You can create dynamic charts by using filters to determine which data should be shown in your chart, and binding that to a component like a table. In the below example, we’ve created a chart that updates whenever a new user is selected in a table. 

Summarizing Google Sheets data with the Stat component

Stat components act as a counter to summarize important data at a glance. You can apply conditional styling to alert users when a number is lower or higher than expected.

Drag a stat component into your Space. Then configure your component by choosing the Google Sheets data source and then the “List” function for the sheet whose data you want to chart. 

Within the Stat Details, you can specify how you want your stat to be calculated: by count of records, sum or pre-aggregated value (value is used as-is). Filters can be used to narrow down your data. For example, you can choose to only show data within the last 7 days, or only records that are in “pending” status. You can also bind your filter to another component, so that your stat component updates whenever a user selects a row in a table, for example. 

How to connect Google Sheets with databases

Another powerful thing Internal can do is to combine your Google Sheets data with data from other sources, like your databases. In the below example, we have maintenance logs which are stored in a Google Sheet, and scooter data that comes from a database. 

We’re using a Card List component to display the data from our Google Sheet, and a Table to show the scooter data. A filter has been placed on the card list so that it only displays the maintenance logs for the scooter selected in the table (the Scooter ID is bound to the Scooter Table -> Selected Row -> ID). Users just need to click on a scooter in the table to see the maintenance log records of that scooter.

In this way, your internal tools become way more useful and powerful if they can work with data across multiple data sources. 

Want to check it out for yourself? Sign up for a free trial below, or come talk to us.