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. 
2 Likes
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 
yet the help says
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 App Inventor Tutorials and Examples: Google Spreadsheet Database | Pura Vida Apps
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. 
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.
- A one-to-one correspondence for the callback blocks has been added.
- The catch-all AfterWriting event has been removed.
- The callback event blocks for AddCol and AddRow provide the columnNumber and rowNumbers.
- 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.
- 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
2 Likes
Is this the query syntax page?
https://support.google.com/docs/answer/3093343?hl=en
or
https://developers.google.com/chart/interactive/docs/querylanguage
?
I'm having trouble trying to get to the equivalent of an SQL UPDATE WHERE statement.
The only update operation available relies on knowing the row and column number of what you want to update.
We get (now) the row number of a freshly inserted row, but how do we get to that from another context, like updating the delivery status of a pizza order 20 minutes after initial order creation?
Databases like Oracle have a ROWID pseudocolumn you can SELECT, but not Google Sheets.
This may require a double step ADD operation, where column A is reserved for a copy of the row number of the newly ADDed row, and is updated in place immediately after the ADD returns the row number.
(I looked for an Index function in the query function language, that might return a row number by cell value, to no avail.)
Even this workaround requires us to never delete a row, for fear of breaking all following row number references.
On further thought, maybe here's a workaround, a long forgotten SQL trick:
Use an ascending datetimestamp as a primary key, and derive the row number of a given datetimestamp by taking the count of rows whose datetimestamp column is less than or equal to the given datetimestamp value. That should give us the current row number of the row with the given datetimestamp, regardless of row deletions.
Oh nice catch!. That is already a default value, but I accidentally deleted the number block. It comes with the default gridId in the test server. I'll update the screenshot right now. Thanks.
It should be the latter link.
As far as I know, there is no equivalent to SQL's update in Google's Query Language
Another workaround that you can employ would be to reserve one column, such as column A, to always be the equation "=ROW()". This will always be the row number of the cell and will also automatically update when you remove a row.
P.S. I forgot to mention this but the test server also has a ReadSheet block and GotSheetData event block in response to the request for:
I've updated the post to descrive this block under the Read Blocks header.
2 Likes
Hi! I am working on this! I do not see GoogleSheets1 under Storage. Do I have to update appInventor? Or import an extension? Thanks for any help you can give me!
Hi @ccdragos8,
You need to use the test server linked in the original post. Please be aware that this is just for testing and the API may change significantly and in potentially incompatible ways before it's released to production.
I have successfully added rows and retrieved row IDs.
I have also added the =row() formula successfully in my Add Row operation to act as a primary key for other tables' use as a foreign key.
However, I am having trouble adding more complex formulas, for example =sumif() against a table of order items matching this row number.
Here's the project and evidence. I am halfway between completing order row extra summation columns, and adding new order items.
APizzaSheet.aia (9.7 KB) APizzaSheet-b5f57497646b.json (2.3 KB)
I tried two variants of what to supply as a =sumif formula, one with single quotes around the row number test, and the other with double quotes, which works when manually inserted. Neither can be posted to a cell.
I shared the sheet for edit, so you can play.
Please don't mess up order row 2, my carefully crafted sumif formula.
Here are a couple of arrayformulas you can use, place in the header row:
For your rowID numbers (Orders:OrderNumbers, Menu:Menu Row, etc.)
={"header";arrayFormula(if(B2:B<>"",row(A2:A)-1,""))}
For your OrderTotalEstimate in Orders
={"OrderTotalEstimate";arrayFormula(if(A2:A<>"",sumif(OrderItems!$B:$B,A2:A, OrderItems!$H:$H),""))}
I have left these in place on the Orders sheet columns J & K if you want to use them
Note that the order numbers one will start at 1.
When using array forumlas it is best to remove all unused rows.