Modify Google Sheets (API) Using PowerShell / Uploading CSV Files

Hey michael,

Welcome back! It’s generally just permissions related as is covered in the article (giving someone access by email address, etc.). You can actually create a “free tier” Google API account with your personal account to access it but it’s generally not too painful. Transferring documents in and out from the service account is a little tougher.

However, there’s a caveat you may be running into in your situation. A lot of organizations that have enterprise-level agreements/contracts with Google severely restrict giving sharing permissions out to “work” documents at an enterprise level. This is to prevent both intentional and unintentional leaking of documents. I think it’s moving toward being the default setting for new “accounts” or “deployments” if it isn’t already.

I ran into this a lot at my previous position which was in state government and when I left all my Google documents I’d ever touched were immediately locked away and inaccessible even to the accounts I’d granted permissions to. I wasn’t allowed to give permissions to my personal Gmail account as the organizational level had locked it down to specific approved organizations and *definitely* not personal accounts. This was not unique to me and is the state-wide policy for every state employee from the bottom to the top. I strongly suspect you may be running into intentional permissions walls like this. A lot of the time it may have not even been requested and may just be how the account was initially configured without much thought being put into it (especially for smaller organizations but certainly not exclusively).

You’ll definitely want to use the Google API web console to see what kind of errors it’s throwing as the return codes from PowerShell (or even the API calls themselves) are extremely vague and the console will at least give you a short description of what errors are coming up. You can use the same trick to get the document ID of just loading it in the browser to get the spreadsheet IDs but getting the API will help to see if you’re allowed to do this.

You may want to try sharing documents from within the official Google interfaces like sheets.google.com or any of the apps and see what happens with something like a personal Gmail account. If you have enterprise permissions restrictions you should get them in the web interface in a much nicer way.

It might be easier to come up with a “Import-Spreadsheet” function or something like that and actually just have the service account recreate the spreadsheets (by just reading them and uploading the parsed data theoretically) and then have it programmatically add all of the users (usually by email) you want to have access to it.

As far as doing this from within PowerShell or from code/using the API if we take a look here there is such a thing as a Move-GFile function as well as Get-GFile, Get-GFileID and some others that could really come in handy for that.

If you want to go really deep I did also find this. This is in reference to both accessing files inside a shared domain (organizational) service account AND sharing it with THEM (which is part of what you want to do here). In short, you will have to “impersonate” users which is not as nefarious as it sounds and is in the technical sense (your OS processes impersonate/masquerade as other processes (usually the parent but not always) all the time such as svchost, etc).

It’s basically highly advanced authentication/permissions but if you have a need to do this by API those resources should help give you an idea of what it will take (won’t be easy but is possible). You would need to use the official Google API reference and build the API commands as there are no PowerShell skeleton functions for us to draw from in UMN-Google to do this finely-grained level of authentication/impersonation/etc.

Here’s the official Google resource on service account impersonation (the REST API is what we are technically using so choose the “REST” pane in the documentation and the code will look the most familiar / be most applicable to UMN-Google). At a rough glance it looks like you’d need to make some more permissions changes such as granting the ability to create impersonation tokens and rework the API commands to call for impersonation as the service account once the permissions are configured correctly to allow it.

It’s not easy, but it’s possible and I linked to some Python tools earlier to apparently do it and I assume there are other ways as well outside of the official REST API. That’s definitely entering the highly advanced zone for sure and reaching the limits of my knowledge though. I really think the challenge is going to be the organizational permissions but hopefully all that helps!

Hey Mike,

Great question! The easiest way (and the way I always used to be honest) is to add permissions for yourself to edit the spreadsheet on the computer. Once you make the changes (like bolding the headers, setting the column sizes, anything visual) when you update the columns the formatting changes will still stay in place.

UMN-Google doesn’t have any built in way to do this. Can it be done programmatically though? Yes it can. You would just need to make the requests yourself by modifying the UMN-Google request template code by referencing the Google API (I will demonstrate where to go / how to do this).

If you have the need to do it programatically check this out first keeping in mind we’ll be using it as a reference to build some commands and you don’t need to read it all line for line. This is the Google API developer’s documentation on using formatting in Google Sheets. Don’t be too overwhelmed yet until our next step.

Next I would recommend just copying UMN-Google’s code into your own module and adding some new methods to add style. You could also just completely ditch UMN-Google and handle everything in your own code (it doesn’t do too much).
You can refer to UMN-Google to see the structure of how to do this here.

Ok, now that you’ve seen both the UMN-Google functions and structure and how to do the formatting you can put these together to make a new request. Let’s look at the code for Clear-GSheetSheet from UMN-Google as an example:

[CmdletBinding()]
Param
(
[Parameter(Mandatory)]
[string]$accessToken,

[Parameter(Mandatory)]
[string]$sheetName,

[Parameter(Mandatory)]
[string]$spreadSheetID

)

Begin{}
Process
{
$sheetID = Get-GSheetSheetID -accessToken $accessToken -spreadSheetID $spreadSheetID -sheetName $sheetName
$properties = @{requests=@(@{updateCells=@{range=@{sheetId=$sheetID};fields="userEnteredValue"}})} |ConvertTo-Json -Depth 10
$suffix = "$spreadSheetID" + ":batchUpdate"
$uri = "https://sheets.googleapis.com/v4/spreadsheets/$suffix"
Invoke-RestMethod -Method Post -Uri $uri -Body $properties -ContentType 'application/json' -Headers @{"Authorization"="Bearer $accessToken"}
}
End{}
}

Now do you see the properties above embedded in the JSON? Those are what you would add/adjust to based on the first Google API documentation document I linked to so that the parameters and properties matches what Google expects. It would theoretically be pretty easy to modify one of these existing “batchUpdate” commands and add in the color/style formatting. Here’s a brief example from the same API page:

"requests": [
{
"repeatCell": {
"range": {
"sheetId": sheetId,
"startRowIndex": 0,
"endRowIndex": 10,
"startColumnIndex": 0,
"endColumnIndex": 1
},
"cell": {
"userEnteredFormat": {
"numberFormat": {
"type": "DATE",
"pattern": "hh:mm:ss am/pm, ddd mmm dd yyyy"
}
}
},
"fields": "userEnteredFormat.numberFormat"
}

You could modify your own requests with this format and other formats from the same Google API developer’s documentation I linked to (tons and tons of different options are available). The example code doesn’t add any of these special extra JSON properties when we build the ArrayList and pretty much just submits pure data but additional properties are available in the JSON that aren’t being used.

This option will either be relatively easy (you’ll just be adapting/running with the existing UMN-Google framework and modifying the parameters to include your styling code) or very difficult depending on your experience level (it’s going to be hard to adapt it in the way I’m saying if it all looks like an alien language) and will take a decent amount of time either way but it’s definitely possible if you have an absolute requirement.

Unfortunately this isn’t already built into UMN-Google but the option is still available to just preformat your documents in the browser (just give yourself access by adding access to your email) but the groundwork has definitely been laid to expand upon pretty easily and send some custom formatting code along with your spreadsheet data to the Google API (it will basically be in JSON requests just like your current data is now but as additional properties). Hopefully that helps!

Hey AndyO,

Absolutely! I think the problem is with your range. The range should basically be the top left corner (A1) down to the bottom right corner (UX) but I see (U1) in your range so it may not be counting your rows correctly (I use a fancy row counting trick in my code to do this for the example) and if they’re always the same you could probably put a static # in there. A1 to U1 would only be the top row being posted and that won’t match the size of your ArrayList which almost certainly has x number of rows and not just a single row. This will trigger a (vague but much less vague than PowerShell) error in the Google API console.

Now look at this output from mine here:

PS Y:\James\PowerShell\GoogleSheets> y:\James\PowerShell\GoogleSheets\Test-Sheets.ps1
VERBOSE: PUT https://sheets.googleapis.com/v4/spreadsheets/1anI7Bdr0ZjkSDwMbGpCb0KZc1SpSC-3JdQiycvoM5CM/values/Computers!A1:C3?valueInputOption=RAW with -1-byte payload
VERBOSE: received -1-byte response of content type application/json; charset=UTF-8

spreadsheetId : 1anI7Bdr0ZjkSDwMbGpCb0KZc1SpSC-3JdQiycvoM5CM
updatedRange : Computers!A1:C3
updatedRows : 3
updatedColumns : 3
updatedCells : 9

There’s virtually nothing that can go wrong other than giving it the wrong credentials / wrong range. I would try setting your last range value to the number of rows like U(X) which in the examples I actually calculate this (if you have this calculation still in there it may be getting the wrong count somehow since it appears to only be picking “1” instead of x rows). You also will want to log into the Google API console because you will get no errors from PowerShell (other than 400 failed basically with no reason given). You have to go to the console to see them (almost certainly invalid range errors).

Look at the code behind UMN-Google to understand why and how little is actually happening here:

function Set-GSheetData
{
<# .Synopsis Set values in sheet in specific cell locations or append data to a sheet .DESCRIPTION Set json data values on a sheet in specific cell locations or append data to a sheet .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER append Switch option to append data. See rangeA1 if not appending .PARAMETER rangeA1 Range in A1 notation https://msdn.microsoft.com/en-us/library/bb211395(v=office.12).aspx . The dimensions of the $values you put in MUST fit within this range .PARAMETER sheetName Name of sheet to set data in .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .PARAMETER valueInputOption Default to RAW. Optionally, you can specify if you want it processed as a formula and so forth. .PARAMETER values The values to write to the sheet. This should be an array list. Each list array represents one ROW on the sheet. .EXAMPLE Set-GSheetData -accessToken $accessToken -rangeA1 'A1:B2' -sheetName 'My Sheet' -spreadSheetID $spreadSheetID -values @(@("a","b"),@("c","D")) .EXAMPLE Set-GSheetData -accessToken $accessToken -append 'Append'-sheetName 'My Sheet' -spreadSheetID $spreadSheetID -values $arrayValues #>
[CmdletBinding()]
Param
(
[Parameter(Mandatory)]
[string]$accessToken,

[Parameter(ParameterSetName='Append')]
[switch]$append,

[Parameter(ParameterSetName='set')]
[string]$rangeA1,

[Parameter(Mandatory)]
[string]$sheetName,

[Parameter(Mandatory)]
[string]$spreadSheetID,

[string]$valueInputOption = 'RAW',

[Parameter(Mandatory)]
[System.Collections.ArrayList]$values
)

Begin
{
if ($append)
{
$method = 'POST'
$uri = "https://sheets.googleapis.com/v4/spreadsheets/$spreadSheetID/values/$sheetName"+":append?valueInputOption=$valueInputOption"
}
else
{
$method = 'PUT'
$uri = "https://sheets.googleapis.com/v4/spreadsheets/$spreadSheetID/values/$sheetName!$rangeA1"+"?valueInputOption=$valueInputOption"
}
}

Process
{
$json = @{values=$values} | ConvertTo-Json
Invoke-RestMethod -Method $method -Uri $uri -Body $json -ContentType "application/json" -Headers @{"Authorization"="Bearer $accessToken"}
}

End{}
}

That’s it. That’s all that is in UMN-Google. You could actually implement that one line (Invoke-RestMethod) and completely replace UMN-Google altogether. It does nearly nothing other than put your parameters into Google otherwise you are in control 100% (and you could easily make it actually 100% by just building the URI / json yourself in a couple of prep lines like the UMN-Google wrapper and calling Invoke-RestMethod).

Once I looked at the UMN-Google source code and understood exactly what it was doing and that it was basically just a single pre-built REST template it got a lot easier to develop sheets with it and gave me a lot more confidence knowing that somehow my command / ArrayList wasn’t built correctly etc. and that UMN-Google wasn’t doing anything wrong or overly complex behind the scenes. It also helped me understand the Google API model and that with this API almost nothing is actually happening in PowerShell and you could open up your web browser / a test tool like postman and feed this command in and it would still work if you had the right token. This is a very thin communication/messaging layer and all the “action” is happening inside Google API.

UMN-Google is mostly just a convenience wrapper so you don’t have to manually build all the REST URIs and JSON and on top of that know all the Google parameters each command has to take etc. but behind the scenes it’s just passing what you give it to Google through these preassembled REST URIs.

It’s almost always going to be range issues / input errors (with the occasional trying to set a column that doesn’t exist yet or a curveball like that / restriction you may have not been aware of, all the more reason to start using the Google API console as you’ll never guess it in a million years without it if you get one of the weird ones). The only exception has been the person who doesn’t have English has their computer’s default language and they posted a fix for that. Otherwise when I say there is nothing to UMN-Google I literally mean there’s nothing to it. A single line that posts whatever you give it to Google!

The console is available here then click “View Metrics”. It’s all just inputting the right # of rows/columns/etc. and building the ArrayList and you’re going to need to use the console to troubleshoot this. Otherwise you’re blind and just guessing without the console. Tricky for sure, but the console will tell you what is going wrong!

It’s just a one liner though that posts the data so it’s going to be fundamentals like the range for sure because the code is just a one liner that sends your parameters to the Google API and it’s either a valid request or it isn’t basically as far as PowerShell is concerned and only Google will be able to tell you more about why they think your request was invalid and they rejected it. Hopefully that gets it for you!