21min

Google Sheets With Charts Plugin for Bubble

Introduction

User's Data Version,The features that make Google Sheets so successful are available through the Sheets API. With the API, your code can access and update spreadsheets just like any other user. The API lets your apps:Import, export and format Sheets data Control conditional formatting Build and edit charts embedded in a sheet Configure data validation Create and update named and protected ranges Add and adjust filtered views Create and manipulate pivot tables... and even more!Make use of over 35 actions and counting plus an easy to use data parser element to help get your rows & columns ready fasterTo use the Element,

  1. Place it on your page.
  2. Provide a list of Things.
  3. Use the action "Make Row Data" &/or "Make Column Data".
  4. Reference the output and feed it to the specific Plugin actions.

Setting up

Setup Google Developers Console

  1. Sign in to Google Console: https://console.developers.google.com
  2. Create a new project
Document image



2. Give it a name and indicate Organization (if exists)

Document image



3. Go to Library and add enable Google Sheets API

Document image
Document image



4. Before generating Keys - configure Consent Screen

Document image



Here is an example of how the Convent Screen can be configured:

  • choose user type:
Document image



  • add App information:
Document image



  • add app domain and developer contact info:
Document image



5. Create OAuth Client ID credentials

Document image



In the Application type field choose "Web application", and don't forget to indicate Authorized redirect URIs (it should be the standard generic redirect https://zeroqode-demo-15.bubbleapps.io/api/1.1/oauth_redirect):

Document image



6. Publish your app in the OAuth Consent screen section, to make it work:

Document image



7. Paste keys into your app, to the proper fields:

Document image



8. Activate the checkbox "Use a generic redirect URL".

How to use

For the most part, the most used feature in a sheet is to write Columns or Rows. Most plugins and other secure integrations connect via the API Connector meaning that when it comes to adding a list of data or trying to format your values a certain way things quickly turn into long complex workflows or roadblocks. We go about this a little differently.

Firstly notice there are two Action's at the bottom of the list below that end with either (ROW TOOL) or (COLUMN TOOL), These have all the safety of the API Connector, all the benefit's of being a core feature to your app but also allow the use of the 'Sheet's Data Maker' element. Here is how -

1. Drop the element on your page.

2. Setup an event for Column's & an Event for Row's

Document image



* Remember when adding a run of actions that include the data making element, to provide some delays.

Handy to know:

  • Its worth noting that Spreadsheet ID's, Sheet ID's **& _Sheet Names _are referenced throughout this plugin and they stand for different things. Eg. A spreadsheet has an ID like so,_ 1PciD2dkT3IJTuIJ0484XjdoalxBfSXStkL3sFpIYj4QT, each sheet inside the spreadsheet also has an id. This ID ranges in its value but usually, the default first page in the spreadsheet will have the number 0 as its ID. Common ID's after the initial sheet will often look like this, 241376695. The other identifiable property is a sheets name, this can be the title you assigned it or something like this, Sheet0. Another quick note on ID's, you will also come across the chart ID**_. Keep in mind that some action's require one of these or a mix of these & sometimes even the same ID into places
Document image



  • There are a number of calls that have (custom) added to the end of their names, These are for your own creative use cases and the way they work is simple - I have forced the returning data to only contain the one thing that always carries on, the spreadsheet ID. So you will see an action such as this one below and you will know that the ability is in this case to update a spreadsheet. Because it will only return the spreadsheets is your able to follow the doc links in the plugin to Google Sheets Reference Doc's and build your own functional extra's/styles/formats/abilities using the JSON body. Reference Doc's and build your own functional extra's/styles/formats/abilities using the JSON body.​​​​
Document image



Example 'Create Sheet' JSON body.

{ "properties": { "title": "myCustomSheet", "defaultFormat": { "backgroundColor": { "alpha": 1, "blue": 12, "green": 22, "red": 30 }, "horizontalAlignment": "CENTER", "borders": { "bottom": { "color": { "alpha": 12, "blue": 45, "green": 121, "red": 41 }, "style": "DASHED", "width": 122 }, "left": { "color": { "alpha": 1.0, "blue": 121, "green": 200, "red": 50 }, "style": "DASHED", "width": 100 }, "right": { "color": { "alpha": 2, "blue": 121, "green": 121, "red": 0 }, "style": "DASHED", "width": 140 }, "top": { "color": { "blue": 0, "alpha": 0, "green": 0, "red": 0 }, "style": "DASHED", "width": 0 } }, "hyperlinkDisplayType": "PLAIN_TEXT", "textFormat": { "bold": false, "fontSize": 0, "foregroundColor": { "alpha": 0, "blue": 0, "green": 0 } } } }}

  • It's a good idea to setup a 'Do when unhandled error occurs' event as it can be very easy to input values or ranges that can cause an error to return.

  • REMEMBER - Your service account is the owner of a sheet, to begin with. if you provide the link to the sheet to a user you must **first use either the 'Allow spreadsheet access' or 'Transfer Ownership' actions to avoid permission issues.

Available Actions

  • Get Spreadsheet

    Get Spreadsheet (Data)

    Get Spreadsheet Properties (Data)

    Get Spreadsheet Properties

    Get Sheet Values (Range)

    Get Sheet Values (Column)

    Get Sheet Values (Row)

    Add Sheet to Spreadsheet

    Remove Sheet from Spreadsheet

    Clear RangesCreate Spreadsheet (Custom)

    Update a Spreadsheet (Custom)

    Add Charts to Spreadsheet (Custom)

    Delete Chart From Spreadsheet (Custom)

    Add Protected Range

    Conditional Formatting Spreadsheet (Custom)

    Data Validation Spreadsheet

    Move/Resize Chart From Spreadsheet (Custom)

    Read Charts on Spreadsheet (Custom)

    Copy Sheet to Another

    Add Empty Rows to Sheet

    Add Empty Columns to Sheet

    Write to a Cell

    Write a Column Sheet

    Write a Row to Sheet

    Find & Replace

    Move a Column

    Move a Row

    Auto Resize a Row

    Auto Resize a Column

    Allow Spreadsheet Access

    Restrict Spreadsheet Access

    Transfer Ownership

    Allow Spreadsheet Access to User

    Write a Row (ROW TOOL)

    Write a Column (COLUMN TOOL)

Demo to preview the settings





Updated 20 Apr 2022
Did this page help?
Yes
No