🔙 Back to blog
November 3, 2022
Toby Oliver

How to integrate Google Sheets with Bravo Studio

Google Sheets is a spreadsheet program included as part of the free, web-based Google Docs Editors suite. You can choose to use Google Sheets as your backend in Bravo Studio for simple mobile apps that are mainly designed to display data.
Tech

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

More like this

Join 100,000+ Bravistas today

Fastly test or launch powerful and beautiful apps.
No code necessary.
Get started for free
bravo-icon
Start Bravorizing today
Fastly test or launch powerful and beautiful apps.
No code. Yes Design.
Get started for free
Get the best of Bravo straight to your inbox
Subscribe to receive new updates, offers and resources
👏Thank you! Your submission has been received! 📩
😖 Oops! Something went wrong while submitting the form.
© Copyright 2022 App Foundry S.L.
All rights reserved.
Bravo Studio and its products are not affiliated with or endorsed by Figma.