Connecting a web app built in Bubble to Google Sheets using no line of code
Get to know how your Bubble-made web application can fetch and manipulate data stored in a Google Spreadsheet
We in SheetDB.io constantly work on new integrations and features of our app in order to make it useful for as many people and businesses as possible. What SheetDB does is turning a Google Spreadsheet into a JSON API which then can work with different services, tools and programming languages.
I’ve recently touched the topic of the no-code application creating by building a simple app in Adalo and integrating it with Google Sheets. Now, the time has come to face Bubble.io - somewhat a similar service in terms of the promises they make concerning coding, although devoted to web applications and less template-ish. As usual, I’m going to enhance my app’s capabilities by teaming it up with SheetDB, which enables pulling data from Google Spreadsheets, as well as sending them there and manipulating them with the CRUD methods.
Creating an inventory list in Google Sheets
My idea for the app, that would showcase some possibilities of such a coalition, was a tool designed for the internal use of any kind of store which needs to keep track of items in their stock (so basically every). I wanted the application to have two features:
- searching through an inventory;
- adding new items to stock.
Concerning the former, I needed an already existing inventory list. Though short as it was, I’ve created a list of electronic devices to be sold in my hypothetical shop and typed it into a spreadsheet. Besides products’ names, separate columns are dedicated to their manufacturers, categories, prices and quantities available in stock. What’s for the latter, new items added to stock are to be saved in the same sheet.
Turning the spreadsheet into an API
In order to enable communication between Google Sheets and the app, I‘ve used SheetDB.io to create a JSON API out of my spreadsheet. The process is fast and simple, so no one should have a problem completing it ad hoc. All you have to do is give SheetDB the spreadsheet’s URL address and it will provide you with the API’s endpoint.
Often to unleash their full potential in terms of integrating them with third-party APIs, the no-code services, like Bubble, require providing slightly modified endpoints for different requests, depending on what you want to do with the data. As for SheetDB API, everything you might need to know is clearly laid out in its documentation.
Connecting Bubble to the external API
To be able to use a third-party API in Bubble, the first thing to do after starting a new project and figuring out a unique name for it (the creator won’t let you pick any name which has already been taken!), is installing a plugin called the API Connector. I’ve found it easily by searching through Bubble’s plugin portfolio under the Plugins tab.
Once the API Connector is installed, you can immediately add new APIs and set their API calls for different tasks. In my case, the first request that my app needed to run properly was a GET, since it should fetch the data from my spreadsheet and display them to users. I’ve named this API call Search. For the dropdowns: Use as and Data type - I didn’t change them from the default state of: Data and JSON.
Planing to place a search box above my inventory list, which would let users look for items by several properties, like their name, producer and category, I had to use one of the SheetDB’s features along with the standard API endpoint - the search_or in particular. With it you can check if the defined parameters are true for your spreadsheet and receive an array of rows matching any of them in return. Also, such a search should find matching items even when provided with the incomplete parameters’ values. For this purpose I’ve also introduced asterisks: *. All things considered, the endpoint for this API call ought to look as follows:
https://sheetdb.io/api/v1/YOUR_API_ID/search_or?COLUMN'S_NAME1=*[query]*&COLUMN'S_NAME2=*[query]*&...One could add as as many parameters as needed. I wished to have three columns taken into account, so my endpoint was:
https://sheetdb.io/api/v1/hn3wd4ekwo3v6/search_or?Product=*[query]*&Producer=*[query]*&Category=*[query]*Once you put query between a pair of square brackets, the API Connector creates a URL parameter with this query as a key, and no value defined. In order to initialize the call properly, type any value into the input and get rid of it afterwards. And unmark the Private option next to it! It is crucial for any results.
To find out more about the search feature, check SheetDB’s documentation or watch my YouTube video on this topic, titled: Using the search feature in SheetDB API to filter results from Google Sheets.
The second API call which I wanted my application to perform used the POST method. Here, the endpoint didn’t need any modifications, however I’ve switched Use as to Action and changed the Body type dropdown from JSON to Form-data. This way the parameters can be added to the call and each of their keys must correspond to a column in the spreadsheet. Values here serve only to initialize this API call, so they are more of a test-like nature and there’s no need to delete them once it’s done. When this call is initialized, those values should be already sent to the spreadsheet as a new row of data. Speaking of a test-like nature, this certainly cannot be said about the Private checkboxes next to every parameter which, again, have to be unticked!
Designing the app’s interface and workflow
After the plugin’s setup, it was time to place some elements into the app’s interface and with that, Bubble comes in handy, having many options to choose from. My project assumed having an input at the top, which would enable typing searched phrases and an element called the Repeating Group below, where the fetched records would be displayed. This element is somewhat like a table where the first cell’s setup is replicated in all the other ones. In order to have a list of items, I’ve set the number of columns to only 1, whereas the columns from my spreadsheet have been added one by one in the first row of the group - initially as text fields, then assigned to the corresponding columns’ values.
But to let those text fields know what I wanted them to display, firstly I had to set the Repeating Group’s type of content to my first API call - Search. From the Data source dropdown I’ve picked the option Get data from an external API and selected my call as the API provider. There’s also the (path) query input where one can insert dynamic data from which my input’s value had to be chosen.
As for the text boxes mentioned above, in their settings (double-click on the element) you can insert dynamic data in their body as well. The Current’s cell Search option has connected the box to the inventory list from the spreadsheet and the ‘s Product follow-up defined the precise column to be used there. The next box to the right had ‘s Producer ending and so on with all the spreadsheet’s columns.
The button moving users to a page, where adding new items would be possible, was an element finishing the current screen of my app. Under the Workflow tab on the left you can add different actions to buttons, one of which is redirecting to another page.
What I’ve done on the second page of the app, was simply placing as many inputs as I had columns in the spreadsheets and putting the Add a new item button below. Its workflow had to include two things:
- Posting values from the inputs to Google Sheets;
- Reseting the inputs after the first task.
Both of them located in the dropdown list meant to add new actions in the Workflow tab: first - under Plugins, the second - under Element Actions. With the posting action I just had to pick my second API call, conveniently called Posting, and then change each initially defined parameter’s value to dynamic data, specifically: each input’s value.
After all this, the app works correctly. It lets users search stock by three parameters, as well as it enables adding new items to the inventory. This was obviously just an example of how you can integrate Google Sheets with your application made in Bubble.io and use an online spreadsheet as a user-friendly, free of charge database. From this point, you can take it to higher levels and create much more complicated tools, because Bubble can be really powerful and when paired with SheetDB, it’s capable of even more!
Thank You!
If you’d like to see this whole process in action, checkout the video I’ve made for our YouTube channel. It’s called: Connect your Bubble-made web app to Google Sheets to fetch & manipulate data (NO CODE), and it’s a part of the NO CODE Series alongside with the video on integrating SheetDB with Adalo.
And if you have interest in this kind of topics, check out my other posts on SheetDB’s blog which revolves around our service or visit SheetDB’s YouTube channel where new tutorials about the app’s features are posted regularly. With any questions email us at support@sheetdb.io or reach out on Twitter @sheetdb_io. We’d love to hear your opinion on our work and/or ideas as to what else you’d like us to do with SheetDB, because your voice matters to us!
