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 ourselvesaud
- 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 exchangescope
- and this one is actually the scope we expect access token to have after succesfull exchangeiat
,epx
- are kind ofdate +%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