PowerShell reading data from Google Sheets using service account

In Google Cloud I have an project where i have created service account and downloaded its credentials.json file

File will look something like this:

{
  "type": "service_account",
  "project_id": "my-awesome-project",
  "private_key_id": "xxxxxxxxxxxxx",
  "private_key": "-----BEGIN PRIVATE KEY-----",
  "client_email": "[email protected]",
  "client_id": "000000000000000000000",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "universe_domain": "googleapis.com"
}

At the very end we want to call following:

Invoke-RestMethod -Uri "https://sheets.googleapis.com/v4/spreadsheets/$id/values/$range" -Headers @{"Authorization" = "Bearer $access_token" } | Select-Object -ExpandProperty values

to do it, we need an access_token

and to retrieve it we may utilize urn:ietf:params:oauth:grant-type:jwt-bearer

to retrieve token we should call following:

Invoke-RestMethod -Method Post -Uri "https://oauth2.googleapis.com/token" -Headers @{"Content-Type" = "application/x-www-form-urlencoded" } -Body @{
  grant_type = "urn:ietf:params:oauth:grant-type:jwt-bearer"
  assertion  = "$header.$payload.$signature"
} | Select-Object -ExpandProperty access_token

where assertion is an JWT token signed with private key from our credentials.json

the assertion token payload will be:

{
  "iss": "[email protected]",
  "aud": "https://oauth2.googleapis.com/token",
  "scope": "https://www.googleapis.com/auth/spreadsheets.readonly",
  "iat": 1746953454,
  "exp": 1746957054
}

let's break it down:

  • iss - issuer or who did issued this token - not how we are pointing to our selves, that's because actually we have signed token for ourselves
  • aud - for whom this signed token should be sent - note - here we are pointing not to google sheets but rather to token exchange endpoint, so token we have signed is supposed to be used only for exchange
  • scope - and this one is actually the scope we expect access token to have after succesfull exchange
  • iat, epx - are kind of date +%s

And here is the full script:

$credentials = Get-Content credentials.json | ConvertFrom-Json

# header = base64url_encode(bytes(json_encode({ alg: "RS256", typ: "JWT" })))
$header = [Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes((ConvertTo-Json -Compress -InputObject @{
        alg = "RS256"
        typ = "JWT"
      }))).TrimEnd('=').Replace('+', '-').Replace('/', '_')

$payload = [Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes((ConvertTo-Json -Compress -InputObject @{
        iss   = $credentials.client_email
        scope = "https://www.googleapis.com/auth/spreadsheets.readonly"
        aud   = "https://oauth2.googleapis.com/token"
        iat   = [int](Get-Date -UFormat %s)
        exp   = 3600 + [int](Get-Date -UFormat %s)
      }))).TrimEnd('=').Replace('+', '-').Replace('/', '_')

$rsa = [System.Security.Cryptography.RSA]::Create()
$rsa.ImportFromPem($credentials.private_key) # POI: we are signing token with private key from credentials.json
$signature = $rsa.SignData([System.Text.Encoding]::UTF8.GetBytes("$header.$payload"), [System.Security.Cryptography.HashAlgorithmName]::SHA256, [System.Security.Cryptography.RSASignaturePadding]::Pkcs1)
$signature = [Convert]::ToBase64String($signature).TrimEnd('=').Replace('+', '-').Replace('/', '_')

$access_token = Invoke-RestMethod -Method Post -Uri "https://oauth2.googleapis.com/token" -Headers @{"Content-Type" = "application/x-www-form-urlencoded" } -Body @{
  grant_type = "urn:ietf:params:oauth:grant-type:jwt-bearer"
  assertion  = "$header.$payload.$signature"
} | Select-Object -ExpandProperty access_token

$speadsheet = "xxxxxxx"
$range = "data!A:J"

$values = Invoke-RestMethod -Uri "https://sheets.googleapis.com/v4/spreadsheets/$speadsheet/values/$range" -Headers @{"Authorization" = "Bearer $access_token" } | Select-Object -ExpandProperty values
$values

with this you can read Google Sheets data from PowerShell withou any 3rd party dependency