search

SCRT-HQ / PSGSuite

Powershell module for Google / G Suite API calls wrapped in handy functions. Authentication is established using a service account via P12 key to negate the consent popup and allow for greater handsoff automation capabilities
https://psgsuite.io/
Apache License 2.0
234 stars 66 forks source link

[Feature Request] Some additional Spreadsheet capabilities #254

Open FISHMANPET opened 4 years ago

FISHMANPET commented 4 years ago

I'm trying to migrate off of a custom brewed Powershell Module that goes against the REST API directly to using PSGsuite and there's a few specific bits of functionality around Spredhseets that it's currently missing that I've already implemented:

I'll volunteer to take this on. I'm not sure exactly how I'll implement this, I'll have to examine the current functions, how, they're currently interacting with the API, and how specific or general new functionality will be. I also see some spreadsheet requests in #125, I may contribute to whatever work has happened there as well.

Just opening this issue to "claim" the work and start a place for discussion about proposals I'll have once I actually start coding something

FISHMANPET commented 4 years ago

As I mentioned in the other issue I'm finally getting back to this... So first of all, I found a bug! (which I've fixed). If you used import-gssheet on a sheet where there was an extra cell of data beyond the header, it would throw an error. Here's an example that will fail if you try to import it. The fix is pretty simple, just snip each row of data so it's only as long as the number of columns you've got.

FISHMANPET commented 4 years ago

Also, I'm gonna take @WJurecki who I know is using spreadsheets and specifically the Add-GSSheetValues function. I'm wondering if there are strong feelings one way or the other on the append parameter? It's there now, so taking it out would break things but it just feels like a really weird way to interact with a sheet, and I'm wondering why anybody would ever want to append to existing table and just stick the header there in the middle.

I'm also wondering if maybe I'm just missing an easy way to construct the object to actually append. I'm used to going directly against the REST API, where you can just throw an array of values at it, and it will append them. And in fact it looks like that's what the code ends up doing eventually, but first it has to deconstruct the PSCustomObject that the function takes. Which seems a little silly, as I have to write a fair amount of code to construct that PSCustomObject from my array of data in the first place, seems there should be an easier way. Or maybe my use case (Appending daily stats to a spreadsheet) is different than what you had in mind when you wrote it?

FISHMANPET commented 4 years ago

I also found an inconsistency between the Add-GSSheetValues and Export-GSSheet. Add-GSSheetValues uses a builtin enum type for ValueInputOption, and the user entered type for that is USERENTERED but Export-GSSheet just does parameter validation on a string and USER_ENTERED is the correct value. I know the REST API accepts USER_ENTERED for the append function, so not sure what's going on here. It may be a weirdness in the Google Library but I'll take a look at it. At the very least the documentation is currently incorrect, as Add-GSSheetValues says it's USER_ENTERED but that value throws an error.

FISHMANPET commented 4 years ago

I also found an inconsistency between the Add-GSSheetValues and Export-GSSheet. Add-GSSheetValues uses a builtin enum type for ValueInputOption, and the user entered type for that is USERENTERED but Export-GSSheet just does parameter validation on a string and USER_ENTERED is the correct value. I know the REST API accepts USER_ENTERED for the append function, so not sure what's going on here. It may be a weirdness in the Google Library but I'll take a look at it. At the very least the documentation is currently incorrect, as Add-GSSheetValues says it's USER_ENTERED but that value throws an error.

Well looks like Batch Update Request doesn't have an ENUM at all, and I think there's some kind of limitation on Enum names that would prevent it having the same format as what the REST api takes...

Could probably write a bunch of gnarly and fragile code to map the user friendlier values to the enum values, but... maybe not worth it

FISHMANPET commented 2 years ago

merging in the sheets-batch-update branch brings all this functionality in