Hello All,
Edit (July 31): The testing website has been updated so that a Google Sheets Service Account is no longer required if you are reading from publicly available Google Sheets documents. Writing still requires a Service Account however. This version of the Google Sheets Component is preparing to enter the upcoming release of App Inventor with the next update, and so more than ever, your feedback with any unintentional behavior would be greatly appreciated. Thanks!
Original Post
Due to the discontinuation of Google Fusion Tables, the App Inventor team has developed a new component that will take the place of the Fusion Tables component, namely one which will use the Google Sheets API to allow a developer to store data on a Google Sheets document. We would very much appreciate any feedback about the Google Sheets component.
To begin using the Google Sheets Component, one must create a Google Sheets Service Account. A step-by-step guide can be found here:
The test server for this component can be found here (http://sheets-dot-ai2-ewpatton-temp.uc.r.appspot.com/). To clarify, this Google Sheets component is not a feature of the current release of App Inventor and is still under development. In order to use the Google Sheets component, you will need to use the test server linked above and download the companion from the test server by clicking Help > Companion Information. The download link for the test server's companion will be in the resulting pop-up
Below is a tutorial called The PizzaParty App which had previously used the Fusion Tables component. This tutorial has been updated to use the new Google Sheets component instead.
In addition to describing how to use the Google Sheets component, this tutorial will describe the process of creating a Service Account for the Google Sheets API and downloading the credentials as a json file, which will need to be uploaded to App Inventor for the component to work properly. Every Read and Write block in the Google Sheets component requires this json file to be authenticated. (The only exception is the GetWithQuery block, which only requires that the document is shared to anyone with the link.)
Once you have gone through the tutorial above and understand how to set up the credentials, please explore some of the other blocks that were not covered by the tutorial above. A summary of the blocks can be seen below.
A Summary of the Blocks
Helper Blocks
To begin with, there are two blocks that will generate A1 notation given (1-indexed) numbers for the row and column: GetCellReference and GetRangeReference. For example, calling GetCellReference with the following numbers for row and column will yield the string “C2”:
And the GetRangeReference block shown below will yield the string “A1:C2”
Although these blocks are available to the developer, anywhere you use these blocks can be replaced with a text box provided the text is accepted A1 Notation (without the SheetName! prefix, as that is handled by each of the blocks in its implementation).
Read Blocks
The Sheets component has a separate block for reading data from the spreadsheet column-wise, row-wise, cell-wise, and range-wise. The Sheets component also allows you to fetch the data of an entire page of the spreadsheet as once. For example, below is the ReadRow block.
Most of these blocks will have a sheetName parameter, which is simply the name of the page that you wish to edit. By default, Google Sheets names these “Sheet1”, “Sheet2”, and so on. If you have a custom name for the page, be sure to change this whenever you load.
When reading from a Cell or Range, you can either use the helper functions above, or define the range yourself with your own text block. Below are examples of ReadCell and ReadRange using both a GetCellReference and a user defined text for the reference.
Finally, there is the ReadSheet block, which will simply return all the data in the spreadsheet with the provided sheetName.
Each read operation has its own event block that handles the return value.
As a quick note, the cellData is a text value, rowData and colData are both lists of text values, and rangeData and sheetData are 2D lists, or lists where each element in the list corresponds to a list of text, one for each row.
Write Blocks
In addition to the Read operations, there are blocks for assigning data to a row, column, cell or range as well. These are the Write Blocks. They require similar inputs as the ReadBlock, but this time, they need the values to be assigned. The shape of the data to be assigned should be in the same format as the data that is returned in the GotData blocks shown just above. That is, cells need a single text value, rows and columns need a list of strings, and ranges need a list of lists of strings. Below are examples for how these are used.
Similar to the Read block, each of the Write operations has a callback event, which is called whenever the write call has finished updating the Google Sheets document. This allows you to update any information on the app which depends on the datasheet being updated.
Add and Remove Rows and Columns
In addition to Read and Write blocks, one can append a new row or column from the spreadsheet using the AddRow and AddCol blocks. You can also remove a row or column from the spreadsheet using the RemoveRow and RemoveCol blocks as well. Each of these operations has their own callback event block as well, along with the number of the row or column that was just added.
For example, to add a new Row, and remove the first row, you can call the following blocks.
Notice that the RemoveRow and RemoveBlocks require the gridId instead of the sheetName. This is due to a requirement of the Google Sheets API. The gridId serves the same purpose as the sheetName, directing the API to the page you wish to operate on in the Google Sheets document, but this gridId is unique to the page and cannot be changed by the user, whereas the sheetName can be changed by the user.
Query Block
For advanced users, the ReadWithQuery block may be particularly interesting. This block uses the Google Query Language, a language similar to SQL for those who are familiar with it. This block requires that the Google Sheets document is shared so that anyone with the link has the permission to at least view the data.
This means that you can request subsets of your data, such as all rows where the username matches ‘Johnothan1999’, or where the score is above 100. For example, let’s say that the Google Sheet has the following pieces of data.
The following block will retrieve the data in the second, third, and fourth columns (aka columns labelled B,C, and D) for the rows where the second column matches “John1999.”
The response will be a list of lists, similar to the result of the GetRange block. Each list in the response represents the column labels if one exists, and the rows which matched the criteria. In this example, the response contains two rows with the three requested columns, the first being the labels and the second row being [“John1999”, “John”, “10”].
More information on the Google Query Language syntax can be found here: https://developers.google.com/chart/interactive/docs/querylanguage?hl=en
ErrorOccured
Another block that was implemented was an ErrorOccured event block, which will catch any error from the Sheets component and store the stack trace as a string in the errorMessage value.
Thank you for your time. Any feedback is highly appreciated.
Best,
Tommy Heng
Taifun.
