Built In Google Sheets Component for App Inventor

Hello All,

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.

The test server for this component can be found here: http://sheets-dot-ai2-ewpatton-temp.uc.r.appspot.com/

Below is a tutorial called the PizzaParty app which 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”

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.

image

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 GetWithQuery 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

13 Likes

hmm, cool :+1: :smiley: :+1:

1 Like

Well done Tommy, some great work here. A couple of questions:

  1. Are the credentials required for only private/view only spreadsheets? If the spreadsheet is anyone can view then will read work but write won’t? If the spreadsheet is public read and write are credentials.json needed?

  2. From reading your instructions, the spreadsheet is still created and hosted on the developers google drive, yes? Any plans to allow users to connect to their own google drive and create their own spreadsheets? (I have been working on this…

https://community.appinventor.mit.edu/t/web-app-to-connect-user-to-their-own-google-drive-and-all-that-entails-for-ai2/2495/13

1 Like

This is great news! I will test when I have time

great work!

Which means, the Remove methods need the gridId and Google does not provide a method to get the gridId from the sheetName? Is that understanding correct?

To be consistent with the Add, Read and Write methods would it probably be possible to use the gridId there, too? Like this a user could rename the sheet names in the spreadsheet and the app still would work…

let me suggest to use separate blocks, i.e. AfterWritingCol, AfterWritingCell, AfterWritingRange, AfterWritingRow to avoid nested if-elseif-else statements in the AfterWriting block

Taifun


Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.

2 Likes

The procedureName parameter is horribly named. I would not regret its loss. I went looking in the blue procedures leg of the builtin blocks palette for which procedure was connected to the writing to the Sheet, and came up empty. It took a while to guess that the value would be a member of the list (‘WriteCol’, ‘WriteRow’, ‘WriteCell’, ‘WriteRange’), namely the methodName.

By all means, drop the parameter and split the event into 4 (or more) events matching the possible triggering methods. The BLE extension does this well, with ReceivedStrings, ReceivedBytes, etc.

It is no small accomplishment to storm the gates of Google and come back with sheet access blocks.

But I want more loot.

  • A csv download block for an entire sheet
  • Column ranges, like A:C to request all rows, columns A to C
  • HLOOKUP and VLOOKUP
  • Filters

Do you use https://developers.google.com/sheets/api/reference/rest ?

1 Like

Regarding the WriteComplete event block, could the InsertRow equivalent return the row number of the newly inserted row? That would help if I were to have inserted a formula like =(sum of column 2 where column 1 = ‘ABG’) and wanted to see the result.
Or is this hopeless, because I can’t insert formulae and expect them to act like formulae ?

1 Like

Thank you for the feedback! I will be changing the event to match the possible triggering methods based on what I’ve heard so far. :slightly_smiling_face:

Thank you for the comments Tim!

To answer the first question:

Are the credentials required for only private/view only spreadsheets? If the spreadsheet is anyone can view then will read work but write won’t? If the spreadsheet is public read and write are credentials.json needed?

As of right now, you will need the credentals.json for all the API calls. However, if it is public, you wouldn’t need to share the Google Sheets doccument with the Service Account. This means if the Google Sheets document is public to read from, you can use the read blocks to access the inforrmation, and if it is public to edit, you can use the write blocks.

As for the second question:

From reading your instructions, the spreadsheet is still created and hosted on the developers google drive, yes? Any plans to allow users to connect to their own google drive and create their own spreadsheets? (I have been working on this…

At the moment, I’m prioritizing allowing the developer to create their own datastore to substitute for the functionality of Fusion Tables, with some additional functionality. More likely then not, we may not get around to creating a new Google Sheets document on the user’s Google account, although it’s not out of the question.

For now, the closest thing to creating and using a Google Sheets documnet on the user’s account would be to have the user create their own Sheets, make it public to read/write, and the app can use the user’s spreadsheetId as the source, relying on similar principles as the answer to the first question.

Thanks!
Tommy

I agree that it is weird for the discrepency between some blocks using grid id and sheetName. I will continue to look into whether I can determine one given the other, but I remain at the mercy of the Google Sheets Java API. I’ve been referencing mainly the pages around here.

  • Assuming that the csv download block is simply a Read block that will return a csv, I can implement this.
  • The column range can be indirectly covered by providing the text “A:C” to the ReadRange block. If a separate block that will construct this is what you would like, I can definitely add this.
  • I haven’t thought of HLOOKUP and VLOOKUP blocks before. I’m not familiar with the functionality but I will look into it.
  • Filters are definitely something I plan on adding to the Google Sheets component. Making sure that it is in a user friendly format will be the tricky part.

I’ve actually been using the Google Sheets API for Java. Mostly referencing pages around here.

I can definitely add this feature. I plan on creating a 1-1 correspondence, along the lines of FinishedWriteCell and FinishedAddRow. For “FinishedAddRow” and “FinishedAddCol” I can provide the number of the newly added row/column.

You should be able to enter formulae and it will evaluate on the sheet appropriately. When using the API, one would normally have the option between reading/writing the raw value (e.g. formulae) or as a user-inputted value (e.g. reading the result of the formulae rather than the actual formulae themselves). I opted to force the latter.

Thanks again for the feedback and suggestions!

I downloaded http://sheets-dot-ai2-ewpatton-temp.uc.r.appspot.com/companions/Emulator.apk
and got wrong emulator apk version
yet the help says
About

Should I be worried about the version difference?

The Cell reference converter blocks works fine for the column name conversion:
APizzaSheet.aia (3.7 KB)

a few years ago I played around with the Google Visualisation API Query Language, which offers SQLish query possibilities, see also https://puravidaapps.com/spreadsheet.php#select

examples
select C,D where B matches ‘Taifun’
select name where salary > 700
select dept, max(salary) group by dept
select lunchTime, avg(salary), count(age) group by isSenior, lunchTime

I would be great to have something like this…
I myself prefer to follow the KISS principle, Keep it simple, stupid… whih means from my point of view, don’t worry too much about a “user friendly format”, keep it simple, i.e. just provide a possibility to send a sql statement in text format as it is rather than complicating things…

Taifun

1 Like

Wow - the timing is perfect. Thank you. :slight_smile:

Restricting the blocks to such a low level (row and column numbers only)
without any kind of server side index or search is too ToHuBoHu.

where to get Google Sheets Component?

1 Like

it is in development and only available on a test server... (this is the Open Source Development category...), also that component could still change a lot until it will be released...

Taifun


Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.

1 Like

After hearing some feedback, some additions had been made to the Google Sheets component.

  1. A one-to-one correspondence for the callback blocks has been added.
  2. The catch-all AfterWriting event has been removed.
  3. The callback event blocks for AddCol and AddRow provide the columnNumber and rowNumbers.
  4. An SQL-ish Query Block which follows the Google Query Language syntax. Requires the document be available to anyone with a link, and is the only API block which does NOT require a credentials.json.
  5. A new block that allows you to fetch the entire Google Sheet page at once as a list of lists.

I look forward to hearing your thoughts!
Tommy H

1 Like

looks great!
you also might want to add the gridId as default here...


Taifun