Public
Documentation Settings
Select option
Double Column
Select option
Google Service Account Sheets Demo
Introduction
GET
Side Load JSRSASign
POST
Google Get Token
POST
Update Sheet

Google Service Account Sheets Demo

https://stackoverflow.com/questions/53965446/how-to-sign-a-jwt-with-a-private-key-pem-in-cryptojs

Side Load JSRSAssign to sign JWT tokens to be able to login into Google Sheets API

GETSide Load JSRSASign

http://kjur.github.io/jsrsasign/jsrsasign-latest-all-min.js

Load the JS RSA Sign library We can do this by calling a CDN of the library. Then save it to an environment and calling eval on it in the next script we want to use it in.

Example Request
Side Load JSRSASign
curl
curl --location 'http://kjur.github.io/jsrsasign/jsrsasign-latest-all-min.js'
Example Response
No response body
This request doesn't return any response body
No response headers
This request doesn't return any response headers

POSTGoogle Get Token

https://oauth2.googleapis.com/token

Here we just pass in the required information about the service account and scopes we want to touch. Then sign the payload and send it over for an access token.

//iss: email address of service account //scope A space-delimited list of the permissions that the application requests. //aud A descriptor of the intended target of the assertion. When making an access token request this value is always https://oauth2.googleapis.com/token. //exp The expiration time of the assertion, specified as seconds since 00:00:00 UTC, January 1, 1970. This value has a maximum of 1 hour after the issued time. //iat The time the assertion was issued, specified as seconds since 00:00:00 UTC, January 1, 1970.

https://developers.google.com/identity/protocols/OAuth2ServiceAccount#makingrequest

https://gist.github.com/dinvlad/425a072c8d23c1895e9d345b67909af0

HEADERS
Content-Type

application/x-www-form-urlencoded

Bodyurlencoded
grant_type

urn:ietf:params:oauth:grant-type:jwt-bearer

assertion

{{jwt}}

Example Request
Google Get Token
curl
curl --location 'https://oauth2.googleapis.com/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer' \
--data-urlencode 'assertion={{jwt}} '
Example Response
No response body
This request doesn't return any response body
No response headers
This request doesn't return any response headers

POSTUpdate Sheet

https://sheets.googleapis.com/v4/spreadsheets/{{sheetId}}/values/{{range}}:append?valueInputOption=USER_ENTERED&insertDataOption=INSERT_ROWS

Now we can just pass in the token as if we got it from a consent screen in the Auth tab!

Happy hacking!

HEADERS
Authorization

Bearer {{accessToken}}

PARAMS
valueInputOption

USER_ENTERED

insertDataOption

INSERT_ROWS

Bodyraw 
{
  "values": [
    [ 'Hello', 'World', '!']
  ]
}
Example Request
Update Sheet
curl
curl --location -g 'https://sheets.googleapis.com/v4/spreadsheets/{{sheetId}}/values/{{range}}:append?valueInputOption=USER_ENTERED&insertDataOption=INSERT_ROWS' \
--header 'Authorization: Bearer {{accessToken}}' \
--data '{
  "values": [
    [ '\''Hello'\'', '\''World'\'', '\''!'\'']
  ]
}'
Example Response
No response body
This request doesn't return any response body
No response headers
This request doesn't return any response headers