How to integrate Google Sheets with Bravo Studio

Bravo Studio and Google Sheets: Getting started

There are several ways to authenticate Google Sheets with Bravo Studio, and depending on which option we choose we will have different limitations. Ideally, oauth should be used, but for oauth we need to add a login or use a service that refreshes our oauth token, like Sheety does.

As for now, we will use the APIKey (which is easier to handle but doesn’t allow us to add new rows.)

To create an APIKey you need to go to: https://console.cloud.google.com/ to create a new project

Next, create a new key under APIs and Service > Credentials

This key will need to be included in all API requests that we will carry out. We should also set the access to 'Anyone with the link can view' in the Share button of the document.

Now we should be able to do GET requests to our document. Here's an example using this Google Sheet.

There are several endpoints we can use, but we will focus on the one that returns our values back.

‍

GET <https://sheets.googleapis.com/v4/spreadsheets/><document_id>/values/<sheet_page_name>!<indexes_we_want>?majorDimension=ROWS&key=API_KEY

document_id: Is the long identifier of the URL

sheet_page_name: The name of the sheet page we want to access in the document

indexes_we_want: We use A1:H, because we want to get all the rows from A to H

‍

If we want to skip first row, we can query A2:H for example.

An example:

This one queries our main table called Issues and skips the first row with the names

‍

GET https://sheets.googleapis.com/v4/spreadsheets/1f0hM_Jd2lrp6QEfv-ejS-jJ5Qx4V4lcfy3aBiYxYb6w/values/Issues!A2:H?majorDimension=ROWS&key=API_KEY

‍

Sadly we can’t query filters easily using the Google Sheet API, an easy way to avoid this limitation is to create a new page for each filter we need. In our case we did create two new pages, with the Solved and Unsolved issues.

We can add a QUERY filter to the main table that has all the Issues listed, for the ones we need and use the API to query that page.

We then can do these queries to get the filtered results, we query to A1 instead of A2, because we do not have the first row on the filtered tables.

This query will get the solved issues:

‍

GET <https://sheets.googleapis.com/v4/spreadsheets/1f0hM_Jd2lrp6QEfv-ejS-jJ5Qx4V4lcfy3aBiYxYb6w/values/Issues-Solved!A1:H?majorDimension=ROWS&key=API_KEY>

‍

And this query will get the unsolved issues:

‍

https://sheets.googleapis.com/v4/spreadsheets/1f0hM_Jd2lrp6QEfv-ejS-jJ5Qx4V4lcfy3aBiYxYb6w/values/Issues-Unsolved!A1:H?majorDimension=ROWS&key=API_KEY

‍

Filters used:

‍

=QUERY(Issues!A2:H,"select * where H = False")

=QUERY(Issues!A2:H,"select * where H = True")

‍

Pain points and limitations

There are some limitations that probably will need to be discussed to have a better integration with sheets.

• Authentication comes with oauth or apikey, both require some configuration from user, nothing much we can do here except guide them. APIKey is sensitive but is in the url query should be handled as a variable
• Sheets support images but they are not very well supported in the API, so using URLs instead and do automatic uploading of images to our server or 3rd party could save a lot of trouble. (could be a business feature or paid by space used).
• Sheets API doesn’t allow to filter data, a trick is to create other sheet views with the filters we need, another option is used the chart api with query language, which allow to use SQL in a sheet! But due to restrictions response require a backend to process it, as doesn’t return a valid JSON.
• Sheets API doesn’t allow to create new data, unless you authenticate the user via oauth or use a Google App Script, to create a form that submit the result to the sheet, which is tricky.

Reference:

https://developers.google.com/sheets/api/reference/rest

‍

For more information on mobile app building with Bravo Studio, visit our Academy page or Youtube channel