Mastering Google Sheets API

[dorianharris]

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.

[TipLister]

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?

[dorianharris]

1. Thank you for. First bit of progress in days

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.

2. So now my cell data looks like this.

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.

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

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?

[TipLister]

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?

[dorianharris]

@TipLister

regex \s*([^,]+

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:

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

[TipLister]

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

Method: spreadsheets.values.get  |  Sheets API  |  Google...

Method: spreadsheets.values.batchGet  |  Sheets API  | ...

[dorianharris]

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

[tona]

Hello. Any update?

[dorianharris]

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

[aestela]

Hi @dorianharris I’m currently looking into this and I’m stuck with your same problem where data is served in bubble’s interface as a text array without the column headers. Where you able to solve this in the API request? I’m looking into Sheets API documentation but can’t find anything related.

[dorianharris]

Thank you for the nudge.

I’ve half-solved the problem, and it’s a bit of a hack, so here you go.

The headers I added manually (i.e. not dynamically from the Google Sheet) because, in my case, they’re fixed anyway. So my GET request started in the row below the header. I could have pulled in the header data too but I don’t know how to stop it scrolling in the repeating group.

The rest of the data I pulled in via Google’s API, as described above. What I realised looking at it again now, is that the text array is, in effect, a list of items corresponding to their respective column on Google sheets. In other words, the first item in the array corresponds to the first column.

With that in mind, I set the first cell in the repeating group as follows:

Where ‘Direct Test’ is the name of my API Call, ‘Values’ is the range of the spreadsheet (e.g. A1:Z100), Values (second one) is the array itself, ‘item 1’ is the first extracted row (according to majorDimension set as ‘rows’ is the API call, although I’m not sure about that) and ‘item#1’ is the first item in the array, in my case the order number.

So, I’ve got what I was looking for in the sense I’ve got a replica of the Google sheet on Bubble. What I haven’t managed to do is:

a) Pull in the header row dynamically and freeze it.
b) Search the repeating group data.

I hope I’ve helped a bit. Let me know if it needs clarification.

I’m still working on the rest. If you get there first, do say.

[aestela]

Hi @dorianharris thank you for your explanation, it makes a lot of sense, I’n my case the headers are also static so your solution could help. I’m testing with sheetdb and I’m also stuck with the DDBB search, I’m trying to compare data from sheets vs data on bubble and update the bubble DDBB accordingly but can’t get this to work. I’ll test saving every sheet column in a state (although it might hang the app +100.000 rows) and then extract the new item’s location in the array.

This should be so straight forward… but then again… its no-code lol.

I’ll give you a shoutout if the method works for searching data.

[dorianharris]

My sheetdb test was more successful and pretty much what I’m trying to replicate. Have you got as far as importing the sheet into bubble? If not, I may be able to help with that. I didn’t try and POST data though.

A small update on my direct test with Google’s API. It is possible to extract the header from the sheet and freeze it. I created a second repeating group, above the main table, set it a s a fixed number of cells (1 row / 1 column), and set the path values as the header row.

It’s better than adding the headers manually but it’s still not as good as the sheetdb version which lets you use the sheet columns as search parameters.

[aestela]

I wasn’t able to do searches on sheetdb inside bubble, I can populate a RG but If I want to compare data between what is stored in sheets vs what’s in my bubble DDBB.

What I was testing was to create the new rows by comparing an “ID” column I have in both databases and only replicating the ones that are “not in” my bubble DDBB but couldn’t succeed.

[dorianharris]

I wasn’t able to do searches on sheetdb inside bubble, I can populate a RG but If I want to compare data between what is stored in sheets vs what’s in my bubble DDBB.

Please share your settings both the API call and for the repeating group. I should be able to show you how to search your sheetdb on Bubble if you can see that.

@aestela

What I was testing was to create the new rows by comparing an “ID” column I have in both databases and only replicating the ones that are “not in” my bubble DDBB but couldn’t succeed.

I got a bit confused between columns and rows. Ultimately, do you want to be able to read and write data in Bubble and post it to the google sheet, or vice-versa? Or both?

[aestela]

Hi @dorianharris!! Sure, my setup is as follows:

API:

RG:

And inside every RG cell, text elements for every column in the Google Sheet.

This is all working perfectly and I’m able to see Google Sheets data inside the RG.

In the end what I’m trying to achieve is the following:
My customer updates data inside Google Sheets (adds new rows and deletes old rows), I need to reconcile the data inside bubble with the one in sheets and update it accordingly. What I was expecting to do was to compare data between sources to either create it in bubble (if its new) or delete it in bubble (if it was deleted in Gsheets), by evaluating the existence of the identifier (Opportunity_ID in this case).

I tried the following set up for testing, where I’d filter out the Opportunity_ID’s in google sheets that aren’t contained in my bubble DDBB’s Opportunity_ID, unfortunately the filter doesn’t work and the same values are returned as if it wasn’t there.

I tried saving the Google Sheets rows in a State but didn’t work either.

Hope this clarifies things a bit!

Thank you very much!

[dorianharris]

This doesn’t quite answer your question but one difference I see in our sheetDB set-up is that mine is a search where yours is effectively requesting a copy of your Google sheet without any search options.

Maybe that’s not an issue for you at this stage . If it is, the documentation is here SheetDB API documentation

In practise, my call looks like this:

https://sheetdb.io/api/v1/eflmy86jmjlkm/search_or?OrderNumber=*[query]*&OrderDate=*[query]*&OrderedBy=*[query]*&Materials=*[query]*&Supplier=*[query]*&Cost=*[query]*

with each query matching a header (column) in my google sheet. This method allows me to query the values in each / all of the specified columns and return just the rows with the specific values.

Over in Bubble I have an input connected to the repeating group as follows:

Back to your question…

@aestela

What I was expecting to do was to compare data between sources to either create it in bubble (if its new) or delete it in bubble (if it was deleted in Gsheets)

Anything added or deleted from Gsheets will automatically appear on Bubble, if not in real time, soon after (the reverse is not true. If you want to post from Bubble to Gsheets you’ll need to set up POST requests).

If I’ve understood correctly though, you’re not looking to POST to Gsheets, you ‘simply’ want to know, from within Bubble, which Opportunity_ID’s) have been changed since your last access. Is that correct? Is that so you can check that Bubble and Gsheets are connected (i.e. a short term solution) or do you need to have a record of changed items, long-term, for some reason?

[aestela]

Hi Dorian! I tried doing a query in the API Call, but can’t figure how to query from an array in order to return only the new rows. I tried setting it adding /search:or?OpID=![Query] where [Query] was a list of texts I passed in the data source configuration.

In your example, is the “Input Search all orders’s value” a list or a single value?

Regarding the last part, yes that is correct, and I basically need them to be in sync. I could use Gsheets as my database and that would solve all my problems but then again, that would be impossible to sustain $$ since I use the data on several pages for +800 users which would consume the API calls in seconds.

[dorianharris]

@aestela

Hi Dorian! I tried doing a query in the API Call, but can’t figure how to query from an array in order to return only the new rows. I tried setting it adding /search:or?OpID=![Query] where [Query] was a list of texts I passed in the data source configuration.

First, sorry, the GET request I posted above:

@dorianharris

https://sheetdb.io/api/v1/eflmy86jmjlkm/search_or?OrderNumber=*[query]*&OrderDate=*[query]*&OrderedBy=*[query]*&Materials=*[query]*&Supplier=*[query]*&Cost=*[query]*

lost its all-important asterisks when I pasted it here. They should read OrderNumber= * [query ]* &OrderDate= * [query] *

without the spaces (if I remove the spaces on here the asterisks disappear).

That’s allowing me to query my data on the front end (below) using any of the parameters added to the GET request above.

In my case, because I have included OrderNumber= * [query ]* in my GET request, if I type “JB085/11” into my search input, just that row will display. If I enter “Speedy”, all the rows with Speedy will display.

@aestela

In your example, is the “Input Search all orders’s value” a list or a single value?

As per the above, it’s a single value. Somehow (I’m imagining a separate call to create sheet metadata), sheetdb lets you set up the above query parameters directly from your header row as long as you add them to the GET request your side (on Bubble).

The ‘value’ in my “Input Search all orders’s value” is thus something like “Speedy”. When I run the search (just press enter, there are no workflows) sheetdb returns all rows which include the term “Speedy” in the ‘Supplier’ column. As I haven’t set ‘Order Ack By’ as a query, if I enter “Yes” no results are returned (as I wanted).

For anyone still following this thread, it’s exactly the above search functionality that I’m still trying to replicate using Google’s API.

@aestela

Regarding the last part, yes that is correct, and I basically need them to be in sync. I could use Gsheets as my database and that would solve all my problems but then again, that would be impossible to sustain $$ since I use the data on several pages for +800 users which would consume the API calls in seconds

To understand better, where are all these Gsheets coming from in your model and who are your 800+ users (customers, suppliers?). I haven’t got why you have so many sheets to interact with.

[aestela]

Hi Dorian! Thanks for your reply! I hit a roadblock on the query since I need to send a list of +100.000 through the query parameter to filter them out, that’s why I was trying to call the complete sheet and search it in bubbles side but doesn’t seem possible.

Just to clarify my use case, my customer uses a CRM which doesn’t have an API, and when requested they were charging upwards of a million dollars to develop it… yeah… incredible that this kind of things still happen. So, the solution their IT department came up with, was to create a workflow internally that extracts all CRM data in a csv form and it’s stored in an FTP for sharing with my app.

Since Bubbles backend workflows only allow to upload CSVs of up to 200 rows I am using Integromat to do the backend work but still the sheet requires some prep-work before running any automation, so I figured that if I was able to pull data directly from sheets I’d be able to update directly without going through Integromat.

Regarding the +800 users, they are all sales advisors that “consume” the same database since it’s all consolidated in the same sheet.

I’ve drawn an example of what I’m trying to achieve, hopefully this makes things clearer.

Thank you once again!