*obj/GoogleSheets won't open spreadsheet

[pwhirley]

I have successfully authenticated with Google Workspace (*obj/GoogleSheets) and I have created the sheets object.  I have successfully logged in with sheets'Login().
The scopes for my app are ../auth/drive and ../auth/spreadsheets, which should give me full read/write access to both my drive and my sheets.

When I try either sheets'OpenSpreadsheetByID(SheetID$) or sheets'OpenSpreadsheetByPath(SheetPath$) the return is zero, indicating that it was not successful.  I have tried all sorts of changes but I have no idea why it is not opening the spreadsheet

Is there any error information available that will tell me why it is failing to open my spreadsheet?  All I need to do is grab the data from a range of cells, but sheets'READ$("J1:K31") failed with an error 42, which I presume is because there is no spreadsheet open to read from.

Any help would be appreciated.  It would be great if I could trace through the logic in the GoogleSheets.pvc, but apparently I am not authorized.

Thanks - Phil

[pwhirley]

I was finally able to create a new spreadsheet then open a test one. 

I tried again to open the original sheet I need to work with, but it still fails with no indication of the reason.

This spreadsheet was shared with me and is, therefore, not actually in my drive.  I did not think this would make a difference to the API, but I'm unable to open the shared spreadsheet.

Any suggestions?

Thanks - Phil

[Devon Austen]

When you setup Google sheets API access that has granted you access to your spreadsheets but not to spreadsheets created by others and shared with you. So you can only access your own sheets via *obj/GoogleSheets.

This is a Google security decision and not up to us at PVX Plus Technologies.

If you want to access this shared spreadsheet via *obj/GoogleSheets then you need to have API access to that shared spreadsheet. Here is a article I found discussing this issue with some ideas.

https://stackoverflow.com/questions/61483396/how-can-i-access-google-sheet-dataits-shared-with-me-only-read-access-via-a

[pwhirley]

Thanks Devon,

I had figured that out and have read the same article.   The solution, of course, is to use a "service account", which is a much better option, anyway.  Sadly, the PxPlus Google Sheets interface doesn't seem to support access with a service account.  I have written a Python script to read the spreadsheet and save the necessary content to a file so that my PxPlus program can pick it up.  It's awkward but it works.

What are the chances I could get the password to the PxPlus programs so that I could copy them and enhance them to use the service account JSON file.  I'd much prefer to keep everything in PxPlus rather than using Python or C#.

Thanks - Phil

[Devon Austen]

Are you trying to login to the Google Sheets object using the service account credentials (i.e. client id and secret)?

If the issue is during login() then you can already access that code. The *obj/GoogleSheets object uses the *obj/oauth2 object for authentication with oAuth2'Service$="google" set. The code for *obj/oauth2 is visible so you can walk though it and modify it. The object does call a oauth2 agent that runs on a web server this is by default pointing at one we run on our server but you can point it at your own copy and we provide the code for our version in the lib *web/services/oauth2/agent. More info about that object can be found here: https://manual.pvxplus.com?utilities/obj_oauth2.htm

[pwhirley]

Thanks again, Devon.  I am trying to authenticate with a service account, which does not have a client ID and secret.  It has a bunch of other values to pass in.  Those two elements only apply to a regular login.  Every example I've found in other languages for logging in with a service account use the Google-Auth library, which isn't available for PxPlus.

Knowing the the OAuth2 object is available for me to view is good.  I will investigate that route.  If I can use that logic to separately authenticate with a service account I can then use the returned token to instantiate the GoogleSheets object.  That will work nicely.

Phil