Hmm. I'll see what I can find out. I don't actually know a lot about the Emulator companion. I may well have posted the wrong thing. Sorry about that.
I have updated the test server and have a new Companion to download. Most of the change are in the Companion, so be sure you have deleted the old one and loaded the newest one.
I have been unable to reproduce your problems with added rows and columns. Would you mind sending me the aia of your project? Is your sheet visible to anyone with the link? I wonder if there might be an issue with the component behaving differently with sheets that require credentials and sheets that don't.
I believe the issues you had with ReadSheet are intended behavior from the Google Sheets API. Google Sheets is a spreadsheet rather than a database, and it doesn't know what the last column is. To determine where to add column data, I'm calculating the max column number with any data in it on my side. I may have to just include those caveats in the documentation.
In google apps script, I use the getDataRange() call on a sheet, which returns the entire data table (including empty rows, empty last cells). It seems that the component is not making this call, when required....
Does this mean you have the Companion working?
No,
just an observation on how the component appears to be (mis) behaving....
Oh, I see. Yes, the api call for retrieving a sheet is not getDataRange(). Interesting that that call returns the trailing empty cells. I'm not sure if I can use that api call for retrieving a sheet, since I won't know the data range. I'll play around with it.
1 Like
OK, so the reason you're getting the error when adding a column is that you have actually removed all the available columns from your sheet. It appears that the API cannot handle that. It is expecting a spreadsheet with plenty of available columns but only a few in use.
I see that the Add Column menu option in Google Sheets can add a column to your spreadsheet, but the API call can't. It looks like the API is not adding a column in the database sense but writing to the first empty column.
I'll look at the API to see if there is a way to add a column to a spreadsheet with no available empty columns. This may be a documentation issue rather than a bug.
I've confirmed that when I add a row to your spreadsheet, the first column filled is the third one instead of the first one. That is not happening on my spreadsheet. My spreadsheet is not shared, so I assume that's the difference. Looking into this too.
ETA: I'm wrong. It has nothing to do with the permissions. It has to do with the arrangement of the data on the spreadsheet. Still looking.
1 Like
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
This works
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.
1 Like
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...
1 Like
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)
2 Likes
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.