Forum Academy Marketplace Showcase Pricing Features

Mastering Google Sheets API

Today I start my second month on Bubble. It has been an amazing experience so far. Suddenly everything seems possible. But I’ve also hit a wall with Google Sheets API and it would be great if you could help me. Apologies in advance for any misuse of terminology.

Bigger picture, I’m interested in Bubble for APIs generally and I’d like to master them. I know there are plenty of 3rd parties which easily do the job, I’d still rather learn how APIs work.

Smaller picture, I’m trying to create an interface for my current employer so they can read and write to their multiple (20+) Google Sheets without going in and out of each doc. The data are simple order data (supplier, order number, materials etc.) all in neat columns and rows.

Test 1: sheetdb.io

In my initial MVP, I integrated with a few Google Sheets aps and got exactly where I wanted to be with sheetdb.io. It’s very easy and I’d recommend it if you’re happy with a third party solution, or just to test. Here’s how I see it up with sheetdb.io in plugins:

The full call looks like this:

https://sheetdb.io/api/v1/5onoptbzy33kzz9/search_or?OrderNumber=[query]&OrderDate=[query]&OrderedBy=[query]&Materials=[query]&Supplier=[query]&Costs=[query]

The queries allow me to search the sheet by column. For example, if I add a further query &OrderedBy=[query] I would also be able to search who ordered items once the app is live.

And here’s the repeating group set-up in design:

And for the cell data in the repeating group I just added a text for each column and connected it to the sheet with this:

It really was that simple. I pulled in all the data I wanted from the sheet and even added a simple search input which works way better than Google sheets. No work-flows btw.

Test 2: Google sheets API

This test was by no means as successful although connecting to Google sheet’s API was also remarkably simple. If you haven’t done so already, I followed the following steps:

On Google Cloud Platform / Google Sheet

  1. Create a developer account at https://console.cloud.google.com/
  2. Set up keys. I’m only reading data so far so only set up the client key and that seems to work fine.
  3. Choose which services you want to interact with (in my case, Google Sheets) from the console library and enable them.
  4. Change the settings of the Google sheet you want to interact with to “shared” (top right of the page) and share it with the email that appears at the bottom of the ‘credentials’ page back on Google console.

On Bubble

  1. Install Bubble’s generic API connector.
  2. Create an API for Google within the API connector. That’s literally just a case of ‘Add another API’ and calling it ‘GoogleCloud’ (the name is not important as far as I know).
  3. Set-up the call.

I set the call up as follows:

When run, the call reads the spreadsheet fine and the raw data (linked at the bottom of the call results) shows the entire data from the sheet.

I can also access the data from Bubble including individual rows, albeit randomly, from here:

But I can’t recreate the sheets.db integration in 2 important ways (possibly connected):

  1. I can’t recreate the repeating group as I did with sheetdb.io. It’s not clear to me which ‘type of content’ and ‘database source’ to use in the repeating group nor which calls to make from the cells.

  2. I haven’t managed to extract the values &Supplier=[query]&Costs=[query]* as I had in the sheetsdb.io version. I imagine I’ll need that when I come to search the data later on the live app.

What am I missing? Is sheetsdb.io and others preprocessing the data somehow or is it something more obvious than that.

Thank you in advance for your help. I’m obviously a complete newbie so I can’t offer much in return, for now, although I hope this post has been useful for some.

so for

  1. type of content has to be what you called your request in the api connector
    and source is “get data from an external api” and select what you have in the api connector

not sure what you mean with 2 yet?

1 Like
  1. Thank you for. First bit of progress in days :slight_smile:

For those wanting to learn from my mistakes, I’d given my API calls uniquely unhelpful names. The one I needed I’d called ‘values’ which is why I was going round in circles.

  1. So now my cell data looks like this.

Screen Shot 2021-06-23 at 15.05.41

which is progress but what I really want to do is extract the data elements or values (not sure of the right word) so they appear in columns as per Google sheets e.g.

Screen Shot 2021-06-23 at 15.16.50

With sheetdb.io that was easy. Each of the column headings from my Google sheet appeared in the drop-down here:

Screen Shot 2021-06-23 at 15.13.15

But there are no such options with my Google API call and I don’t really understand how they appeared in the previous set-up.

How do I pull out the data from my call into the correct cells for columns and rows rather than grouped together in the cells of the first column?

urgh i mean the make shift solution is to extract this with regex \s*([^,]+)
or using find and replace, checkbox: use regex pattern then you can underneath find add the above pattern and replace add $4 to get the 4th thing (in this case Speedy) or 3 for 3rd thing etc

but I am afraid it more has to do with your API call and setup
could you provide some more screens of api call and then your repeating group showing the data?

doesn’t mean anything to me yet. I’ll look it up, thank you.

The API set up looks like this:

with this as the full request:

**https://sheets.googleapis.com/v4/spreadsheets/[sheet]/values/[values]?key=[key]

And this is the cell data for the repeating group:

Screen Shot 2021-06-23 at 15.44.59

vs this one from sheetdb.io where the cell matches the relevant column (e.g. OrderNumber).

Screen Shot 2021-06-23 at 15.46.22

so the regex i provided just turns a list of texts separated by commas into text 1 text 2 text 3

from a quick look at google sheets api docs
I think having a major dimension as another query parameter may help

1 Like

Thank you. I’ll go and experiment and will report back.

Hello. Any update?

I haven’t forgotten, just sensed that I was out of my depth and needed to learn around the topic. Getting there!