Google Sheets API - Connection to Bubble

Jobs / Freelance
1

Hi,

have some problems with connecting the Google API for google sheets to bubble.

Actually I already solved the API Connection via a service like DBSheets or sheety.
Nevertheless I wanted to do it directly by the instruction that offers
Google and created an API by using an Google Aps Script.

The problem is: the result looks like this:

{
“range”: “testing!A1:Z1000”,
“majorDimension”: “ROWS”,
“values”: [
[
“Name”,
“Nachname”,
“Mail”,
“ID”
],
[
“Tobias”,
“Spaeth”,
“tobiaspspaeth@gmx.de”,
“1”
],
[
“Benjamin”,
“Maier”,
“benjamin@mail”,
“2”
]
]
}

But I expected a rest API in json format like this here:

{
“sheet1”: [
{
“unternehmen2”: “Computer”,
“mail”: 9000,
“id”: 2
},
{
“unternehmen2”: “Rufus”,
“mail”: “Herbert”,
“id”: 3
},
{
“unternehmen2”: “Markus”,
“mail”: “@web.de”,
“id”: 4
}
]

When i want to connect it to bubble it only gives my a chain of values
instead of a single value.

So in Bubble API Connector i get something like this here:

image
image1274×511 34 KB

and not something this here, as i would expect it:

image
image1253×666 43.1 KB

My main problem ist now: When i want to show content in a cell or add content to the database it gives me the a chain of values and not a single value.

Can somebody help me to deal with this problem?

1 Like
2

Hey @tobiasspaeth83 i am working on something like that! I have the same trouble because i need to turn the Google Sheet on JSON to work on loop to create Users in my app from Email/Name in Google Sheet, i’ll send dm to you, can we work togheter on it!

1 Like
4

Hi @tobiasspaeth83 and @machadoa953

A big welcome to the Bubble Community @tobiasspaeth83 - I understand this was your first post. Looks like you’ve jumped in at the deep end :wink:

Sorry for the pause, I need to take a deep breath every time I return to Google’s API :slight_smile:

In answer to your question:

My understanding of Sheets API is that it returns a ‘chain of values’, exactly as you described, either as a row (majorDimension=ROWS) or a column (majorDimension=COLUMNS) of cell values. Either way, data returned in your GET request isn’t in the nice JSON format you and I were hoping for. Or not how I understand the API - I am happy to be corrected.

My ‘solution’ was to pull in each value from the string of values into a separate cell in a repeating group. You can read more details about that solution here: Mastering Google Sheets API - #10 by aestela

But I’m reluctant to even call it a solution because it only half solved my own problem. And before I had the chance to find a better solution, my employer moved to Excel so I left it unfinished.

In short, I’m not the best person to ask, sorry!

5

You’re correct when you say that Google Sheet doesn’t return JSON and that’s exactly it, it just returns a “chain of values” but it doesn’t have a key and value like JSON. The easiest way I’ve managed to get around this is to download the Google Sheet spreadsheet in CSV format and then use the T1 CSV Uploader plugin (or, if you want something more customized, play around with the Papaparse javascript library) to turn the CSV into JSON and then loop and save each item in a row in the database and then show in the repeater group.

My use case that made me fall into this world was because I had a User/Email list on Google Sheet and I would like to enable a function where I could upload the spreadsheet and, with a recursive workflow, create accounts in the system based on the values ​​from Google Sheet, which I would automatically pull via API.

Anyway, I ended up leaving it aside, it wasn’t something necessary and I spent several stressed hours trying to solve this problem, who knows in the future I’ll come back to my solution of transforming the spreadsheet into .csv

Anyway, we are warriors for having passed through this valley, we are definitely persistent. If I eventually have an update, I’ll post it here or in the other topic!

1 Like
6

@machadoa953 nd @dorianharris ,
thank you two for your feedback!

@dorianharris, of course i ve already looked at your old post, but actually couldn´t figure it all out what you meant with the header…

but i will look at it again.
at least there are some posibilities how i could perhaps solve it.

7

Yes, same thing happens with Big Query, Google is trying to be clever and not repeat the keys loads of times.

They provide an SDK to “unpack” this but that is code only. I think I had to do something nasty with some Javascript and passing the raw JSON back into my Bubble app.

Will see if I can find it.