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.
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.
This one queries our main table called Issues and skips the first row with the names
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:
And this query will get the unsolved issues:
=QUERY(Issues!A2:H,"select * where H = False")
=QUERY(Issues!A2:H,"select * where H = True")
There are some limitations that probably will need to be discussed to have a better integration with sheets.