Bubble | No-code apps

Append & Update Rows into a Google Sheet

This plugin lets your Bubble application write rows into a Google Sheet. This is your Bubble app authenticating with a Service Account - invisible to your users.
You can send Bubble objects directly - the plugin will convert the Bubble objects into a row in your Google sheet.  Or you can pass specific values for up to 25 columns.
You can also do basic Database type operations against a Google Sheet. eg get and update Cells, delete Rows.

The trickiest thing is getting your Google permissions setup correctly.  Purchase of the plugin comes with reasonable support to get you up and running. Contact me in the forum @lindsay_knowcode

There are step by step videos below to show you how to set up things on the Google side. Playlist here https://youtube.com/playlist?list=PLpZXz2W3mS45yN5qbC0TDD1g7D_NZHIhy

If you are here you have probably have tried other plugins and tried to use the API connector and found it just too painful - that is why I created this plugin.

It does not use any 3rd party intermediary connection service like sheets.io or others - so no additional costs. It uses the Google Sheets and Google Drive Rest APIs directly. So no additional Zapier or tray.io costs,

This plugin does not try to do every operation to manage a Spreadsheet - it is just for easily getting data out of Bubble into a Google sheet. If there is something useful you need adding DM me in the forum @lindsay_knowcode https://bubble.io/contributor/knowcode-1595426107417x822993085097189100

Summary - What does this plugin do?

How to setup your Service Account in Google

First setup a Google App and get your Service account email and Private key
  1. Create an App in https://console.cloud.google.com - Google Developer console
  2. Create a Service account in that App and give it the right permissions
  3. Download the json token file, copy the service account email & private key in to the plugin

Google setup - Step 1

Create an App in Google Developer console

Google setup - Step 2

Create a Service account in that App and give it the right permissions

Google setup - Step 3

Download the json token file, copy the service account email & private key in to the plugin

How to setup your Workflows

Now create the Bubble workflows. Use Backend Workflows so that you can take advantage of "ignore privacy rules" and keep your token secure.
  1. Authenticate and get an access token so you can access Google sheets - store it in your DB (see example)
  2. Within your workflows create a Spreadsheet - get the Sheet Id - and store it in you DB. You likely only need to do this once. (see example) 
  3. Now you can append and update rows into your Google Sheet from your Bubble DB (see example)
  4. Share your Google sheet (see example)

Bubble setup - Step 1

Get an authentication token

Bubble setup - Step 2

Create & Share a Google sheet

Bubble setup - Step 3

Append an update rows in that sheet - by sending an Object or sending columns

Actions in the Plugin and what they do.

You can pass in any Bubble object - the plugin will convert the Bubble Object into a row.  Or you can pass specific values for up to 25 columns.

  • Get an authentication token
  • Create a Google sheet
  • Tell Google to share the sheet
  • Append an update rows in that sheet

Working Examples

The examples below are simple examples and not using Backend workflows to keep access to your Google sheet token private.  However number 6 below is setup in a secure way using Backend workflows - but won't actually function as Backend workflows as the is is a free app and BE WF are only enabled on Paid for Bubble apps See Step 8 of how to setup Backend Workflows and Privacy rules.

1. Get Access Token

You need to get an Access token first. You can store this as it expires after 60 minutes.  The example in this demo application has no privacy rules - in your application you will want to store the Access token in a table secured with Privacy Rules and run it from a Backend workflow with "ignore privacy rules".
GS - get access token
Authenticate and get an access token for access to Google Sheets. Keep the access token private in a table protected by Privacy Rules. You need to do this when the access token expires. 

Use a conditional custom event in your workflows to refresh the token if it has expired. So whenever the token expires (has a life of 60 minutes) your Bubble app automatically get a fresh token. See example here https://bubble.io/page?type=api&id=google-sheet-updater&test_plugin=1663938003776x932115872220446700_current&tab=tabs-2
Generally you don't need a "Get access token" button in your app - your workflows go and get a token as required (when the token has expired).

2. Create a new Google Sheet

You need to create a Google sheet - the one where you want to update. What you need is a Sheet ID owned by the Service account. When the sheet is owned by the service account. is greatly simplifies setting up the Google permissions.
GS - Create a new Google sheet
Used to setup an empty Google sheet. You probably need to do this once and store the Sheet Id. You need the sheet id for any row appending or updating.

3. Share a Google Sheet

GS - Share a Google sheet Tell google to email an invite to the User to be able to read or edit the sheet. Select whether you want the User to have Read or Edit access to the Google sheet.
Grant Read or Edit permission to the sheet
Email address of Google account to share sheet with

4. Append a Row - supply a Bubble Database Thing

GS - Append Thing into a Row in Google sheet This example shows how to add a row to the end of the spreadsheet. The properties of the Thing from the DB are converted to columns.  The format of the dates from bubble are like this: "2022-09-24T22:43:29.230Z" so Google sheets will use UTC as the date.

If you want to see the values entered into the Google Sheet - add yourself as a reader or writer in Step 3

5. Update a Row

GS - Append Thing into a Row in Google sheet This example shows how if you provide a row number and set the method to update, that row will be over-written. The properties of the Thing from the DB are converted to columns. Alternatively, if you provide a row number, that row will be over-written.
If it isn't working the what you expect,  the way to diagnose the problem is to view the "debug" - see the FAQ below on how to do that.  If you need help with what the debug means send it to me @lindsay_knowcode
Row to update
This is how to append the row in the workflows

6. Append a Row - supply columns (backend workflow)

This test example will produce a warning that the the Scheduled API will not work for the free app. But the example is here of how to put the update onto a Backend Workflow.
Designing your Bubble app to process requests from Backend Workflows is generally going to be helpful for the performance and management of your Bubble app.
GS - Append a row in a Google Sheet This example shows how you can specify the values for specific columns.  Leave the row number empty to append to the sheet and supply a specific row number if you want to over-write the row in the sheet.

See comment in the FAQ about how to specify the timezone.
Column A
Column B
Column C
Column D
Column E
Row to update - leave the row number blank to append to the end of the sheet.

7. Append a Row - supply columns A- Y, columns 1- 24 (actions in a workflow)

GS - Append a row in a Google Sheet This example shows how you can specify the values for specific columns.  Leave the row number empty to append to the sheet and supply a specific row number if you want to over-write the row in the sheet.

See comment in the FAQ about how to specify the timezone.
Column A
Column B
Column C
Column D
Column E
Row to update - leave the row number blank to append to the end of the sheet.

8. How to rename a sheet

GS - rename a sheet If not set the sheet will be called "Untitled spreadsheet'. Use the method to rename it.

9. How to get a cells value

GS - get a cells value Given a sheet name, column and row - get the cells value
Row Number
Column Letter
Cell Value

10. How to set a cells value

GS - set a cells value Given a sheet name, column and row - set the cells value
Row Number
Column Letter
New Cell Value

11. How to delete a row

GS - delete a row Given a row number - rows start at 1
Row Number

12. How to delete multiple rows

GS - delete multiple row Given a start row number - rows start at 1 and and end row number - delete multiple rows.

If you the start row number is 3 and the end row number is 5 - rows 3,4,5 will be deleted.
Start Row Number
End Row Number

13. Append a Row - supply columns Z- AX, columns 25- 49 (actions in a workflow)

GS - Append a row in a Google Sheet This example shows how you can specify the values for specific columns.  Leave the row number empty to append to the sheet and supply a specific row number if you want to over-write the row in the sheet.

See comment in the FAQ about how to specify the timezone.
Column Z
Column AA
Column AB
Column AC
Column AD
Row to update - leave the row number blank to append to the end of the sheet.

14. How to get a Row (list of all the values in a row)

GS - get a Row Given a sheet name, row , and an End column (eg AA) - get the row values as a Bubble list of texts
Row Number
Column Letter

Row Values

How to setup the Privacy Rules and Backend workflows to keep your token secret.

Use Backend Workflows (only works with paid for Bubble apps)
Set the privacy rules so that no one can read the table you keep the Token in.

Then use a Backend Workflow with "ignore privacy rules" enabled to do the updte.

FAQ

How do I update multiple different sheets?

You need to store a different spreadsheet id for each sheet you wish to update. For example in a table. You use the same access token for each sheet - the access token is the same for each sheet.

How do the google permissions work?

The Google service account that is setup is the owner and only user who can access its own resources. (ie drive docs like Gsheets) so when that Service Account creates the sheets, it implicitly can read/write/share the sheet(s) (drive doc) as it is the owner.

How should I setup my privacy rules for the token and sheet id?

Don’t give any access to anything/anyone to the Bubble table you store the token and sheet id. Make it totally private. (unless your Google sheet data is public and you don't care)  You should only be accessing the config table (especially tokens) via Backend workflows with "ignore privacy rules"
You can't run Backend workflows with a Free Bubble app (like this example app is) so this app can't show you how it works - but it does show you how to set it up.

You may want to expose the token expiry time. DO NOT EXPOSE THE TOKEN - USE A BACKEND WORKFLOW.

https://youtu.be/2aZ7zybs1NM

What is the Token expiry time? It seems to always be 3599?

The plugin requests an authentication token that lasts 60 minutes. That is set in the plugin - I try to simplify things by not offering configurable options that are never used. If you have a good reason to want to specify a particular expiry time get in touch and I'll make it configurable.

Why are the timezone coming into my Google sheet as the Users local time?  I expect UTC.

You need to specify to Bubble what timezone you would like. The default setting in the Bubble users timezone. What you want to do is specify UTC.

How do I get hold of the debug?

If things aren't working as expected then debug is very useful to tell you what is wrong. You can get hold of the debug information from the action in the workflow.

Why not make it a free plugin?

There are already too many half baked, unsupported plugins.  Purchase of the plugin comes with reasonable support to get you up and running.
If you are a Good Person and doing Good Things  and deserve charity I may offer you a free license. Get in touch.
Bubble Backups https://planbbackups.io
Safe secure backups for your Bubble database
My Bubble Profile & Plugins
https://bubble.io/contributor/knowcode-1595426107417x822993085097189100
Pleased to say all 5* reviews.
My Coaching
https://nocodeguides.io/profile/lindsaysmith book a quick expert consultancy session
My Youtube Channel - about my plugins.
https://www.youtube.com/c/LindsaySmithRocks
My Freelancing Website 
https://www.knowcode.tech
https://knowcode.tech

Google Access Token

https://knowcode.tech
This was created by ToC Plugin.