I have put up a new version of the Google Sheets component at http://gcloud.srlane-test.uc.r.appspot.com (note new link).
I believe your app works now, though testing may have produced changes to your datasheet caused me to miss something you were testing.
Thanks so much for your help.
Hello @Susan_Lane, these are my tests:
1.- Test 1
ReadCol.conNumber
This works, I can get a column with empty middle cells.
[["This is cell A1"],[""],[""],[""],["Célula A5"],["Hello"],["AAAA"]]
2.- Test 2
AddRow. make a list: AA AddRow, BB AddRow, , DD AddRow
3.- Test 3.
I get error
4.- Test 4
I get
[["This is cell A1"],[""],["Célula A5"],["Hello"],["AAAA"]]
I expected to get:
[["This is cell A1"],[""],[""],[""],["Célula A5"],["Hello"],["AAAA"]]
OK.
I talked to #3 previously, though it may not have been clear. The reason this is erroring out is that the Sheets API is not actually inserting a column. It's expecting an unbounded spreadsheet and finding the first column containing no data. If you use Google Sheets itself to insert a blank column, then your test will work.
The API does have methods that can actually insert a column in the way you're expecting, but I think in the interests of making the component available, we are going to document the expectations and create an issue for future work to handle the creation of an actual new column.
I'll see if I can do anything to handle the error message more gracefully.
#4 -- I'm reviewing the SelectWithQuery method. You're correct that we're not getting enough empty fields, but since this component is built in a data table paradigmn rather than a spreadsheet, I'm not sure that the current behavior of returning data from a single column makes sense anyway.
Thanks again for this help.
Correct @Susan_Lane , if the last column is empty, the values are inserted.
AddCol is similar to WriteCol.
The advantage of WriteCol is that it does not give an error when we click a second time, that is, if we execute AddCol to an empty column twice, we get an error, but with WriteCol we can execute it several times and it does not give an error.
Write Col overwrites existing data. Add Col finds the first empty column and writes data there. If it does not find an empty column, it returns an error.
If you supply a column number that does not exist to Write Col, it will return an error.
Hi!
I’m a bit confused about what the test server does for this component.
Will people need to deploy their own instance of this server to use the component ? Or there will be a shared public instance ?
If the later, will mean that all the sheet information will be readable by anyone with access to that server ?
This is a shared public instance.
The test server is only for testing the component and finding bugs and not for production use...
Sorry, what I meant is: does the test server call the google api with your service account credentials ? Basically does it proxy the calls to read/write data ?
The test server provides a platform for testing the google sheets component. You should not use this for anything else (e.g. developing apps, production, sensitive data), other than testing the functionality of the google sheets components (and of course providing feedback)
You are allowed to do so, but for providing feedback.
It allows you to enter them but be careful about the bugs...
I believe I made a mistake and overwrote this test server with a different development branch.
Give me a moment to fix it.
I have updated the test server (note the link in the original post), the Companion, and the Emulator Companion.
The ReadWithQuery functionality was dropped from the Google Sheets API after the component was originally written. That sent me back to the drawing board.
In place of ReadWithQuery, there are now two blocks: ReadWithExactFilter and ReadWithPartialFilter. These allow you to query against a single column in the spreadsheet to either exactly match or contain a substring parameter.
Both of these blocks return both a list of row numbers and the list of row data. You need row numbers for other blocks, but previously there was no way to query for those numbers.
I tested against @Juan_Antonio 's very useful test application. It has not yet been tested against sheets with restricted access.
I know @TIMAI2 reports that the Emulator I posted previously didn't work. I haven't had a chance to investigate what the problem is. I updated it just in case.
I am leaving on vacation for two weeks and will be able to return to this when I get back.
I think (hope) we're getting close.
component works fine, will it be added in future release,
else you can provided this component as extension as we might use it in the latest version of mit ai2.
or
@Susan_Lane make sure the test server keeps running until the component gets released in the Stable version of MIT AI2, as I and many developers have the projects saved in the http://sheets-dot-ai2-ewpatton-temp.uc.r.appspot.com/ because the downloaded AIA files also cannot be imported as it says "unable find the component "GoogleSheets" "
Thanks for extension suggestions, only thing that make me to stick with Google Sheets API based component is the speed of read and write, with API it's 1-1.5 seconds, with Apps Script, it takes minimum 3 seconds
Be interested to understand your "speed" test. What size dataset do you use, or what query/function are you calling?
Just read an entire sheet data with 10 column and 10 rows, you can check yourself while you perform this with apps script and Google Sheets Component, just use a button and label, use a timer label to check speed
Since you have tested this maybe you can provide the information so others can learn.
