scrthq
commented
4 years ago @FISHMANPET I like your first suggestion personally, given the expected behavior of what you'd pipe into the function in general. I'll hop on this :-)
Regarding the auth bit, Gmail, Drive, Sheets, and a couple other APIs require authentication as the user you're interacting with the API as via impersonation. Drive API does support an AsAdminUser switch on some of the API endpoints but that's more for general management e.g. providing access to a Shared Drive as you would in the Admin Console directly. I don't think Sheets API supports that for any of it's endpoints offhand, would need to check though.
Describe the bug When piping an array into Export-GSSheet, if the elements of that array have a 'User' property, those values will be passed into the
-Userparameter of the function one at a time, requiring to accept the oauth consent screen each time. This is because the User parameter hasValueFromPipelineByPropertyName = $truesetTo Reproduce Steps to reproduce the behavior:
You can run this example yourself, just specify your username and a sheet you have access to write to. In the first example, you'll see that for each user, it will build UserCredentials for that user (but still authenticate as the user whose configuration is currently loaded). You can see in ~/.scrthq/PSGSuite that a refresh token is created for each "user":
In the second two examples you'll see it authenticate as either the user you explicitly specify in the 2nd example, or the
AdminEmailin the second example.In this simple example, in the "broken" case, you'll have to go through the oauth consent process twice (once for each "user") and then it will write to your spreadsheet, and subsequent attempts with the same data won't require another oauth consent screen because the refresh token for each "user" will already be stored (though in reality it will still be logging in as you, or at least that was the behavior here using a Gsuite Standard project user). I discovered this initially with a dataset of about 160 users, which would have required me to accept the oauth consent 160 times before the data would actually be written. When my terminal would be stuck waiting to auth, ctrl+c wouldn't appear to kill it. What I would have to do is ctrl+c, then accept the current oauth consent, then it would terminate.
You've got the Authentication in
Processpart of the function, and the actual writing of the data in the End function, and when piping values into a function it will executeBeginonce,Processonce for each value piped in, then finally theEndpart once. So in this case it will setup authentication for each user piped in, then once it's gotten through all of that, it will write the data using the last user in the list.Expected behavior Data should be written to the spreadsheet as the current
AdminUserwhen no-Userflag is specifiedPotential Fixes?
ValueFromPipelineByPropertyName = $truefrom the User parameter. This might be some kind of breaking change, but I'm not sure the intention was ever to auth as every single user passed in. Maybe that's a pattern that makes sense for some account type functions that run as an admin where you want to change a setting for each user specified, but I'm not sure it's a useful pattern in the case of interacting with a spreadsheet.Processpart of the function, and possibly throw some kind of error if an array of Users is specified. This might also be a breaking change but again I'd say anyone using this function in this way would be exploiting a bug anyway. At the very least if the function does this, print out some kind of warning, because the odds of this being encountered accidentally are much higher than the odds that someone is deliberately passing multiple users.AdminEmailwhen using the ClientSecretsJson method of authentication? Without tracing it line by line, it appearsNew-GoogleServiceis using that passed in value of User when it stores the Token Response even though it's authenticating as theAdminEmailand not the specified User.