A Google Sheets GUI can help you turn your spreadsheets into real apps.
Google Sheets is one of the most ubiquitous, widely used business tools around today. It allows you to get all the power of spreadsheets, databases, and scripting all at once.
But at the end of the day, it鈥檚 still a spreadsheet tool.
So you are limited to the grid design and unfiltered data entry. Users can add data in the wrong format. If you have multiple users, editing files can get confusing. There鈥檚 no user access control, advanced filtering, or search options.
In general, it feels like you need to adjust all your workflows to work around the limitations, instead of having an app that adjusts to your needs.
That鈥檚 where creating a Google Sheets GUI can come in handy.
With a Graphical User Interface (GUI, you can use Google Sheets as a flexible data source.
At the same time, end users interact with your app using your Google Sheets UI. Thus, you can control data entry, create custom views, add charts and metrics, and create different user levels.
This allows you to get the best of both worlds. A flexible data source , along with a powerful user-facing app.
Today, we鈥檒l show how you can use a free open-source no-code builder to create your Google Sheets GUI in just four steps. By the end, you鈥檒l know how you can create beautiful Google Sheets apps, get data, edit it, trigger automations, and much more.
Let鈥檚 get started!
How do I create a dashboard in Google Sheets?
You can use Google Sheets as a dashboard, by using custom styles, charts, and App Script to run functions. But it鈥檚 probably better and easier to use a free no-code builder such as 91制片厂 to create a custom Dashboard for Google sheets .
You can follow the tips from this tutorial to build a dashboard in addition to a Google Sheets GUI.
Join 300,000 teams running operations on 91制片厂
Can Google Sheets be used as a database?
Google Sheets is a great option for a free and simple database. With it, you can add data to your tables and use other apps to access it using the API.
Check out our guide to data entry automation.
What are we building?
Today, we鈥檙e building a two-screen app that will manage sheets called Contacts and Messages. Our Contacts screen will be a simple CRUD UI, enabling our internal users to manage the rows in this table in a controlled, streamlined manner.
They can access modal forms to either add new rows or update existing ones.
We鈥檝e built a similar screen for users to view incoming Messages.
However, rather than performing full CRUD operations, our modal form simply enables users to expand the message details, as well as submitting a reply.
When a user replies from the front-end of our Google Sheets GUI, an automation is triggered, sending this response to the original submitter, and updating the status attribute of the relevant row.
To learn more about how we can use a form to submit messages, check out our guide to building forms for Google Sheets data entry.
How to build a Google Sheets GUI in 4 steps
Let鈥檚 jump right in. If you haven鈥檛 already, sign up for a free 91制片厂 account to start building as many applications as you like.
Join 300,000 teams running operations on 91制片厂
The first thing we need to do is create a new 91制片厂 Workspace. This is a collection of data sources, end-user apps, and automation rules, which can all interact with one another. When we create a new Workspace, we鈥檙e first prompted to give it a name.
We鈥檒l simply call ours Google Sheets GUI.
1. Setting up our data layer
Once we鈥檝e created our Workspace, we鈥檙e offered several options for connection to our first data source.
91制片厂 offers dedicated connectors for a range of RDBMSs, NoSQL tools, and APIs, alongside our built-in low-code database.
Today, however, we鈥檙e choosing Google Sheets.
When we choose this option, we鈥檒l be prompted to complete Google鈥檚 SSO flow.
Simply follow the steps to sign in. Then, we鈥檒l be shown the following dialog, where we can input the URL for our target spreadsheet.
We鈥檙e then asked which of the sheets within our spreadsheet we鈥檇 like to Fetch, making them usable within 91制片厂. As we said earlier, our spreadsheet has two tables, called Contacts and Messages.
We鈥檙e fetching both.
Here鈥檚 how these will look in 91制片厂鈥檚 Data section.
Already, we can edit our data, add rows, or update the schema of our table, using 91制片厂鈥檚 spreadsheet-like interface.
At this point, we could start generating screens. However, we鈥檙e going to make a few minor adjustments first that will save us time later.
Specifically, we鈥檙e going to make a few small changes to some of our columns, that will influence the kinds of components that are used to edit them when we come to generate our CRUD UIs.
Firstly, we鈥檙e going to change the Type of the Message attribute on our Messages table to Long-Form Text.
Similarly, there are several columns across our two tables where we鈥檇 like to offer users a choice of defined options. These are Method and Status on the Messages table, and Status on the Contacts table.
For each of these, we鈥檒l update the Type to Single Select, as well as inputting our available options based on the values in our dummy data.
We鈥檒l make sure to repeat this for all three columns.
And that鈥檚 our data model ready to go.
2. Generating CRUD screens
Next, we can head to the Apps section of our workspace to start building the front-end for our Google Sheets GUI.
In 91制片厂, Apps are end-user tools that can access any of the data sources within their parent Workspace.
We鈥檒l then be prompted to give our new app a name. We鈥檒l simply call ours Google Sheets GUI for demo purposes.
We鈥檒l then be brought to the 91制片厂 builder, where a new app has been created with a blank screen.
At this point, we could start building our Google Sheets GUI from scratch using 91制片厂鈥檚 library of built-in components. However, today we鈥檙e going to rely on 91制片厂鈥檚 autogenerated layouts instead, so we can simply delete this screen.
When we do this, we鈥檙e offered several options for how we鈥檇 like to build our first screen, including starting from scratch or using a pre-built layout.
We鈥檙e going to choose the Table option. This will generate working CRUD UIs for any connected data tables we choose.
When asked, we鈥檒l select both of our Google Sheets.
We鈥檙e then given a choice of formats for hosting our CREATE and UPDATE forms. We鈥檒l pick the option to wrap these in Modals.
Here鈥檚 how our Contacts screen looks in the 91制片厂 builder straight out of the box.
As we saw earlier, we can access forms to CREATE or UPDATE rows, using screen modals.
At this point, our Google Sheets GUI offers full CRUD functionality. However, we鈥檙e going to make a few key changes to each of our screens, both to improve user experiences and to alter the actions we can take on each data table.
3. Modifying autogenerated screens
To make things easier, we鈥檒l take each screen in turn.
Contacts
On our Contacts screen, we want to retain full CRUD functionality for end-users. So, the changes we鈥檙e going to make will be fairly minimal.
We鈥檒l start by deselecting the id column on our table, since this is an autogenerated field in our spreadsheet, and users likely won鈥檛 need to access it.
Besides this, all we want to do is make a few minor visual changes to our two forms.
We鈥檒l start by selecting our Create Row form and updating its Title setting to something a little more descriptive.
Again, we鈥檒l also deselect the id field, because this is autogenerated in our Google Sheet anyway.
The names of the columns in our spreadsheet are fully capitalized. We can use the Label and Placeholder settings alongside each form field to make these more human-readable.
Lastly, under Styles, we鈥檒l set our Button Position to Top.
We鈥檒l also apply similar changes to our Edit Row form.
Messages
The changes that we鈥檒l make to the Messages screen of our Google Sheets GUI will be more extensive.
Rather than offering full CRUD operations, we want end-users to be able to read and respond to incoming messages, as well as updating their status to reflect this. We鈥檒l handle the logic for this in an automation in the following section.
For now, though, we simply want to get our UI ready.
We鈥檙e going to start by deleting the Create Row button and the corresponding form modal.
We鈥檒l then deselect the id column on our table, as we did with our other screen.
Next, we鈥檙e going to make some more wholesale changes to our remaining form. We鈥檒l start by making the same changes as we did for the forms in our previous screens.
We鈥檒l then change the Type from Update to View, making our form read-only.
We鈥檒l also set each of our fields to Disabled to give more of a visual cue that these can鈥檛 be edited.
Finally, we鈥檒l drag our fields into a more logical order, and use the Columns setting to reduce their vertical real estate.
Finally, we need to add some UI components to enable users to submit their response.
We鈥檒l start by hitting the + icon to open the components menu, where we can search for Container.
This provides a flexible space for us to add additional components within.
The first component we鈥檒l nest inside our Container is a Button with its Text set to Reply.
We鈥檒l configure what this will do in the following section, after we鈥檝e created our automation rule.
Next, we鈥檒l add a Long Form Field beneath our button, setting its Field, Label, and Placeholder to Response.
Now, our users have somewhere to draft responses to incoming messages.
4. Adding automations
Lastly, we鈥檙e going to create an automation flow that sends the user鈥檚 response to the original submitter of the Message, as well as updating the Status field of the appropriate row to reflect this.
We鈥檒l start by heading to the Automations section of our 91制片厂 Workspace.
Automations are global within a Workspace, meaning that they can interact with any of our data sources or apps.
When we create a new Automation, we鈥檙e prompted to choose a name and a trigger. We鈥檙e going to call our new rule Respond, and select the User Action trigger.
This brings us to 91制片厂鈥檚 automation editor.
First, we need to configure our trigger with the appropriate inputs. Specifically, we鈥檙e going to need to pass two variables from the front-end of our Google Sheets GUI for this to work. These are an email address, the Response from our form field, and the id of the corresponding Messages row.
We鈥檒l add these as fields.
Next, we鈥檒l hit the + icon to add a follow-up action, choosing Send Email (SMTP).
We鈥檒l use {{ trigger.fields.email }} as our Send To field and {{ trigger.fields.response }} as our Message.
We can use static values for the other fields.
We can then run a test to confirm that this works.
When we do so, we鈥檒l see the correctly formatted email in our inbox.
Next, we鈥檒l add an Update Row action, pointed at the row in our Messages table with ID matching {{ trigger.fields.response }}.
We鈥檒l then hit Edit Fields to set STATUS to replied.
And, we鈥檒l run another test to confirm that this has worked.
Back in the Apps section, we now need to configure our Reply button to trigger this automation with the correct data.
We鈥檒l start by opening the On Click actions drawer.
Here, we鈥檒l add a Trigger Automation action and point it at our new automation rule.
We鈥檒l set our email to {{ [Edit row form block].Messages.CONTACT_EMAIL }}, response to {{ [Long Form Field].Value }}, and rowId to {{ [Edit row form block].Messages._id }}.
Now, when a user hits Reply, the appropriate data will be passed to our automation rule, firing an email and updating the appropriate row in our Sheet.
From a functional perspective, our Google Sheets GUI is ready to go. When you鈥檙e happy, you can hit Publish to push it live for end users.
Turn data into action with 91制片厂
91制片厂 is the open-source, low-code platform that empowers IT teams to turn data into action.
With leading support for external data sources, autogenerated UIs, powerful AI-driven automations, optional self-hosting, custom RBAC, free SSO, and more, there鈥檚 never been a better way to build professional internal tools.
Check out our features overview to learn more.