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

Tips
1

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

Screen Shot 2019-12-10 at 4.30.47 PM
Screen Shot 2019-12-10 at 4.30.47 PM1736×1258 151 KB

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

Screen Shot 2019-12-11 at 5.28.20 PM
Screen Shot 2019-12-11 at 5.28.20 PM2044×736 94.6 KB

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!

18 Likes
2

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
3

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.

4

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

5

How about using Google Sheets for displaying data only ?
( No read or update needed )
Current I can connect to Airtable , but limit records ber base , so I would like to use Google sheets

1 Like
6

hey @andres,

Been trying to do this but for some reason I get an error that says:

{“detail”:“Could not update the connection, verify its permissions.”}

Do you have any idea what i’m doing wrong? Thanks.

7

Hi Bro,

Did you get an answer for this? I just want to display data when the user clicks a button.

There is a dropped down that user selects, and when they click this button, app shud send the dropped down value to google sheet and get the corresponding value and display in bubble app.

Any idea? The data is way too much to store in Bubble DB and will unnecessarily increase the bubble app size

8

Can anyone show me how to do this?

9

Have you found a solution for this?

10

Hello @andres ! Would you please share the workflow action detail for sending data to Gsheet (step2) ? I followed steps to connect but I’m lost for the next steps :confused:
I also would like to get data back from Gsheet to Bubble (my idea is to use Gsheet Translate function to trabslate app messages)
Thank you so much in advance !

11

Hi, bubble noob here hoping someone could help. I’m getting a little stuck on step 10 onwards, my body looks like this.

{
   "branch": [
	
      {
         "branchName":"<branchName1>",
         "branchCount": "<branchCount1>"
      },
	
      {
         "branchName":"<branchName2>",
         "branchCount": "<branchCount2>"
      }
   ]
}

When I click initialize call my near empty google sheet appears regardless of what I put in as the value of the , etc. Is that meant to happen?
Should I manually put in values for the dynamic data or leave it empty?
When you say tag items as exported, does it mean I have to add a new yes/no field to my branch datatype?
How do you access sheet.best from the backend workflow like they did in the image?
Sorry if this is off topic, I can ask this on a new thread if needed.

12

Hey, for anyone who may hit this post looking for a solution. I’ve just posted about a solution I’ve put together that doesn’t require any plugins, the API connector or third-party services. Check it out and let me know what you think. Hope it helps

13

Noob question here: I have managed to set up my app to submit data to a Google Sheet via sheet.best.
Now I have two concerns:

I don’t want the data to be publicly available, so now I’m wondering what I can do, or whether it is even possible so submit data via API to Google Sheets without it being exposed?

1 Like