We recently had a support request from a customer that wanted to do something very simple – take data in a spreadsheet, perform a lookup based on an email address and then output data from the other columns in the matching row onto the web page.
The initial request that came in was worded differently though – they requested that we import the data and then perform the look-up. The problem we saw was that the data would keep changing which meant that, in addition to the lookup function, we would also have to create an “import” function that was reusable and smart.
We proposed instead that they take the excel spreadsheet and import it into a Google Sheet. Then, we would do a direct lookup against that sheet.
This approach provided a couple of advantages:
- The data could be changed at any time by the customer simply by editing the sheet or completely deleting the contents and replacing it.
- It would cost less since we would not have to create a separate reusable smart import function.
The sole disadvantage was that the lookup would take more time – they would be scanning 25,000+ rows in the google sheet searching for the email address. Given the cost savings and the fact that most users would only be doing the lookup once or twice, the customer determined that the tradeoffs were acceptable.
Preparing The Google Sheet
The easiest way to connect to the Google Sheet was via a 3rd party service that specialized in turning sheets into an API. We chose the SHEET2API service for this project.
When you sign up for the service you will login with your Google Account which will allow you to use a Google Sheet.
Your Google Sheet needs to be formatted as just rows and columns with a header, like this:
Notice that the first row is a header row that is essentially labels for the columns.
Grant Sheet2API Access To The Sheet
Next, you need to SHARE your Google Sheet with Sheet2API:
- Navigate to your Google Sheet in your web browser.
- Click “Share” in the top right.
- In the “Add people and groups” input box, type [email protected].
- Un-tick “Notify people” and click “Share”.
- Finally, click “Share” at the top right again, then click “Copy link”.
That last step is important – the LINK that is created for the share will be used in the next step so make sure you keep a copy of it.
Create The API
To create the API for the sheet, use the CREATE SPREADSHEET API button in your Sheet2Api dashboard.
Next, copy and paste the URL from the GRANT operation (the prior step above) into the dialog box and click the CREATE button:
You should then be taken back to your list of API sheets where you’ll see a unique URL for your Sheet’s API. You should copy that URL since you’ll need it to link to Bubble in a bit:
Now that we have the API url, we can connect it to Bubble:
Setup API Link In Bubble
If you have not already done so, you should add the Bubble API Connector plugin.
Once that has been added, you’ll see a button Add Another API.
When you click that button, the following screen will be shown:
This screen can be a little confusing – not the best UI by bubble. This is how you fill out the fields:
- API NAME: Sheet2API (you can use any name here)
- Authentication: None or self-handled
Click the Add A Shared Header button and fill out the two fields as follows:
- Key: content-type
- Value: application/json
Next, we have to create a name for the actual function we’ll be calling to get data from the api. So, in the field labeled NAME that has the prefilled value API CALL, change the prefilled value to GetRowByEmail.
When all these items have been filled out, the screen should look something like this:
We’re not done yet. We have to expand and fill out other parameters for the GetRowByEmail function – so, click on the EXPAND link that’s to the right of the GETROWBYEMAIL function – see the image below where we show the location of the EXPAND link:
Now, fill out the fields as shown in this image:
Notice that the last KEY (’email”) has the PRIVATE option unchecked.
Then, click on the INITIALIZE CALL button. If everything is set up correctly, you should see a popup screen that look like this:
Just click the SAVE button – this will complete the set up of the API.
Using Your New API
You can now use your API call anywhere that Bubble allows calculations or lookups – for example as shown in this image:
For the sake of completeness, lets go ahead and finish up this article by briefly discussing how you can use this api call to lookup data in the sheet using just the email address.
We’ll need to setup the following elements on a new bubble page:
- An input box to allow the user to enter the email address
- A button to trigger the lookup
- A couple of TEXT elements (labels) to display the values returned by the lookup
- A couple of custom states on the page to store the values returned by the by lookup.
- The TEXT elements (labels) should have their initial values set to the custom states. Thus, when the custom state values change the data displayed by the labels will change.
In the image above, notice that the two text elements on the right have their values set to custom states. (We are storing the custom states on the INDEX page but you can store them in any element you like).
Now, lets take a look at the workflow attached to the LOOKUP button:
In the workflow, we are just setting the custom state to a value returned by the lookup. In our example here, the custom states are on the INDEX page and the email address being used for the lookup is in an input called EMAIL ADDRESS.
Finally, here’s a GIF that shows the lookup in action:
The hardest part of this process is setting up the API link. Once that is completed, all your existing Bubble skills will apply.
Note that we showed a simple lookup but you can also display the data in a repeating group.
If you need us to do something similar for you, just drop us a note! For example, the API can be extended to allow updates to your Google Sheet.
PS: Bubble does have a SHEET2API plugin but why add another extra layer of code to your app when just using one API call will do the trick?