100% Fail rate on Sheets API

[Explore-OR]

Describe the bug Using the update-GSSheet cmdlet produces a 100% failure rate on attempts to Update-GSSheet only discovered through Gsuite Admin console. Aside from exported array information not appearing on targeted sheet, determining the issue requires looking up API failures.

To Reproduce Steps to reproduce the behavior:

1. Create Array
2. Pick a sheet
3. Try to update sheet with array values

Expected behavior An indication of the API failure. Using -verbose or -debug switches does not reveal any more context for failures.

The service account can load sheets data without issue but writing to sheets seems to fail.

Screenshots If applicable, add screenshots to help explain your problem.

Environment (please complete the following information):

• OS: Server 2016
• PowerShell Version: 5
• PSGSuite Version: 2.36.4

Additional context Service account has https://www.googleapis.com/auth/drive permission within API controls.

[FISHMANPET]

Can you share what exact errors you're getting? Also, what kind of values are you trying add to the sheet? Right now it expects an array of hashtables, rather than an array of values directly, I think to be consistent with the way the Export-GSSheet cmdlet takes data. It might make sense to modify the functionality of Update-GSSheet to take arrays of values instead of hash tables, but that would be a breaking change that we'd have to think about how to implement sanely.

[Explore-OR]

I was initially trying to pass an array of values of results from a process onto a sheet. This failed so instead I went back to just trying to copy a set of values taken from one sheet (Get-GSSheet) and just update the same sheet for matching properties just trying to append the sheet.

After a powershell restart error reporting finally returned and it makes the issue pretty obvious (always restart). I've checked the PSgsuite service account and it has the more permissive ' https://www.googleapis.com/auth/drive' within API client access. Should the 'Sheets' be explicitly allowed as well?

Add-GSSheetValues : Exception calling "Execute" with "0" argument(s): "Google.Apis.Requests.RequestError The caller does not have permission [403] Errors [ Message[The caller does not have permission] Location[ - ] Reason[forbidden] Domain[global] ]

Thanks for your help and more broadly, the creation of this project.

On Wed, Apr 22, 2020 at 11:26 AM Peter Bajurny notifications@github.com wrote:

Can you share what exact errors you're getting? Also, what kind of values are you trying add to the sheet? Right now it expects an array of hashtables, rather than an array of values directly, I think to be consistent with the way the Export-GSSheet cmdlet takes data. It might make sense to modify the functionality of Update-GSSheet to take arrays of values instead of hash tables, but that would be a breaking change that we'd have to think about how to implement sanely.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/SCRT-HQ/PSGSuite/issues/281#issuecomment-617951161, or unsubscribe https://github.com/notifications/unsubscribe-auth/APJP3RSTY2SVV45BVIPUIKDRN4ZEPANCNFSM4MOMJKNA .

[scrthq]

Hey @Explore-OR - Looking at the source code, I think Update-GSSheet was pushed out accidentally; it's part of an upcoming release but still has a ton of work to do: https://github.com/SCRT-HQ/PSGSuite/blob/master/PSGSuite/Public/Sheets/Update-GSSheet.ps1

I'll get that cleaned up as soon as possible.

---

Seeing that it looks like you're looking for (and have found) Export-GSSheet though, that cmdlet is using that scope so that should be enough Sheets and Drive APIs are enabled on the Dev Console project too, right?

The user account you're using to push data to the Sheet also has Edit access to the Sheet?