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/>. ### #region Dependancies function ConvertTo-Base64URL { <# .Synopsis convert text or byte array to URL friendly Base64 .DESCRIPTION Used for preparing the JWT token to a proper format. .PARAMETER bytes The bytes to be converted .PARAMETER text The text to be converted .EXAMPLE ConvertTo-Base64URL -text $headerJSON .EXAMPLE ConvertTo-Base64URL -Bytes $rsa.SignData($toSign,"SHA256") #> param ( [Parameter(ParameterSetName='Bytes')] [System.Byte[]]$Bytes, [Parameter(ParameterSetName='String')] [string]$text ) 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 } #endregion #region oAuth 2.0 #region Get-GOAuthTokenService function Get-GOAuthTokenService { <# .Synopsis Get google auth 2.0 token for a service account .DESCRIPTION This is used in server-server OAuth token generation .PARAMETER certPath Local or network path to .p12 used to sign the JWT token .PARAMETER certPswd Password to access the private key in the .p12 .PARAMETER iss This is the Google Service account address .PARAMATER scope The API scopes to be included in the request. Space delimited, "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive" .EXAMPLE Get-GOAuthTokenService -scope "https://www.googleapis.com/auth/spreadsheets" -certPath "C:\users\$env:username\Desktop\googleSheets.p12" -certPswd 'notasecret' -iss "serviceAccount@googleProjectName.iam.gserviceaccount.com" #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$certPath, [Parameter(Mandatory)] [string]$certPswd, [Parameter(Mandatory)] [string]$iss, [Parameter(Mandatory)] [string]$scope ) Begin { # build JWT header $headerJSON = [Ordered]@{ alg = "RS256" typ = "JWT" } | ConvertTo-Json -Compress $headerBase64 = ConvertTo-Base64URL -text $headerJSON } Process { # Build claims for JWT $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 JWT certificate signing $googleCert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($certPath, $certPswd,[System.Security.Cryptography.X509Certificates.X509KeyStorageFlags]::Exportable ) $rsaPrivate = $googleCert.PrivateKey $rsa = New-Object System.Security.Cryptography.RSACryptoServiceProvider $null = $rsa.ImportParameters($rsaPrivate.ExportParameters($true)) # Signature is our base64urlencoded header and claims, delimited by a period. $toSign = [System.Text.Encoding]::UTF8.GetBytes($headerBase64 + "." + $claimsBase64) $signature = ConvertTo-Base64URL -Bytes $rsa.SignData($toSign,"SHA256") ## this needs to be converted back to regular text # Build request $jwt = $headerBase64 + "." + $claimsBase64 + "." + $signature $fields = 'grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion='+$jwt # Fetch token $response = Invoke-RestMethod -Uri "https://www.googleapis.com/oauth2/v4/token" -Method Post -Body $fields -ContentType "application/x-www-form-urlencoded" } End { return $response.access_token } } #endregion #region Get-GOAuthTokenUser function Get-GOAuthTokenUser { <# .Synopsis Get Valid OAuth Token. .DESCRIPTION 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. .PARAMETER appKey The google project App Key .PARAMETER appSecret The google project application secret .PARAMETER projectID The google project ID .PARAMETER redirectUri An https project redirect. Can be anything as long as https .PARAMETER refreshToken A refresh token if refreshing .PARAMATER scope The API scopes to be included in the request. Space delimited, "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive" .EXAMPLE Get-GOAuthTokenUser -appKey $appKey -appSecret $appSecret -projectID $projectID -redirectUri $redirectUri -scope $scope .EXAMPLE Get-GOAuthTokenUser -appKey $appKey -appSecret $appSecret -projectID $projectID -redirectUri $redirectUri -scope $scope -refreshToken $refreshToken .NOTES Requires GUI with Internet Explorer to get first token. #> [CmdletBinding()] [OutputType([array])] Param ( [Parameter(Mandatory)] [string]$appKey, [Parameter(Mandatory)] [string]$appSecret, [Parameter(Mandatory)] [string]$projectID, [Parameter(Mandatory)] [string]$redirectUri, [string]$refreshToken, [Parameter(Mandatory)] [string]$scope ) Begin { $requestUri = "https://accounts.google.com/o/oauth2/token" } Process { if(!($refreshToken)) { ### Get the authorization code - IE Popup and user interaction section $auth_string = "https://accounts.google.com/o/oauth2/auth?scope=$scope&response_type=code&redirect_uri=$redirectUri&client_id=$appKey&access_type=offline&approval_prompt=force" $ie = New-Object -comObject InternetExplorer.Application $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 $requestBody = "code=$authorizationCode&client_id=$appKey&client_secret=$appSecret&grant_type=authorization_code&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 } } else { # Exchange the refresh token for new tokens $requestBody = "refresh_token=$refreshToken&client_id=$appKey&client_secret=$appSecret&grant_type=refresh_token" $response = Invoke-RestMethod -Method Post -Uri $requestUri -ContentType "application/x-www-form-urlencoded" -Body $requestBody $props = @{ accessToken = $response.access_token refreshToken = $refreshToken } } } End { return new-object psobject -Property $props } } #endregion #region Get-GOAuthIdToken function Get-GOAuthIdToken { <# .Synopsis Get Valid OAuth ID token for a user. .DESCRIPTION The ID token is signed by google to represent a user https://developers.google.com/identity/sign-in/web/backend-auth. .PARAMETER clientID Client ID within app project .PARAMETER redirectUri An https project redirect. Can be anything as long as https .PARAMETER scope The API scopes to be included in the request. Space delimited, "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive" .EXAMPLE Get-GOAuthIdToken -clientID $clientID -scope $scope -redirectUri $redirectURI .NOTES Requires GUI with Internet Explorer to get first token. #> [CmdletBinding()] [OutputType([array])] Param ( [Parameter(Mandatory)] [string]$clientID, [Parameter(Mandatory)] [string]$redirectUri, [Parameter(Mandatory)] [string]$scope ) Begin { $requestUri = "https://accounts.google.com/o/oauth2/token" } Process { ### Get the ID Token - IE Popup and user interaction section $auth_string = "https://accounts.google.com/o/oauth2/auth?scope=$scope&response_type=token%20id_token&redirect_uri=$redirectUri&client_id=$clientID&approval_prompt=force" $ie = New-Object -comObject InternetExplorer.Application $ie.visible = $true $null = $ie.navigate($auth_string) #Wait for user interaction in IE, manual approval do{Start-Sleep 1}until($ie.LocationURL -match 'id_token=([^&]*)') $null = $ie.LocationURL -match 'id_token=([^&]*)' Write-Debug $ie.LocationURL $id_token = $matches[1] $null = $ie.Quit() return $id_token } End{} } #endregion #endregion #region Get-GFile function Get-GFile { <# .Synopsis Download a Google File. .DESCRIPTION Download a Google File based on a case sensative file or fileID. .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER fileName Name of file to retrive ID for. Case sensitive .PARAMETER fileID File ID. Can be gotten from Get-GFileID .PARAMETER outFilePath Path to output file including file name. .EXAMPLE Get-GFile -accessToken $accessToken -fileName 'Name of some file' .EXAMPLE Get-GFile -accessToken $accessToken -fileID 'ID of some file' .NOTES Written by Travis Sobeck #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [Parameter(ParameterSetName='fileName')] [string]$fileName, [Parameter(ParameterSetName='fileID')] [string]$fileID, [Parameter(Mandatory)] [string]$outFilePath #[string]$mimetype ) Begin{} Process { if ($fileName){$fileID = Get-GFileID -accessToken $accessToken -fileName $fileName} If ($fileID.count -eq 0 -or $fileID.count -gt 1){break} $uri = "https://www.googleapis.com/drive/v3/files/$($fileID)?alt=media" Invoke-RestMethod -Method Get -Uri $uri -Headers @{"Authorization"="Bearer $accessToken"} -OutFile $outFilePath } End{} } #endregion #region Get-GFileID function Get-GFileID { <# .Synopsis Get a Google File ID. .DESCRIPTION Provide a case sensative file name to the function to get back the gFileID used in many other API calls. .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER fileName Name of file to retrive ID for. Case sensitive .PARAMETER mimetype Use this to specify a specific mimetype. See google docs https://developers.google.com/drive/api/v3/search-parameters .EXAMPLE Get-GFileID -accessToken $accessToken -fileName 'Name of some file' .NOTES Written by Travis Sobeck #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [string]$fileName, [string]$mimetype ) Begin{} Process { $uri = "https://www.googleapis.com/drive/v3/files?q=name%3D'$fileName'" if ($mimetype){$fileID = (((Invoke-RestMethod -Method get -Uri $uri -Headers @{"Authorization"="Bearer $accessToken"}).files) | Where-Object {$_.mimetype -eq $mimetype}).id} else{$fileID = (((Invoke-RestMethod -Method get -Uri $uri -Headers @{"Authorization"="Bearer $accessToken"}).files)).id} # Logic on multiple IDs being returned If ($fileID.count -eq 0){Write-Warning "There are no files matching the name $fileName"} If ($fileID.count -gt 1){Write-Warning "There are $($fileID.Count) files matching the provided name. Please investigate the following sheet IDs to verify which file you want.";return($fileID)} Else{return($fileID)} } End{} } #endregion #region Permissions for Google Drive files function Get-GFilePermissions { <# .Synopsis Get Permissions on Google Drive File .DESCRIPTION Get Permission ID list on Google File .PARAMETER accessToken OAuth Access Token for authorization. .PARAMETER fileID The fileID to query. This is returned when a new file is created. .PARAMETER permissionID If specified will query only that specific permission for the file, rather than all permissions .PARAMETER DefaultFields If specified, will only query "default" rather than querying all fields of Permission object. Added primarily for backwards compatibility .EXAMPLE Get-GFilePermissions -accessToken $accessToken -fileID 'String of File ID' -permissionID 'String of Permission ID' .OUTPUTS If only a fileID, this will return an object with two properties, the first is kind, and will always be drive#permissionList The second will be permissions, which includes the individual permissions objects. Each one of these will have the same format as if a specific PermissionID was requested If a permissionID is also specified, only that specific permission will be returned. It will have a kind property of drive#permission as well as all properties of that specific permission. More details on the permission object available here: https://developers.google.com/drive/api/v2/reference/permissions #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, #[Alias("spreadSheetID")] [Parameter(Mandatory)] [string]$fileID, # Parameter help description [Parameter()] [string] $permissionID, # Parameter help description [Parameter()] [switch] $DefaultFields ) Begin { $uri = "https://www.googleapis.com/drive/v3/files/$fileID/permissions" if ($permissionID) { $uri += "/$permissionID" } if (-not $DefaultFields) { $uri += "/?fields=*" } $headers = @{"Authorization"="Bearer $accessToken"} } Process { write-host $uri Invoke-RestMethod -Method Get -Uri $uri -Headers $headers } End{} } function Move-GFile { <# .Synopsis Change parent folder metadata .DESCRIPTION A function to change parent folder metadata of a file. .PARAMETER accessToken OAuth Access Token for authorization. .PARAMETER fileID The fileID to move. .PARAMETER folderID The fileID of the new parent folder. .PARAMETER parentFolderID The fileID of the parentFolder. Optional parameter. root (My Drive) is assumed if not specified. .EXAMPLE MoveGFile -fileID 'String of File ID' -folderID 'String of folder's File ID' #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, #[Alias("spreadSheetID")] [Parameter(Mandatory)] [string]$fileID, [Parameter(Mandatory)] [string]$folderID, [string]$parentFolderID='root' ) Begin { $uriAdd = "https://www.googleapis.com/drive/v3/files/$fileID"+"?removeParents=$parentFolderID" $uriRemove = "https://www.googleapis.com/drive/v3/files/$fileID"+"?addParents=$folderID" $headers = @{"Authorization"="Bearer $accessToken"} } Process { Invoke-RestMethod -Method patch -Uri $uriAdd -Headers $headers Invoke-RestMethod -Method patch -Uri $uriRemove -Headers $headers } End{} } function Remove-GFilePermissions { <# .Synopsis Remove Permissions on Google Drive File .DESCRIPTION Remove Permission ID list on Google File .PARAMETER accessToken OAuth Access Token for authorization. .PARAMETER fileID The fileID to query. This is returned when a new file is created. .PARAMETER permissionsID The permission ID to be removed. See Get-GFilePermissions .EXAMPLE Remove-GFilePermissions -fileID 'String of File ID' -accessToken $accessToken -permissionID 'ID of the permission' .NOTES A successfull removal returns no body data. #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, #[Alias("spreadSheetID")] [Parameter(Mandatory)] [string]$fileID, [Parameter(Mandatory)] [string]$permissionID ) Begin { $uri = "https://www.googleapis.com/drive/v3/files/$fileId/permissions/$permissionId" $headers = @{"Authorization"="Bearer $accessToken"} } Process { Invoke-RestMethod -Method Delete -Uri $uri -Headers $headers } End{} } function Set-GFilePermissions { <# .Synopsis Set Permissions on Google File .DESCRIPTION For use with any google drive file ID .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER emailAddress Email address of the user or group to grant permissions to .PARAMETER fileID The fileID to apply permissions to. .PARAMETER role Role to assign, select from 'writer','reader','commenter' .PARAMETER sendNotificationEmail Boolean response on sending email notification. .PARAMETER type This refers to the emailAddress, is it a user or a group .EXAMPLE set-GFilePermissions -emailAddress 'user@email.com' -role writer -sheetID $sheetID -type user .NOTES Requires drive and drive.file API scope. #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [string]$emailAddress, #[Alias("spreadhSheetID")] [Parameter(Mandatory)] [string]$fileID, [ValidateSet('writer','reader','commenter')] [string]$role = "writer", [ValidateSet($true,$false)] [boolean]$sendNotificationEmail = $false, [ValidateSet('user','group')] [string]$type ) Begin{ $json = @{emailAddress=$emailAddress;type=$type;role=$role} | ConvertTo-Json $ContentType = "application/json" $uri = "https://www.googleapis.com/drive/v3/files/$fileID/permissions/?sendNotificationEmail=$sendNotificationEmail" $headers = @{"Authorization"="Bearer $accessToken"} } Process { Invoke-RestMethod -Method post -Uri $uri -Body $json -ContentType $ContentType -Headers $headers } End{} } function Update-GFilePermissions { <# .Synopsis Update Permissions on Google File .DESCRIPTION Update Permissions on Google File .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER fileID The sheetID to apply permissions to. This is returned when a new sheet is created or use Get-GSheetID .PARAMETER permissionID The permission ID of the entiry with permissions. Sett Get-GFilePermissions to get a lsit .PARAMETER role Role to assign, select from 'writer','reader','commenter','Owner','Organizer' .PARAMETER supportTeamDrives Boolean for TeamDrive Support .PARAMETER transferOwnership Update ownership of file to permission ID .EXAMPLE Update-GFilePermissions -emailAddress 'user@email.com' -role writer -fileID $sheetID -permissionID 'ID of the permission' .NOTES This is usefull for changing ownership. You cannot change ownership from non-domain to domain. #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, #[Alias("spreadSheetID")] [Parameter(Mandatory)] [string]$fileID, [Parameter(Mandatory)] [string]$permissionID, [ValidateSet('writer','reader','commenter','owner','organizer')] [string]$role = "writer", [ValidateSet($true,$false)] [string]$supportTeamDrives = $false, [ValidateSet($true,$false)] [string]$transferOwnership = $false ) Begin{ $json = @{role=$role} | ConvertTo-Json $ContentType = "application/json" $uri = "https://www.googleapis.com/drive/v3/files/$fileID/permissions/$permissionID/?transferOwnership=$transferOwnership" $headers = @{"Authorization"="Bearer $accessToken"} } Process { Invoke-RestMethod -Method Patch -Uri $uri -Body $json -ContentType $ContentType -Headers $headers } End{} } #endregion #region Spread Sheet API Functions #region Add-GSheetSheet function Add-GSheetSheet { <# .Synopsis Add named sheets to an existing spreadSheet file. .DESCRIPTION This function will add a specified sheet name to a google spreadsheet. .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER sheetName Name to apply to new sheet .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .EXAMPLE Add-GSheetSheet -accessToken $accessToken -sheetName 'NewName' -spreadSheetID $spreadSheetID #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [string]$sheetName, [Parameter(Mandatory)] [string]$spreadSheetID ) Begin { $properties = @{requests=@(@{addSheet=@{properties=@{title=$sheetName}}})} |convertto-json -Depth 10 } Process { $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{} } #endregion #region Clear-GSheetSheet 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 accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER sheetName Name of sheet to clear .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .EXAMPLE $pageID = 0 ## using pageID to differentiate from sheetID -- In this case, index 0 is the actual sheetID per the API and will be cleared. $sheetID = ## the id number of the file/spreadsheet clear-gsheet -pageID $pageID -sheetID $sheetID -accessToken $accessToken #> [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{} } #endregion #region Get-GSheetData function Get-GSheetData { <# .Synopsis Basic function for retrieving data from a specific Sheet in a Google SpreadSheet. .DESCRIPTION Basic function for retrieving data from a specific Sheet in a Google SpreadSheet. .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER cell Required switch for getting all data, or a subset of cells. .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 data from .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .PARAMETER valueRenderOption How the data is renderd. Switch option from formatted to unformatted data or 'formula' .EXAMPLE Get-GSheetData -accessToken $accessToken -cell 'AllData' -sheetName 'Sheet1' -spreadSheetID $spreadSheetID .EXAMPLE Get-GSheetData -accessToken $accessToken -cell 'Range' -rangeA1 'A0:F77' -sheetName 'Sheet1' -spreadSheetID $spreadSheetID #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [ValidateSet('AllData','Range')] [string]$cell, [string]$rangeA1, [Parameter(Mandatory)] [string]$sheetName, [Parameter(Mandatory)] [string]$spreadSheetID, [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"} # Formatting the returned data $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] if ($sheet[$row].count -gt ($column)) { $h.$Name = $Sheet[$Row][$Column] } else { $h.$Name = "" } } } [PSCustomObject]$h } } End{} } #endregion function Get-GSheetSheetID { <# .Synopsis Get ID of specific sheet in a Spreadsheet .DESCRIPTION Get ID of specific sheet in a Spreadsheet .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER sheetName The name of the sheet .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .EXAMPLE Get-GSheetSheetID -accessToken $accessToken -sheetName 'Sheet1' -spreadSheetID $spreadSheetID #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [string]$sheetName, [Parameter(Mandatory)] [string]$spreadSheetID ) Begin{} Process { $spreadSheet = Get-GSheetSpreadSheetProperties -spreadSheetID $spreadSheetID -accessToken $accessToken ($spreadSheet.sheets.properties | Where-Object {$_.title -eq $sheetName}).sheetID } End{} } #region Get-GSheetSpreadSheetID function Get-GSheetSpreadSheetID { <# .Synopsis Get a spreadsheet ID. .DESCRIPTION Provide a case sensative file 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. Case sensitive .EXAMPLE Get-GSheetSpreadSheetID -accessToken $accessToken -fileName 'Name of some file' #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [Alias("spreadSheetName")] [string]$fileName ) Begin{} Process { return (Get-GFileID -accessToken $accessToken -fileName $fileName -mimetype "application/vnd.google-apps.spreadsheet") } End{} } #endregion function Get-GSheetSpreadSheetProperties { <# .Synopsis Get the properties of a SpreadSheet .DESCRIPTION Get all 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]$accessToken, [Parameter(Mandatory)] [string]$spreadSheetID ) 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 between sheets in a spreadSheet. .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 accessToken oAuth access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER columnKey Row 0 column value. A key to search for data by. $columnKey = 'Column header' .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) $query = 'Cell Content' .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .EXAMPLE Move-GSheetData -accessToken $accessToken -columnKey 'Column Header -destinationSheetName 'New Sheet!' -query 'Cell Content' -sourceSheetName 'Old Sheet' -spreadSheetID $spreadSheetID #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [string]$columnKey, [Parameter(Mandatory)] [string]$destinationSheetName, [Parameter(Mandatory)] [string]$query, [Parameter(Mandatory)] [string]$sourceSheetName, [Parameter(Mandatory)] [string]$spreadSheetID ) 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 accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .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 title Use this in the simplest case to just create a new sheet with a Title/name .EXAMPLE Create-GSheet -properties $properties -accessToken $accessToken .EXAMPLE create-GSheet -title 'My sheet' -accessToken $accessToken #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [Parameter(ParameterSetName='properties')] [array]$properties, [Parameter(ParameterSetName='title')] [string]$title ) Begin { If (!$properties) { $properties = @{properties=@{title=$title}} |convertto-json } $uri = "https://sheets.googleapis.com/v4/spreadsheets" } Process { Invoke-RestMethod -Method Post -Uri $uri -Body $properties -ContentType "application/json" -Headers @{"Authorization"="Bearer $accessToken"} } End{} } function Remove-GSheetSheet { <# .Synopsis Removes a sheet from a spreadsheet based on sheetID .DESCRIPTION This function will delete an individual sheet. .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER sheetName Name of sheet to delete .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .EXAMPLE Remove-GSheetSheet -accessToken $accessToken -sheetName 'Name to delete' -spreadSheetID $spreadSheetID #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [string]$sheetName, [Parameter(Mandatory)] [string]$spreadSheetID ) Begin{} Process { $sheetID = Get-GSheetSheetID -accessToken $accessToken -sheetName $sheetName -spreadSheetID $spreadSheetID $properties = @{requests=@(@{deleteSheet=@{sheetId=$sheetID}})} |convertto-json -Depth 10 $suffix = "$spreadSheetID" + ":batchUpdate" $uri = "https://sheets.googleapis.com/v4/spreadsheets/$suffix" $data = Invoke-RestMethod -Method Post -Uri $uri -Body $properties -ContentType "application/json" -Headers @{"Authorization"="Bearer $accessToken"} } End { return([array]$data) } } function Remove-GSheetSpreadSheet { <# .Synopsis Delete a SpreadSheet .DESCRIPTION Uses the google File Drive API to delete a file. .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER fileID ID for the target file/spreadSheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .EXAMPLE Remove-GSheetSpreadSheet -accessToken $accessToken -spreadSheetID $spreadSheetID #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, #[Alias("spreadSheetID")] [Parameter(Mandatory)] [string]$fileID ) Begin{} Process { $uri = "https://www.googleapis.com/drive/v3/files/$fileID" Invoke-RestMethod -Method Delete -Uri $uri -Headers @{"Authorization"="Bearer $accessToken"} } End{} } #region Remove-GSheetSheetRowColumn function Remove-GSheetSheetRowColumn { <# .Synopsis Remove row(s) or column(s) .DESCRIPTION Remove row(s) or column(s) .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER startIndex Index of row or column to start deleting .PARAMETER endIndex Index of row or column to stop deleting .PARAMETER dimension Remove Rows or Columns .PARAMETER sheetName Name of sheet in spreadSheet .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .EXAMPLE Remove-GSheetSheetRowColumn -accessToken $accessToken -sheetName "Sheet1" -spreadSheetID $spreadSheetID -dimension ROWS -startIndex 5 -endIndex 10 #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [int]$startIndex, [Parameter(Mandatory)] [int]$endIndex, [Parameter(Mandatory)] [ValidateSet("COLUMNS", "ROWS")] [string]$dimension, [Parameter(Mandatory)] [string]$sheetName, [Parameter(Mandatory)] [string]$spreadSheetID ) Begin { $sheetID = Get-GSheetSheetID -accessToken $accessToken -spreadSheetID $spreadSheetID -sheetName $sheetName if ($startIndex -eq $endIndex){$endIndex++} } Process { $request = @{"deleteDimension" = @{"range" = @{"sheetId" = $sheetID; "dimension" = $dimension; "startIndex" = $startIndex; "endIndex" = $endIndex}}} $json = @{requests=@($request)} | ConvertTo-Json -Depth 20 $suffix = "$spreadSheetID" + ":batchUpdate" $uri = "https://sheets.googleapis.com/v4/spreadsheets/$suffix" write-verbose -Message $json Invoke-RestMethod -Method Post -Uri $uri -Body $json -ContentType "application/json" -Headers @{"Authorization"="Bearer $accessToken"} } End{} } #endregion function Set-GSheetColumnWidth { <# .Synopsis Set the width of a column on a sheet .DESCRIPTION This function calls the bulk update method to set column dimensions to 'autoResize'. .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER numberOfColumns An optional parameter to specify how many columns to autosize. Default to 26 .PARAMETER sheetName Name of sheet in spreadSheet .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .EXAMPLE Set-GSheetColumnWidth -spreadSheetID $id -sheetName 'Sheet1' -accessToken $token -numberOfColumns ($property.count) #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [string]$numberOfColumns = '26', [Parameter(Mandatory)] [string]$sheetName, [Parameter(Mandatory)] [string]$spreadSheetID ) 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 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{} } #region Set-GSheetDropDownList function Set-GSheetDropDownList { <# .Synopsis Set Drop Down List Data validation on cells in a column .DESCRIPTION Set Drop Down List Data validation on cells in a column .PARAMETER accessToken access token used for authentication. Get from Get-GOAuthTokenUser or Get-GOAuthTokenService .PARAMETER columnIndex Index of column to update .PARAMETER startRowIndex Index of row to start updating .PARAMETER endRowIndex Index of last row to update .PARAMETER values List of string values that the use can chose from in an array. Google API only takes strings .PARAMETER inputMessage A message to show the user when adding data to the cell. .PARAMETER showCustomUi True if the UI should be customized based on the kind of condition. If true, $values will show a dropdown. .PARAMETER sheetName Name of sheet in spreadSheet .PARAMETER spreadSheetID ID for the target Spreadsheet. This is returned when a new sheet is created or use Get-GSheetSpreadSheetID .EXAMPLE Set-GSheetDropDownList -accessToken $accessToken -startRowIndex 1 -endRowIndex 10 -columnIndex 9 -sheetName 'Sheet1' -spreadSheetID $spreadSheetID -inputMessage "Must be one of 'Public','Private Restricted','Private, Highly-Restricted'" -values @('Public','Private Restricted','Private, Highly-Restricted') #> [CmdletBinding()] Param ( [Parameter(Mandatory)] [string]$accessToken, [Parameter(Mandatory)] [int]$startRowIndex, [Parameter(Mandatory)] [int]$endRowIndex, [Parameter(Mandatory)] [int]$columnIndex, [Parameter(Mandatory)] [string]$sheetName, [Parameter(Mandatory)] [string]$spreadSheetID, [Parameter(Mandatory)] [string[]]$values, [string]$inputMessage, [boolean]$showCustomUi=$true ) Begin { $sheetID = Get-GSheetSheetID -accessToken $accessToken -spreadSheetID $spreadSheetID -sheetName $sheetName $valueList = [Collections.ArrayList]@() foreach ($value in $values){$valueList.Add(@{userEnteredValue=$value})} $validation = @{ setDataValidation = @{ range=@{sheetId = $sheetID;startRowIndex=$startRowIndex;endRowIndex=$endRowIndex;startColumnIndex=$columnIndex;endColumnIndex=($columnIndex+1)}; rule=@{ condition = @{ type= 'ONE_OF_LIST'; values=$valueList }; inputMessage=$inputMessage;strict=$true;showCustomUi=$showCustomUi } } } $json = @{requests=@($validation)} | ConvertTo-Json -Depth 20 $suffix = "$spreadSheetID" + ":batchUpdate" $uri = "https://sheets.googleapis.com/v4/spreadsheets/$suffix" $json $uri } Process { Invoke-RestMethod -Method Post -Uri $uri -Body $json -ContentType "application/json" -Headers @{"Authorization"="Bearer $accessToken"} } End{} } #endregion #endregion Export-ModuleMember -Function * # SIG # Begin signature block # MIIfBwYJKoZIhvcNAQcCoIIe+DCCHvQCAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB # gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR # AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUZ+P/LHFYS8oBhiZzg1aCFgND # /aqgghoTMIIEhDCCA2ygAwIBAgIQQhrylAmEGR9SCkvGJCanSzANBgkqhkiG9w0B # AQUFADBvMQswCQYDVQQGEwJTRTEUMBIGA1UEChMLQWRkVHJ1c3QgQUIxJjAkBgNV # BAsTHUFkZFRydXN0IEV4dGVybmFsIFRUUCBOZXR3b3JrMSIwIAYDVQQDExlBZGRU # cnVzdCBFeHRlcm5hbCBDQSBSb290MB4XDTA1MDYwNzA4MDkxMFoXDTIwMDUzMDEw # NDgzOFowgZUxCzAJBgNVBAYTAlVTMQswCQYDVQQIEwJVVDEXMBUGA1UEBxMOU2Fs # dCBMYWtlIENpdHkxHjAcBgNVBAoTFVRoZSBVU0VSVFJVU1QgTmV0d29yazEhMB8G # A1UECxMYaHR0cDovL3d3dy51c2VydHJ1c3QuY29tMR0wGwYDVQQDExRVVE4tVVNF # UkZpcnN0LU9iamVjdDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAM6q # gT+jo2F4qjEAVZURnicPHxzfOpuCaDDASmEd8S8O+r5596Uj71VRloTN2+O5bj4x # 2AogZ8f02b+U60cEPgLOKqJdhwQJ9jCdGIqXsqoc/EHSoTbL+z2RuufZcDX65OeQ # w5ujm9M89RKZd7G3CeBo5hy485RjiGpq/gt2yb70IuRnuasaXnfBhQfdDWy/7gbH # d2pBnqcP1/vulBe3/IW+pKvEHDHd17bR5PDv3xaPslKT16HUiaEHLr/hARJCHhrh # 2JU022R5KP+6LhHC5ehbkkj7RwvCbNqtMoNB86XlQXD9ZZBt+vpRxPm9lisZBCzT # bafc8H9vg2XiaquHhnUCAwEAAaOB9DCB8TAfBgNVHSMEGDAWgBStvZh6NLQm9/rE # JlTvA73gJMtUGjAdBgNVHQ4EFgQU2u1kdBScFDyr3ZmpvVsoTYs8ydgwDgYDVR0P # AQH/BAQDAgEGMA8GA1UdEwEB/wQFMAMBAf8wEQYDVR0gBAowCDAGBgRVHSAAMEQG # A1UdHwQ9MDswOaA3oDWGM2h0dHA6Ly9jcmwudXNlcnRydXN0LmNvbS9BZGRUcnVz # dEV4dGVybmFsQ0FSb290LmNybDA1BggrBgEFBQcBAQQpMCcwJQYIKwYBBQUHMAGG # GWh0dHA6Ly9vY3NwLnVzZXJ0cnVzdC5jb20wDQYJKoZIhvcNAQEFBQADggEBAE1C # L6bBiusHgJBYRoz4GTlmKjxaLG3P1NmHVY15CxKIe0CP1cf4S41VFmOtt1fcOyu9 # 08FPHgOHS0Sb4+JARSbzJkkraoTxVHrUQtr802q7Zn7Knurpu9wHx8OSToM8gUmf # ktUyCepJLqERcZo20sVOaLbLDhslFq9s3l122B9ysZMmhhfbGN6vRenf+5ivFBjt # pF72iZRF8FUESt3/J90GSkD2tLzx5A+ZArv9XQ4uKMG+O18aP5cQhLwWPtijnGMd # ZstcX9o+8w8KCTUi29vAPwD55g1dZ9H9oB4DK9lA977Mh2ZUgKajuPUZYtXSJrGY # Ju6ay0SnRVqBlRUa9VEwggTmMIIDzqADAgECAhBiXE2QjNVC+6supXM/8VQZMA0G # CSqGSIb3DQEBBQUAMIGVMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNV # BAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdv # cmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTEdMBsGA1UEAxMU # VVROLVVTRVJGaXJzdC1PYmplY3QwHhcNMTEwNDI3MDAwMDAwWhcNMjAwNTMwMTA0 # ODM4WjB6MQswCQYDVQQGEwJHQjEbMBkGA1UECBMSR3JlYXRlciBNYW5jaGVzdGVy # MRAwDgYDVQQHEwdTYWxmb3JkMRowGAYDVQQKExFDT01PRE8gQ0EgTGltaXRlZDEg # MB4GA1UEAxMXQ09NT0RPIFRpbWUgU3RhbXBpbmcgQ0EwggEiMA0GCSqGSIb3DQEB # AQUAA4IBDwAwggEKAoIBAQCqgvGEqVvYcbXSXSvt9BMgDPmb6dGPdF5u7uspSNjI # vizrCmFgzL2SjXzddLsKnmhOqnUkcyeuN/MagqVtuMgJRkx+oYPp4gNgpCEQJ0Ca # WeFtrz6CryFpWW1jzM6x9haaeYOXOh0Mr8l90U7Yw0ahpZiqYM5V1BIR8zsLbMaI # upUu76BGRTl8rOnjrehXl1/++8IJjf6OmqU/WUb8xy1dhIfwb1gmw/BC/FXeZb5n # OGOzEbGhJe2pm75I30x3wKoZC7b9So8seVWx/llaWm1VixxD9rFVcimJTUA/vn9J # AV08m1wI+8ridRUFk50IYv+6Dduq+LW/EDLKcuoIJs0ZAgMBAAGjggFKMIIBRjAf # BgNVHSMEGDAWgBTa7WR0FJwUPKvdmam9WyhNizzJ2DAdBgNVHQ4EFgQUZCKGtkqJ # yQQP0ARYkiuzbj0eJ2wwDgYDVR0PAQH/BAQDAgEGMBIGA1UdEwEB/wQIMAYBAf8C # AQAwEwYDVR0lBAwwCgYIKwYBBQUHAwgwEQYDVR0gBAowCDAGBgRVHSAAMEIGA1Ud # HwQ7MDkwN6A1oDOGMWh0dHA6Ly9jcmwudXNlcnRydXN0LmNvbS9VVE4tVVNFUkZp # cnN0LU9iamVjdC5jcmwwdAYIKwYBBQUHAQEEaDBmMD0GCCsGAQUFBzAChjFodHRw # Oi8vY3J0LnVzZXJ0cnVzdC5jb20vVVROQWRkVHJ1c3RPYmplY3RfQ0EuY3J0MCUG # CCsGAQUFBzABhhlodHRwOi8vb2NzcC51c2VydHJ1c3QuY29tMA0GCSqGSIb3DQEB # BQUAA4IBAQARyT3hBeg7ZazJdDEDt9qDOMaSuv3N+Ntjm30ekKSYyNlYaDS18Ash # U55ZRv1jhd/+R6pw5D9eCJUoXxTx/SKucOS38bC2Vp+xZ7hog16oYNuYOfbcSV4T # p5BnS+Nu5+vwQ8fQL33/llqnA9abVKAj06XCoI75T9GyBiH+IV0njKCv2bBS7vzI # 7bec8ckmONalMu1Il5RePeA9NbSwyVivx1j/YnQWkmRB2sqo64sDvcFOrh+RMrjh # JDt77RRoCYaWKMk7yWwowiVp9UphreAn+FOndRWwUTGw8UH/PlomHmB+4uNqOZrE # 6u4/5rITP1UDBE0LkHLU6/u8h5BRsjgZMIIE/jCCA+agAwIBAgIQK3PbdGMRTFpb # MkryMFdySTANBgkqhkiG9w0BAQUFADB6MQswCQYDVQQGEwJHQjEbMBkGA1UECBMS # R3JlYXRlciBNYW5jaGVzdGVyMRAwDgYDVQQHEwdTYWxmb3JkMRowGAYDVQQKExFD # T01PRE8gQ0EgTGltaXRlZDEgMB4GA1UEAxMXQ09NT0RPIFRpbWUgU3RhbXBpbmcg # Q0EwHhcNMTkwNTAyMDAwMDAwWhcNMjAwNTMwMTA0ODM4WjCBgzELMAkGA1UEBhMC # R0IxGzAZBgNVBAgMEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBwwHU2FsZm9y # ZDEYMBYGA1UECgwPU2VjdGlnbyBMaW1pdGVkMSswKQYDVQQDDCJTZWN0aWdvIFNI # QS0xIFRpbWUgU3RhbXBpbmcgU2lnbmVyMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8A # MIIBCgKCAQEAv1I2gjrcdDcNeNV/FlAZZu26GpnRYziaDGayQNungFC/aS42Lwpn # P0ChSopjNZvQGcx0qhcZkSu1VSAZ+8AaOm3KOZuC8rqVoRrYNMe4iXtwiHBRZmns # d/7GlHJ6zyWB7TSCmt8IFTcxtG2uHL8Y1Q3P/rXhxPuxR3Hp+u5jkezx7M5ZBBF8 # rgtgU+oq874vAg/QTF0xEy8eaQ+Fm0WWwo0Si2euH69pqwaWgQDfkXyVHOaeGWTf # dshgRC9J449/YGpFORNEIaW6+5H6QUDtTQK0S3/f4uA9uKrzGthBg49/M+1BBuJ9 # nj9ThI0o2t12xr33jh44zcDLYCQD3npMqwIDAQABo4IBdDCCAXAwHwYDVR0jBBgw # FoAUZCKGtkqJyQQP0ARYkiuzbj0eJ2wwHQYDVR0OBBYEFK7u2WC6XvUsARL9jo2y # VXI1Rm/xMA4GA1UdDwEB/wQEAwIGwDAMBgNVHRMBAf8EAjAAMBYGA1UdJQEB/wQM # MAoGCCsGAQUFBwMIMEAGA1UdIAQ5MDcwNQYMKwYBBAGyMQECAQMIMCUwIwYIKwYB # BQUHAgEWF2h0dHBzOi8vc2VjdGlnby5jb20vQ1BTMEIGA1UdHwQ7MDkwN6A1oDOG # MWh0dHA6Ly9jcmwuc2VjdGlnby5jb20vQ09NT0RPVGltZVN0YW1waW5nQ0FfMi5j # cmwwcgYIKwYBBQUHAQEEZjBkMD0GCCsGAQUFBzAChjFodHRwOi8vY3J0LnNlY3Rp # Z28uY29tL0NPTU9ET1RpbWVTdGFtcGluZ0NBXzIuY3J0MCMGCCsGAQUFBzABhhdo # dHRwOi8vb2NzcC5zZWN0aWdvLmNvbTANBgkqhkiG9w0BAQUFAAOCAQEAen+pStKw # pBwdDZ0tXMauWt2PRR3wnlyQ9l6scP7T2c3kGaQKQ3VgaoOkw5mEIDG61v5MzxP4 # EPdUCX7q3NIuedcHTFS3tcmdsvDyHiQU0JzHyGeqC2K3tPEG5OfkIUsZMpk0uRlh # dwozkGdswIhKkvWhQwHzrqJvyZW9ljj3g/etfCgf8zjfjiHIcWhTLcuuquIwF4Mi # KRi14YyJ6274fji7kE+5Xwc0EmuX1eY7kb4AFyFu4m38UnnvgSW6zxPQ+90rzYG2 # V4lO8N3zC0o0yoX/CLmWX+sRE+DhxQOtVxzhXZIGvhvIPD+lIJ9p0GnBxcLJPufF # cvfqG5bilK+GLjCCBawwggSUoAMCAQICEHJNXiAT1cKRQFXzfFSJVHEwDQYJKoZI # hvcNAQELBQAwfDELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAk1JMRIwEAYDVQQHEwlB # bm4gQXJib3IxEjAQBgNVBAoTCUludGVybmV0MjERMA8GA1UECxMISW5Db21tb24x # JTAjBgNVBAMTHEluQ29tbW9uIFJTQSBDb2RlIFNpZ25pbmcgQ0EwHhcNMTcxMjE0 # MDAwMDAwWhcNMjAxMjEzMjM1OTU5WjCByzELMAkGA1UEBhMCVVMxDjAMBgNVBBEM # BTU1NDU1MRIwEAYDVQQIDAlNaW5uZXNvdGExFDASBgNVBAcMC01pbm5lYXBvbGlz # MRgwFgYDVQQJDA8xMDAgVW5pb24gU3QgU0UxIDAeBgNVBAoMF1VuaXZlcnNpdHkg # b2YgTWlubmVzb3RhMSQwIgYDVQQLDBtDb21wdXRlciBhbmQgRGV2aWNlIFN1cHBv # cnQxIDAeBgNVBAMMF1VuaXZlcnNpdHkgb2YgTWlubmVzb3RhMIIBIjANBgkqhkiG # 9w0BAQEFAAOCAQ8AMIIBCgKCAQEAwk6kLE9u+tWv0JUkIJSn5pWfa09g6cqFLucC # XomNj9NYj8t+JfPna3gC6LHv3OQAUDHOoC5H+8N3ea7qVGYIiwPRHzXOGqG/tVai # U5s5hG3vBhfRX8W1/2g4/hpgeXUzrxYn/2c5SOGGy0MU1ZJyUSFEdsjXHEV7HXK4 # qmFGV9RJxtiLZH1qUldCglxcj7zw0QnUdG6oAxpwTCeVp057/WXbnIR8a0gPse+y # /new5+CBUGTAvrw6K2BrJQVsdIIVn/q+BbcZxh9PpeZfTtsi6lgkvy0bUWtl5sSp # d75+hvw4Sl3HAaWZtoWN7LPmbDbbVRO2Arv4doh4Chod4wJ5xQIDAQABo4IB2DCC # AdQwHwYDVR0jBBgwFoAUrjUjF///Bj2cUOCMJGUzHnAQiKIwHQYDVR0OBBYEFF4L # EhElVUvT8n5txOJSNAczooSAMA4GA1UdDwEB/wQEAwIHgDAMBgNVHRMBAf8EAjAA # MBMGA1UdJQQMMAoGCCsGAQUFBwMDMBEGCWCGSAGG+EIBAQQEAwIEEDBmBgNVHSAE # XzBdMFsGDCsGAQQBriMBBAMCATBLMEkGCCsGAQUFBwIBFj1odHRwczovL3d3dy5p # bmNvbW1vbi5vcmcvY2VydC9yZXBvc2l0b3J5L2Nwc19jb2RlX3NpZ25pbmcucGRm # MEkGA1UdHwRCMEAwPqA8oDqGOGh0dHA6Ly9jcmwuaW5jb21tb24tcnNhLm9yZy9J # bkNvbW1vblJTQUNvZGVTaWduaW5nQ0EuY3JsMH4GCCsGAQUFBwEBBHIwcDBEBggr # BgEFBQcwAoY4aHR0cDovL2NydC5pbmNvbW1vbi1yc2Eub3JnL0luQ29tbW9uUlNB # Q29kZVNpZ25pbmdDQS5jcnQwKAYIKwYBBQUHMAGGHGh0dHA6Ly9vY3NwLmluY29t # bW9uLXJzYS5vcmcwGQYDVR0RBBIwEIEOb2l0bXB0QHVtbi5lZHUwDQYJKoZIhvcN # AQELBQADggEBAENRlesMKmBaZ0g68lttYEMtaPiz+DaNpOlXBs1gH66aghB1aP6i # iRJcFVasGLUVFncdG1xbw503LTrBUc5PECMVDVF7KKCfHA1OeFV9vOWyvdVgbe3p # aDy1sj4CADO2D0gnxcGiZoFhEZiBkTvSsj4S3GXZEvoFHJxJLw2kvdLnzy0gH/b/ # b/yblwA1fKXw4locUpDM6qTwM7SiKgkQ5W7/280EYu8BI6c8rpiJmqM1tZLcpswu # avB00T52Y+ZZmz3tMMVgFHn9pFFltYr3s3bEek7I6pU8unISbiyQzxqhIUKaBi8h # y8LgoY5UnGjX5jHsIvINzms+JX5Ity02sL0wggXrMIID06ADAgECAhBl4eLj1d5Q # RYXzJiSABeLUMA0GCSqGSIb3DQEBDQUAMIGIMQswCQYDVQQGEwJVUzETMBEGA1UE # CBMKTmV3IEplcnNleTEUMBIGA1UEBxMLSmVyc2V5IENpdHkxHjAcBgNVBAoTFVRo # ZSBVU0VSVFJVU1QgTmV0d29yazEuMCwGA1UEAxMlVVNFUlRydXN0IFJTQSBDZXJ0 # aWZpY2F0aW9uIEF1dGhvcml0eTAeFw0xNDA5MTkwMDAwMDBaFw0yNDA5MTgyMzU5 # NTlaMHwxCzAJBgNVBAYTAlVTMQswCQYDVQQIEwJNSTESMBAGA1UEBxMJQW5uIEFy # Ym9yMRIwEAYDVQQKEwlJbnRlcm5ldDIxETAPBgNVBAsTCEluQ29tbW9uMSUwIwYD # VQQDExxJbkNvbW1vbiBSU0EgQ29kZSBTaWduaW5nIENBMIIBIjANBgkqhkiG9w0B # AQEFAAOCAQ8AMIIBCgKCAQEAwKAvix56u2p1rPg+3KO6OSLK86N25L99MCfmutOY # MlYjXAaGlw2A6O2igTXrC/Zefqk+aHP9ndRnec6q6mi3GdscdjpZh11emcehsrip # hHMMzKuHRhxqx+85Jb6n3dosNXA2HSIuIDvd4xwOPzSf5X3+VYBbBnyCV4RV8zj7 # 8gw2qblessWBRyN9EoGgwAEoPgP5OJejrQLyAmj91QGr9dVRTVDTFyJG5XMY4Drk # N3dRyJ59UopPgNwmucBMyvxR+hAJEXpXKnPE4CEqbMJUvRw+g/hbqSzx+tt4z9mJ # mm2j/w2nP35MViPWCb7hpR2LB8W/499Yqu+kr4LLBfgKCQIDAQABo4IBWjCCAVYw # HwYDVR0jBBgwFoAUU3m/WqorSs9UgOHYm8Cd8rIDZsswHQYDVR0OBBYEFK41Ixf/ # /wY9nFDgjCRlMx5wEIiiMA4GA1UdDwEB/wQEAwIBhjASBgNVHRMBAf8ECDAGAQH/ # AgEAMBMGA1UdJQQMMAoGCCsGAQUFBwMDMBEGA1UdIAQKMAgwBgYEVR0gADBQBgNV # HR8ESTBHMEWgQ6BBhj9odHRwOi8vY3JsLnVzZXJ0cnVzdC5jb20vVVNFUlRydXN0 # UlNBQ2VydGlmaWNhdGlvbkF1dGhvcml0eS5jcmwwdgYIKwYBBQUHAQEEajBoMD8G # CCsGAQUFBzAChjNodHRwOi8vY3J0LnVzZXJ0cnVzdC5jb20vVVNFUlRydXN0UlNB # QWRkVHJ1c3RDQS5jcnQwJQYIKwYBBQUHMAGGGWh0dHA6Ly9vY3NwLnVzZXJ0cnVz # dC5jb20wDQYJKoZIhvcNAQENBQADggIBAEYstn9qTiVmvZxqpqrQnr0Prk41/PA4 # J8HHnQTJgjTbhuET98GWjTBEE9I17Xn3V1yTphJXbat5l8EmZN/JXMvDNqJtkyOh # 26owAmvquMCF1pKiQWyuDDllxR9MECp6xF4wnH1Mcs4WeLOrQPy+C5kWE5gg/7K6 # c9G1VNwLkl/po9ORPljxKKeFhPg9+Ti3JzHIxW7LdyljffccWiuNFR51/BJHAZIq # UDw3LsrdYWzgg4x06tgMvOEf0nITelpFTxqVvMtJhnOfZbpdXZQ5o1TspxfTEVOQ # Asp05HUNCXyhznlVLr0JaNkM7edgk59zmdTbSGdMq8Ztuu6VyrivOlMSPWmay5Mj # vwTzuNorbwBv0DL+7cyZBp7NYZou+DoGd1lFZN0jU5IsQKgm3+00pnnJ67crdFwf # z/8bq3MhTiKOWEb04FT3OZVp+jzvaChHWLQ8gbCORgClaZq1H3aqI7JeRkWEEEp6 # Tv4WAVsr/i7LoXU72gOb8CAzPFqwI4Excdrxp0I4OXbECHlDqU4sTInqwlMwofmx # eO4u94196qIqJQl+8Sykl06VktqMux84Iw3ZQLH08J8LaJ+WDUycc4OjY61I7FGx # CDkbSQf3npXeRFm0IBn8GiW+TRDk6J2XJFLWEtVZmhboFlBLoUlqHUCKu0QOhU/+ # AEOqnY98j2zRMYIEXjCCBFoCAQEwgZAwfDELMAkGA1UEBhMCVVMxCzAJBgNVBAgT # Ak1JMRIwEAYDVQQHEwlBbm4gQXJib3IxEjAQBgNVBAoTCUludGVybmV0MjERMA8G # A1UECxMISW5Db21tb24xJTAjBgNVBAMTHEluQ29tbW9uIFJTQSBDb2RlIFNpZ25p # bmcgQ0ECEHJNXiAT1cKRQFXzfFSJVHEwCQYFKw4DAhoFAKB4MBgGCisGAQQBgjcC # AQwxCjAIoAKAAKECgAAwGQYJKoZIhvcNAQkDMQwGCisGAQQBgjcCAQQwHAYKKwYB # BAGCNwIBCzEOMAwGCisGAQQBgjcCARUwIwYJKoZIhvcNAQkEMRYEFEKHzp/ivtNk # ULR1cWXyPaeyCYP7MA0GCSqGSIb3DQEBAQUABIIBAKt0opSUNcpZvklNLRjEKHzY # GY6Rwe4+gLm2zPm7jcYKP/D7M82Ct4lOWOVIfuYZhxO6+PquefHzVUzqXHt5CzBt # 7mk/C7Yy0IRob1wMpzTcmeKkFQUN76xvDm+FM2zs6OlVmV1R/NIHRexLq82GeYVb # RdjXVlR9GHprjrHO7+l0KMkr9wlMOiWhsBQS16kjIHpONFb2NAhJZ5PK39FYu3sD # 5ssutBA+S05KQ6G0g6GD1ycFFUSzhoPBR95kqKg0vimKg63fL58mmQZgZc0aZ+WE # vul3JgAKD/VVhhUMcYUNmbo/FhG/cCHXjQjq0+YzEYSgmRbRmHDYb1U8AmA1qLGh # ggIoMIICJAYJKoZIhvcNAQkGMYICFTCCAhECAQEwgY4wejELMAkGA1UEBhMCR0Ix # GzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBxMHU2FsZm9yZDEa # MBgGA1UEChMRQ09NT0RPIENBIExpbWl0ZWQxIDAeBgNVBAMTF0NPTU9ETyBUaW1l # IFN0YW1waW5nIENBAhArc9t0YxFMWlsySvIwV3JJMAkGBSsOAwIaBQCgXTAYBgkq # hkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0xOTA1MzExMjE2 # MjlaMCMGCSqGSIb3DQEJBDEWBBROk/abiAEC01wp8iqohtBoEk0s9jANBgkqhkiG # 9w0BAQEFAASCAQBnyzsBEx1uRGCXwhqo5geJ1DJyGNCnfmX+0OSM/ws1xLbOoATL # 7kEaIY0cAzxtKRLxoQZ9dtLm1/u0gSf7oWM/iRG+S8mL2Tt1qykd/G2GEedy7w/q # Vy8u+GpHrbRmudIJ+GI5ZJXacJZJVL1rJwJeWQS0fFglSylnXgomhxkp3IB562iA # g3opvZS7rwFB4t8ErtCLk62gYIR+yKqLt0HmGZLDvX1Nw0UziuP6cTDA/VuJTNIw # WDQcZZdr7Ztt4HUaPyCd86ggEpPl4K3DZIuXJNPmvJruUAzwLH4rdz5FUrWD7Y9i # RVpsilPWSDUeaIoD5VlDEk+TtiN/tx1ht2f+ # SIG # End signature block |