Forum Academy Marketplace Showcase Pricing Features

[How-to] Send UNLIMITED data to google sheets FREE (without zapier or blockspring)

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

  1. Set up a free account on www.sheet.best
  2. Go to your target spreadsheet and get a shareable link - make sure to set this to ‘anyone with the link can edit
  3. Go to your sheet.best dashboard, click the ‘New Sheet API’ button and paste the link.
  4. A dialog will open with your brand new sheet API. Copy it to your clipboard.

II. Set up sheet.best in Bubble API Connector

  1. In your Bubble API connector, click on ‘Add another API’
  2. Give your API a name
  3. Set Authentication to ‘Private Key in Header’
  4. On the ‘Key Name’ field, type “X-Api-Key”
  5. 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.

  1. Give your api call a name, set it to use as Action, and set the Data Type to JSON.
  2. Make it a POST request, and now paste the API Url endpoint you got from sheet.best (step 4 of part I)
  3. For the Headers, set Key = Content-Type; Value = application/json
  4. Set the Body type to JSON.
  5. 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:

  1. Tags the items as ‘exported’
  2. Exports the items to g sheet
  3. Schedules itself if the count of unexported items > 0

And you’re done. Happy data exporting!


P.S.
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!

13 Likes

Hey There,

This is great, but can you see a way to get it to write the whole table? Im looking to create a “back up” to track any changes to the database. I think a new sheet each day would be good. Is something like that possible?

Benjamin

1 Like

Hey Benjamin,

you can set up a recursive workflow that can send the items to google sheets when these items’ lastprocesseddate<=(-24)hrs ago:count >= 1. As the last step in this workflow, when these items’ lastprocesseddate<=(-24)hrs ago:count = 1, schedule this workflow 24 hrs from now.

As for a new sheet each day, you’d have to check sheet.best documentation to see if they have that ability, but I suspect not out of the box.

A workaround off the top of my head is to use google sheets’ query function to separate tables by day.

Hey @andres,

Thank you for helping me with this. Actually, I am looking for a way to copy the whole table over not just the stuff that was added in the last 24 hours. I know this is data intensive, but it’s necessary for the time being. I am using it to watch when changes to certain things in the database are made. The second idea is to keep a back up, but it’s more important to know when something happened. Does this make sense?

Benjamin