UMN-Google.psm1
|
############### # Module for interacting with Google API # More details found at https://developers.google.com/sheets/ and https://developers.google.com/drive/ # ############### ### # Copyright 2017 University of Minnesota, Office of Information Technology # This program is free software: you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation, either version 3 of the License, or # (at your option) any later version. # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # You should have received a copy of the GNU General Public License # along with Foobar. If not, see <http://www.gnu.org/licenses/>. ### function ConvertTo-Base64URL { <# .Synopsis convert text or byte array to URL friendly BAse64 .DESCRIPTION convert text or byte array to URL friendly BAse64 .EXAMPLE ConvertTo-Base64URL -text $headerJSON .EXAMPLE ConvertTo-Base64URL -Bytes $rsa.SignData($toSign,"SHA256") #> param ( [Parameter(ParameterSetName='String')] [string]$text, [Parameter(ParameterSetName='Bytes')] [System.Byte[]]$Bytes ) if($Bytes){$base = $Bytes} else{$base = [System.Text.Encoding]::UTF8.GetBytes($text)} $base64Url = [System.Convert]::ToBase64String($base) $base64Url = $base64Url.Split('=')[0] $base64Url = $base64Url.Replace('+', '-') $base64Url = $base64Url.Replace('/', '_') $base64Url } ################################################## OAuth ################################################################# function Get-GOAuthTokenUser { <# .Synopsis Get Valid OAuth Token. The access token is good for an hour, the refresh token is mostly permanent and can be used to get a new access token without having to reauthenticate .DESCRIPTION Long description .EXAMPLE You can provide multiple scopes for access to multiple APIs at the same time. They just need to be separated by a space $scope = "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive" provides access to spreadsheets and drive api. .EXAMPLE Another example of how to use this cmdlet #> [CmdletBinding()] [OutputType([array])] Param ( # projectID, appKey, and appSecret [Parameter(Mandatory)] [string]$projectID, # Name of API Project [Parameter(Mandatory)] [string]$appKey, # App key ID [Parameter(Mandatory)] [string]$appSecret, # App key secret [Parameter(Mandatory)] [string]$scope, ## example $scope = "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive" [Parameter(Mandatory)] [string]$redirectUri, ## example $redirectUri = "https://umn.edu" has to be set to https [string]$refreshToken ) Begin { } Process { ### If no refresh token - requires human interaction with IE if(!($refreshToken)) { ### Get Google API access - https://developers.google.com/identity/protocols/OAuth2WebServer#offline #$scope = "https://www.googleapis.com/auth/spreadsheets" $response_type = "code" $approval_prompt = "force" $access_type = "offline" ### Get the authorization code $auth_string = "https://accounts.google.com/o/oauth2/auth?scope=$scope&response_type=$response_type&redirect_uri=$redirectUri&client_id=$appKey&access_type=$access_type&approval_prompt=$approval_prompt" $ie = New-Object -comObject InternetExplorer.Application if($approval_prompt -eq "force"){$ie.visible = $true} $null = $ie.navigate($auth_string) #Wait for user interaction in IE, manual approval do{Start-Sleep 1}until($ie.LocationURL -match 'code=([^&]*)') $null = $ie.LocationURL -match 'code=([^&]*)' $authorizationCode = $matches[1] $null = $ie.Quit() ### exchange the authorization code for a refresh token and access token $grantType = "authorization_code" $requestUri = "https://accounts.google.com/o/oauth2/token" $requestBody = "code=$authorizationCode&client_id=$appKey&client_secret=$appSecret&grant_type=$grantType&redirect_uri=$redirectUri" $response = Invoke-RestMethod -Method Post -Uri $requestUri -ContentType "application/x-www-form-urlencoded" -Body $requestBody $props = @{ accessToken = $response.access_token refreshToken = $response.refresh_token } } ### If refresh token exists else { ### exchange the refresh token for an access token $grantType = "refresh_token" $requestUri = "https://accounts.google.com/o/oauth2/token" $requestBody = "refresh_token=$refreshToken&client_id=$appKey&client_secret=$appSecret&grant_type=$grantType" $response = Invoke-RestMethod -Method Post -Uri $requestUri -ContentType "application/x-www-form-urlencoded" -Body $requestBody $props = @{ accessToken = $response.access_token refreshToken = $refreshToken } } return new-object psobject -Property $props } End { } } function Get-GOAuthTokenService { <# .Synopsis Get google auth 2.0 token for a service account .DESCRIPTION Long description .EXAMPLE Get-GOAuthTokenService -scope "https://www.googleapis.com/auth/spreadsheets" -certPath "C:\users\$env:username\Desktop\googleSheets.p12" -certPswd 'notasecret' -iss "oit-automation@oit-mpt-powershell-sheets.iam.gserviceaccount.com" .EXAMPLE Another example of how to use this cmdlet #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$scope, [Parameter(Mandatory)] [string]$certPath, [Parameter(Mandatory)] [string]$certPswd, [Parameter(Mandatory)] [string]$iss ## Google service account email address ) Begin { } Process { # build JWT header $headerJSON = [Ordered]@{ alg = "RS256" typ = "JWT" } | ConvertTo-Json -Compress $headerBase64 = ConvertTo-Base64URL -text $headerJSON ## Claims ## Build date times needed in seconds $now = (Get-Date).ToUniversalTime() $iat = [Math]::Floor([decimal](Get-Date($now) -UFormat "%s")) $exp = [Math]::Floor([decimal](Get-Date($now.AddMinutes(59)) -UFormat "%s")) $aud = "https://www.googleapis.com/oauth2/v4/token" $claimsJSON = [Ordered]@{ iss = $iss scope = $scope aud = $aud exp = $exp iat = $iat } | ConvertTo-Json -Compress $claimsBase64 = ConvertTo-Base64URL -text $claimsJSON ################# Create JWT # Prep Cert #$certPath = "C:\users\$env:username\Desktop\googleSheets.p12" $googleCert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($certPath, $certPswd,[System.Security.Cryptography.X509Certificates.X509KeyStorageFlags]::Exportable ) # get just the private key $rsaPrivate = $googleCert.PrivateKey # get a new RSA provider $rsa = New-Object System.Security.Cryptography.RSACryptoServiceProvider # copy the parameters from the private key into our new rsa provider $null = $rsa.ImportParameters($rsaPrivate.ExportParameters($true)) # signature is our base64urlencoded header and claims, seperated by a . $toSign = [System.Text.Encoding]::UTF8.GetBytes($headerBase64 + "." + $claimsBase64) # sign the sig, we then serialize to UTF-8 bytes, then base64url encode the signature $signature = ConvertTo-Base64URL -Bytes $rsa.SignData($toSign,"SHA256") ## this needs to be converted back to regular text ## request $jwt = $headerBase64 + "." + $claimsBase64 + "." + $signature $fields = 'grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion='+$jwt $response = Invoke-RestMethod -Uri "https://www.googleapis.com/oauth2/v4/token" -Method Post -Body $fields -ContentType "application/x-www-form-urlencoded" $response.access_token } End { } } ################################################## SpreadSheets ################################################################# function Add-GSheetSheet { <# .Synopsis Add named pages/sheets to an existing document .DESCRIPTION This function will add a specified sheet name to a google spreadsheet. .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER sheetName Name to apply to new sheet .EXAMPLE #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$sheetName, [Parameter(Mandatory)] [string]$spreadSheetID, [Parameter(Mandatory)] [string]$accessToken ) Begin{} Process { $properties = @{requests=@(@{addSheet=@{properties=@{title=$sheetName}}})} |convertto-json -Depth 10 $suffix = "$spreadSheetID" + ":batchUpdate" $uri = "https://sheets.googleapis.com/v4/spreadsheets/$suffix" $ContentType = "application/json" Invoke-RestMethod -Method Post -Uri $uri -Body $properties -ContentType $ContentType -Headers @{"Authorization"="Bearer $accessToken"} } End{} } function Clear-GSheetSheet { <# .Synopsis Clear all data and leave formatting intact for a sheet from a spreadsheet based on sheetID .DESCRIPTION This function will delete data from a sheet .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER sheetName Name of sheet to clear .EXAMPLE $pageID = 0 ## using pageID to differentiate from sheetID -- In this case, index 0 is the actual sheetID per the API and will be deleted. $sheetID = ## the id number of the file/spreadsheet clear-gsheet -pageID $pageID -sheetID $sheetID -accessToken $accessToken #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$spreadSheetID, [Parameter(Mandatory)] [string]$sheetName, [Parameter(Mandatory)] [string]$accessToken ) 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" $ContentType = "application/json" Invoke-RestMethod -Method Post -Uri $uri -Body $properties -ContentType $ContentType -Headers @{"Authorization"="Bearer $accessToken"} } End{} } function Get-GSheetData { <# .Synopsis Basic function for retrieving data from a specificSheet in a Google SpreadSheet. .DESCRIPTION Basic function for retrieving data from a specificSheet in a Google SpreadSheet. .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER sheetName Name of sheet to data from .EXAMPLE #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$spreadSheetID, [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [string]$sheetName, ## 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 [string]$rangeA1, [Parameter(Mandatory)] [ValidateSet('AllData','Range')] [string]$cell, [Parameter()] [ValidateSet('FORMATTED_VALUE', 'UNFORMATTED_VALUE', 'FORMULA')] [string]$valueRenderOption = "FORMATTED_VALUE" ) Begin{} Process { $uri = "https://sheets.googleapis.com/v4/spreadsheets/$spreadSheetID/values/$sheetName" if($cell -eq "Range") { $uri += "!$rangeA1" } $uri += "?valueRenderOption=$valueRenderOption" $result = Invoke-RestMethod -Method GET -Uri $uri -Headers @{"Authorization"="Bearer $accessToken"} $sheet = $result.values $Rows = $sheet.Count $Columns = $sheet[0].Count $HeaderRow = 0 $Header = $sheet[0] foreach ($Row in (($HeaderRow + 1)..($Rows-1))) { $h = [Ordered]@{} foreach ($Column in 0..($Columns-1)) { if ($sheet[0][$Column].Length -gt 0) { $Name = $Header[$Column] $h.$Name = $Sheet[$Row][$Column] } } [PSCustomObject]$h } } End{} } function Get-GSheetSpreadSheetID { <# .Synopsis Get a spreadsheet ID. .DESCRIPTION Provide a case sensative sheet name to the function to get back the sheetID used in many other API calls. mimeTymes are split out to only retrieve spreadSheet IDs (no folders or other files) .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER fileName Name of file to retrive ID for, gernerally this is the same as the SpreadSheet Name. .EXAMPLE Case sensitivity - get-GSheetSpreadSheetID -FileName 'test' #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [Alias("spreadSheetName")] [string]$fileName, [Parameter(Mandatory)] [string]$accessToken ) Begin{} Process { $uri = "https://www.googleapis.com/drive/v3/files?q=name%3D'$fileName'" $spreadSheetID = (((Invoke-RestMethod -Method get -Uri $uri -Headers @{"Authorization"="Bearer $accessToken"}).files) | where {$_.mimetype -eq "application/vnd.google-apps.spreadsheet"}).id If ($spreadSheetID.count -eq 0){throw "There are no files matching the name $fileName"} If ($spreadSheetID.count -gt 1){Write-Warning "There are $($spreadSheetID.Count) files matching the provided name. Please investigate the following sheet IDs to verify which file you want.";return($spreadSheetID)} Else{return($spreadSheetID)} } End{} } function Get-GSheetSheetID { <# .Synopsis Get ID of specific sheet in a Spreadsheet .DESCRIPTION Get ID of specific sheet in a Spreadsheet .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER sheetName Name of sheet used to fetch the ID #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$spreadSheetID, [Parameter(Mandatory)] [string]$sheetName, [Parameter(Mandatory)] [string]$accessToken ) Begin{} Process { $spreadSheet = Get-GSheetSpreadSheetProperties -spreadSheetID $spreadSheetID -accessToken $accessToken ($spreadSheet.sheets.properties | Where-Object {$_.title -eq $sheetName}).sheetID } End{} } function Get-GSheetSpreadSheetProperties { <# .Synopsis Get the properties of a SpreadSheet .DESCRIPTION Get the properties of a SpreadSheet .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .EXAMPLE #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$spreadSheetID, [Parameter(Mandatory)] [string]$accessToken ) Begin{} Process { $uri = "https://sheets.googleapis.com/v4/spreadsheets/$spreadSheetID" Invoke-RestMethod -Method GET -Uri $uri -Headers @{"Authorization"="Bearer $accessToken"} } End{} } function Move-GSheetData { <# .Synopsis 'Move' data around .DESCRIPTION This is a cut and paste between sheets in a spreadsheet. The function will find the row index based on search criteria, and copy/paste between the sheets provided. .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .PARAMETER accessToken oAuth access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER currentSheetName Name of sheet to be searched, and copied from. .PARAMETER newSheetName Name of destination sheet data is to be written to. .PARAMETER query Value to be queried for in specified column (see columnKey) .PARAMETER columnKey Row 0 column value. A key to search for data by. .EXAMPLE $newSheetName = 'Decommissioned' $currentSheetName = 'Servers' $sheetID = get-GSheetID -FileName 'Server-Inventory' $columnKey = 'Server name' $query = 'server-dev1' $accessToken = Get-GOAuthTokenService -scope $scope -certPath $certPath -certPswd $certPswd -iss $iss move-gsheetData -sheetID $sheetID -accessToken $accessToken -CurrentSheetName $currentSheetName -newSheetName $newSheetName -query $query -columnKey $columnKey .EXAMPLE move-GSheetData -sheetID $sheetID -accessToken $accessToken -currentSheetName 'Servers' -newSheetName 'Decommissioned' -query 'Virt-vum-dev' -columnKey 'Server name' #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$spreadSheetID, [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [string]$sourceSheetName, [Parameter(Mandatory)] [string]$destinationSheetName, [Parameter(Mandatory)] [string]$query, [Parameter(Mandatory)] [string]$columnKey ) Begin{} Process { ## Query all data from sheet $data = Get-GSheetData -spreadSheetID $spreadSheetID -accessToken $accessToken -sheetName $sourceSheetName -cell AllData $destinationData = Get-GSheetData -spreadSheetID $spreadSheetID -accessToken $accessToken -sheetName $destinationSheetName -cell AllData ## Get row query belongs to $Index = (0..($data.count -1) | where {$Data[$_].$columnKey -eq $query}) ## Sanity Check - is this the data? if (-not $Index) { write-Warning "$Query in $columnKey does not exist" return $null } Else { $rowIndex = $index[0] + 2 $startRow = $Index[0] + 1 $destinationRow = ($destinationData).count + 2 $destinationStartRow = ($destinationData).count + 1 } ## Get sheet index ID numbers $allSheetProperties = (Get-GSheetSpreadSheetProperties -spreadSheetID $spreadSheetID -accessToken $accessToken).sheets.properties $srcSheetIndex = ($allSheetProperties | where {$_.title -eq $sourceSheetName}).sheetID $dstSheetIndex = ($allSheetProperties | where {$_.title -eq $destinationSheetName}).sheetID $method = 'POST' $uri = "https://sheets.googleapis.com/v4/spreadsheets/$spreadSheetID"+":batchUpdate" $ContentType = "application/json" ## cutPaste row to row $values = @{"cutPaste"=@{"source"=@{"sheetId"=$srcSheetIndex;"startRowIndex"=$startRow;"endRowIndex"=$rowIndex};"destination"=@{"sheetId"=$dstSheetIndex;"rowIndex"=$destinationRow};"pasteType"="PASTE_NORMAL"}} $JSON = @{"requests"=$values} |ConvertTo-Json -Depth 20 Invoke-RestMethod -Method $method -Uri $uri -Body $json -ContentType $ContentType -Headers @{"Authorization"="Bearer $accessToken"} } End{} } function New-GSheetSpreadSheet { <# .Synopsis Create a new Google SpreadSheet. .DESCRIPTION Create a new Google SpreadSheet. .PARAMETER title Use this in the simplest case to just create a new sheet with a Title/name .PARAMETER properties Alternatively, The properties that can be set are extensive. Cell color, formatting etc. If you use this you MUST include @{properties=@{title='mY sheet'}} |convertto-json at a minimum. More details at https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/create .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .EXAMPLE Example for setting the title of the sheet $properties = @{properties=@{title='my sheet'}} |convertto-json create-GSheet -properties $properties -accessToken $accessToken .EXAMPLE create-GSheet -title 'My sheet' -accessToken $accessToken #> [CmdletBinding()] Param ( [Parameter(ParameterSetName='title')] [string]$title, [Parameter(ParameterSetName='properties')] [array]$properties, [Parameter(Mandatory)] [string]$accessToken ) Begin { If (!$properties) { $properties = @{properties=@{title=$title}} |convertto-json } } Process { $uri = "https://sheets.googleapis.com/v4/spreadsheets" $ContentType = "application/json" Invoke-RestMethod -Method Post -Uri $uri -Body $properties -ContentType $ContentType -Headers @{"Authorization"="Bearer $accessToken"} } End{} } function Remove-GSheetSheet { <# .Synopsis Removes a sheet from a spreadsheet based on sheetID .DESCRIPTION This function will delete a sheet. .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER sheetName Name of sheet to delete .EXAMPLE #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$spreadSheetID, [Parameter(Mandatory)] [string]$sheetName, [Parameter(Mandatory)] [string]$accessToken ) Begin{} Process { $sheetID = Get-GSheetSheetIndex -accessToken $accessToken -spreadSheetID $spreadSheetID -sheetName $sheetName $properties = @{requests=@(@{deleteSheet=@{sheetId=$sheetID}})} |convertto-json -Depth 10 $suffix = "$spreadSheetID" + ":batchUpdate" $uri = "https://sheets.googleapis.com/v4/spreadsheets/$suffix" $ContentType = "application/json" $data = Invoke-RestMethod -Method Post -Uri $uri -Body $properties -ContentType $ContentType -Headers @{"Authorization"="Bearer $accessToken"} } End { return([array]$data) } } function Remove-GSheetSpreadSheet { <# .Synopsis Delete a SpreadSheet .DESCRIPTION Delete a SpreadSheet .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .EXAMPLE #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$spreadSheetID, [Parameter(Mandatory)] [string]$accessToken ) Begin{} Process { $uri = "https://www.googleapis.com/drive/v3/files/$spreadSheetID" Invoke-RestMethod -Method Delete -Uri $uri -Headers @{"Authorization"="Bearer $accessToken"} } End{} } function Set-GSheetColumnWidth { <# .Synopsis Set the width of a column on a sheet .DESCRIPTION This function will set dimensions to 'autoResize' .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER sheetName Name of sheet in spreadSheet .PARAMETER numberOfColumns An optional parameter to specify how many columns to autosize. Default to 26 .EXAMPLE Set-GSheetColumnWidth -spreadSheetID $id -sheetName 'Sheet1' -accessToken $token -numberOfColumns ($property.count) #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$spreadSheetID, [Parameter(Mandatory)] [string]$sheetName, [Parameter(Mandatory)] [string]$accessToken, [string]$numberOfColumns = '26' ) Begin{ $sheetID = Get-GSheetSheetID -accessToken $accessToken -spreadSheetID $spreadSheetID -sheetName $sheetName $json = @{requests=@(@{autoResizeDimensions=@{dimensions=@{sheetId=$sheetID;dimension='COLUMNS';startIndex='0';endIndex='26'}}})} |ConvertTo-Json -Depth 20 $suffix = "$spreadSheetID" + ":batchUpdate" $uri = "https://sheets.googleapis.com/v4/spreadsheets/$suffix" } Process { Invoke-RestMethod -Method Post -Uri $uri -Body $json -ContentType "application/json" -Headers @{"Authorization"="Bearer $accessToken"} } End{} } function Set-GSheetData { <# .Synopsis Set values in sheet in specific cell locations or append data to a sheet .DESCRIPTION Set values in sheet in specific cell locations or append data to a sheet .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER sheetName Name of sheet to set data in .EXAMPLE Set-GSheetData -sheetID '1LvqbZSTlgQNIBC2bkv9Ze6nFWPBY98ASI2_sMc1DQSE' -accessToken $accessToken -sheetName 'Sheet1' -rangeA1 'A19:B20' -values @(@("a","b"),@("c","D")) .EXAMPLE Another example of how to use this cmdlet #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$spreadSheetID, [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [string]$sheetName, ## 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(ParameterSetName='set')] [string]$rangeA1, [Parameter(ParameterSetName='Append')] [switch]$append, ## This shoudl be an array or arrays. Each internal array represents one ROW [Parameter(Mandatory)] [System.Collections.ArrayList]$values, [string]$valueInputOption = 'RAW' ) Begin{} Process { 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" } $json = @{values=$values} | ConvertTo-Json ###### uncomment the following two lines for debug #$uri #$json $ContentType = "application/json" Invoke-RestMethod -Method $method -Uri $uri -Body $json -ContentType $ContentType -Headers @{"Authorization"="Bearer $accessToken"} } End{} } function Set-GSheetSpreadSheetPermissions { <# .Synopsis Set Permissions on Google Sheet .DESCRIPTION Set Permissions on Google Sheet .PARAMETER emailAddress Email address of the user or group to grant permissions to .PARAMETER spreadSheetID The sheetID to apply permissions to. This is returned when a new sheet is created or use Get-GSheetID .PARAMETER role Role to assign, select from 'writer','reader','commenter' .PARAMETER type This refers to the emailAddress, is it a user or a group .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .EXAMPLE set-GSheetPermissions -emailAddress 'user@email.com' -role writer -sheetID $sheetID -type user #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$emailAddress, ## email address of user or group to be shared with #[Alias("fileID")] [Parameter(Mandatory)] [string]$spreadSheetID, [ValidateSet('writer','reader','commenter')] [string]$role = "writer", [ValidateSet('user','group')] [string]$type, [Parameter(Mandatory)] [string]$accessToken ) Begin{} Process { $fileID = $spreadSheetID $json = @{emailAddress=$emailAddress;type=$type;role=$role} | ConvertTo-Json $ContentType = "application/json" $uri = "https://www.googleapis.com/drive/v3/files/$fileID/permissions" Invoke-RestMethod -Method post -Uri $uri -Body $json -ContentType $ContentType -Headers @{"Authorization"="Bearer $accessToken"} } End{} } ########################################################################################################################## Export-ModuleMember -Function * |