Another common question I see in the forum is how to send bubble data to google sheets. The easy way involves Zapier - but the problem is you can only send one row at a time and the free plan will very quickly run out of Zaps, forcing you to upgrade to a premium plan. Other tools like Blockspring don’t have a free plan or are very limited.
So, can you send Bubble Data to Google Sheets without paying a cent to 3rd party tools? Yes, you can.
There are several 3rd party services that help you turn your google sheets into an API. See:
We’ll be using sheet.best in this tutorial.
sheet.best has the most generous free plan (and arrays count as a single request <<This is HUGE and what allows us to have a virtually unlimited plan)
Meaning: If you need to read/write 20,000 rows per month, you can just send 2,000 requests in arrays of 10 with the free plan.
Let’s get started.
Adding/Appending Rows to your Google Sheets
I. Generate your API
- Set up a free account on www.sheet.best
- Go to your target spreadsheet and get a shareable link - make sure to set this to ‘anyone with the link can edit
- Go to your sheet.best dashboard, click the ‘New Sheet API’ button and paste the link.
- A dialog will open with your brand new sheet API. Copy it to your clipboard.
II. Set up sheet.best in Bubble API Connector
- In your Bubble API connector, click on ‘Add another API’
- Give your API a name
- Set Authentication to ‘Private Key in Header’
- On the ‘Key Name’ field, type “X-Api-Key”
- For the ‘Key Value’ field, find it in: sheet.best dashboard > Sheets > [wrench icon] > Security > API Key Code
Now let’s move on to the actual API call.
- Give your api call a name, set it to use as Action, and set the Data Type to JSON.
- Make it a POST request, and now paste the API Url endpoint you got from sheet.best (step 4 of part I)
- For the Headers, set Key = Content-Type; Value = application/json
- Set the Body type to JSON.
- In the Body, use the below format for the array and its parameters you want to send. The below example is only appending 2 rows for brevity’s sake.
“productname”: “< productname1>”,
“brand”: “< brand1>”,
“supermarket”: “< supermarket1>”,
“regularprice”: “< regularprice1>”,
“offerprice”: “< offerprice1>”,
“creationdate”: “< creationdate1>”,
“expirationdate”: “< expirationdate1>”
“productname”: “< productname2>”,
“brand”: “< brand2>”,
“supermarket”: “< supermarket2>”,
“regularprice”: “< regularprice2>”,
“offerprice”: “< offerprice2>”,
“creationdate”: “< creationdate2>”,
“expirationdate”: “< expirationdate2>”
There you have it! Now you can send data to google sheets any way you want.
III. Run your workflows
To export every item in your database, create a recursive workflow that:
- Tags the items as ‘exported’
- Exports the items to g sheet
- Schedules itself if the count of unexported items > 0
And you’re done. Happy data exporting!
The Google API documentation only references ways to append rows to sheets using Oauth.
Apparently, there’s a way to use the google sheets API directly without using oAuth as per the instructions in the link below, but I couldn’t get it to work. If anyone has figured this out, pretty please let us know how. This would eliminate the use of 3rd party tools altogether and directly provide a quick and free external database for Bubble users to read/write to. Would be amazing news for many of us!