Google Sheets is an excellent tool for collaboratively managing all types of data. However, there are a few downsides, especially when working at a large scale. One solution is to use a form to enter data into Google Sheets. Today, we’ll see exactly how you can do that with Budibase.
Why create a form to enter data into Google Sheets?
It’s a valid question. After all, it’s perfectly easy to add or modify rows in a spreadsheet. So why complicate things with a dedicated form to enter data into Google Sheets?
There are several situations where spreadsheets alone are insufficient. We won’t bother you with all the possibilities, but let’s take a few quick examples.
Firstly, let’s say you wanted to manage registrations for a meeting or event. You can’t just let each participant add their own details because then they could see all the other participants. Or they might accidentally do something they’re not supposed to, like deleting a row. A data entry form for Google Sheets helps prevent that.
Similarly, what if you had a team of administrators working on a lot of data at the same time? Real-time collaboration is cool and all, but it can also get very complicated very quickly. Data entry and update forms are essential for ensuring accuracy and preventing errors.
Why create Google Sheets forms with Budibase?
Another excellent question!
Undoubtedly, you already know that Google offers its own solutions here. So you could create a form on a separate sheet and use built-in functions, Google Apps Script code, or other script editors to add rows to your dataset. Alternatively, you could do something similar with Google Forms or other Google Drive integrations. You might also like our guide on data access control.
However, there are three big problems here:
- Google’s solutions only allow you to add data, not update existing rows.
- None of these options really look professional.
- You can achieve much better results with Budibase, with far less effort.
The last point might surprise you. It’s a very big claim, to be sure. So, how is this possible?
Our dedicated Google Sheets data connector
Budibase offers an unbeatable range of dedicated connectors for external data sources. And one of them happens to be for Google Sheets. All you have to do is sign in with your existing Google account, paste a link to your spreadsheet, and you’re good to go.
Then, you can use Budibase to automatically generate a fully functional CRUD (Create, Read, Update, Delete) application. We’ll go through all the details in a moment, but the entire process won’t take you more than a few minutes. You’ll also benefit from far superior functionality, customization, data validation, and flexibility than what you would get with a fully Google solution.
Simply connect a sheet and build your custom user interfaces and business processes. We’ll delve into some of Budibase’s more specific features that make it the ideal solution for using a form to enter data into Google Sheets later. But for now, let’s get practical.
Building Google Sheets forms: step by step
We have a few obvious prerequisites. We can safely assume that you already have a Google account, as well as a spreadsheet you want to manage. You’ll also need to sign up for Budibase, of course. You can use the same email address for that.
Once that’s done, here are the specific steps you can follow to start using a form to enter data into Google Sheets.
1. Create a new application and select Google Sheets as the data source
Unsurprisingly, our first step is to start a new application project. So once you’re signed in to Budibase, click on “Create a New Application” and choose “Start From Scratch”. At this point, you’ll be prompted to choose a name and URL.
Click “Create Application” when you’re ready. Then, you’ll see a modal window with a list of all available data sources.
Select Google Sheets. No surprises here.
2. Set up your data connection
Once your application is created, the next step is to connect with Google to link your accounts. You’ll then be prompted to enter the URL of the Google Sheets file you want to connect to.
Next, we need to choose which individual sheets from the file we want to extract and display in Budibase.
Immediately, we can edit our data in a spreadsheet-like interface in the Budibase backend. We can also modify our sheet’s schema, including adding JavaScript formula variables or relationships with other tables, as well as creating custom queries to manipulate our data.
3. Automatically generate CRUD screens
Next, we want to start building our forms.
Specifically, we want a form to add new rows to our spreadsheet and another to update existing rows. For the second type of form, we’ll also need to provide an interface for users to find the right entries before they can modify them.
Budibase offers you a few different options here. On one hand, you can create your own interfaces from scratch if that’s what you want to do. We offer a wide range of built-in components, making it easy to build professional applications with minimal technical skills.
For our needs today, we’ll let our automatically generated CRUD screens do most of the work.
Go to the “Design” tab at the top of the Budibase builder and click “Add Screen” on the left. You’ll then have the choice to create a blank screen or use automatically generated screens.
Choose the second option and click “Continue”. You’ll then need to select the data source for which you want to automatically generate the screens.
Keep in mind that we only have one sheet in our connected spreadsheet, but if you had more, you would choose between them.
4. Create a data entry form for Google Sheets
Our automatically generated user interface also includes a form in the sidebar for creating new rows. But Budibase also gives us the option to build something more advanced.
We’ll end up giving different types of users access to different forms, so we want our data entry form to be on a separate screen.
Start by adding a new blank screen.
You’ll then be prompted to designate an “URL slug”.
5. Assign access levels
One of the main reasons you would want to create a dedicated form to enter data into Google Sheets is to tightly control how different types of users interact with your data.
Let’s say we wanted some colleagues to enter data into our spreadsheet, but we only want the most experienced team members to be able to view and modify the entries.
We can easily achieve this using the role-based access control built into Budibase.
We’ll use two access roles to show how this works. Basic users will be able to access and use our data entry form. Admin users will have full access to that as well as our tabular view, with the update form.
By default, all screens are assigned to the basic role unless we choose a different one.
We’ll use the access dropdown to set our tabular screen to “Admin”.
6. Customize your design and UX
Now we have fully functional forms to enter data into Google Sheets or update existing entries. If we wanted to, we could publish it as is. But we want to make a few design changes to our application to enhance the user experience.
We’ll start with the application navigation.
Under “Configure Links”, we can update the text displayed in our navigation bar for each of our pages, as well as the minimum role required for it to be visible to users.
Next, we’ll update the “Create New” button on our tabular screen to open a sidebar with a prebuilt form. We can configure this using the button settings within the “Table Block” component on that screen, adding a “Navigate To” action.
Similarly, we’ll configure the “Save” button on our form screen to return to the tabular UI.
Finally, we’ll make a few changes to the appearance of our application.
First, we’ll set our theme to “Lighter”.
Finally, we’ll set the button colors to green, just to give a little visual nod to Google Sheets.
7. Publish your application
Finally, when you’re satisfied with your application, it’s time to make it public. As always, with Budibase, you have several different options for deploying and hosting your applications. On one hand, you can deploy to Budibase Cloud, with just one click, and we take care of everything.
Or, if you prefer, you can deploy to your own infrastructure with Kubernetes, Docker, Docker Compose, Digital Ocean, and many more.
This may not seem like the top priority when using a form to enter data into Google Sheets, but there are countless situations where how you host your tools plays a crucial role.
For example, you might want to introduce additional security levels, monitoring, or other measures. Or you might simply have your own infrastructure and want to use it. Check out our page on forms to learn more about how Budibase empowers teams to build advanced forms on existing databases.
You can use it as a standalone web app or embed it into an existing webpage or application using an iframe.
The smart solution for using a form to enter data into Google Sheets
Budibase is the fast and easy way to create all kinds of custom solutions. Our intuitive low-code tool is the perfect solution for internal tools, client-facing applications, dashboards, and much more.
The vast majority of applications that businesses use are simple interfaces on top of a data source. We’re on a mission to create the world’s largest open-source tool ecosystem. Budibase is the smart solution for building low-code custom solutions for a range of business problems.
Sign up for free today to start creating your own amazing low-code solutions. Or check out our library of free and deployable app templates to see what else Budibase can do.
