Google Service Account and use with iHub Cloud

Service account is used to have system to system communication and is recommended for Google services using iHub.

There is two sides of the setup, one in iHub Cloud and one in Google Console

Google Console
Reference create a service account https://cloud.google.com/iam/docs/service-accounts-create

  1. Goto Google Console

  2. Create a project

  3. Goto API & Services

  4. Click Credentials

  5. Click Create Credentials

  6. Create a Service Account

  7. Click on the Service User

  8. Click Keys, create new

  9. Download the Private Key as JSON

  10. Enable the API that you want to access. example Google Sheets

  11. For Google Docs, Sheets, Presentation etc share the resource with the service account to grant access

iHub Cloud

  1. Goto Credentials

  2. Click Add Credential button

  3. Select JWT, enter a name Google JWT Sheets and click Create

  4. Select RS256 as Algorithm

Google only supports Algorithm RS256

  1. Paste the private key from the JSON file downloaded on step 4 in Google. It must start with -----BEGIN PRIVATE KEY----- and include all chars down to -----END PRIVATE KEY-----\n
    Note the \n MUST also be included. No other chars before or after can be added.

  2. Edit the claim to be as below, replace iss to the client_email in the JSON file. Replace or add to the scope property any additional scope using comma.

    { "iss": "my-project@my-project.iam.gserviceaccount.com", "aud": "https://oauth2.googleapis.com/token", "iat": {{currentTimeSeconds}}, "exp": {{plus currentTimeSeconds 1200}}, "scope": "https://www.googleapis.com/auth/spreadsheets" }
  3. Uncheck Encode secret with Base64

  4. Clear the field Prefix Header, it must be empty.

  5. Check the Use JWT as scope variable instead of authorization header

  6. Click save

This will now create a JWT token as a scope variable that can be exchange to an access token.

Use {{JWT}} to use the JWT in the actions.

 

Exchange JWT to Access token

  1. Goto Integrations in iHub

  2. Click Create Integration button

  3. Click Triggers, select when to trigger the integration, like an issue event

  4. Click Add new action

  5. Give it a name like Exhange JWT to Access Token

  6. Select POST as method

  7. Enter https://oauth2.googleapis.com/token in the URL field

  8. Select Authorization Google JWT Sheets [JWT]

  9. Select the radio button x-www-form-urlencoded

  10. Enter the the following JSON

    { "grant_type":"urn:ietf:params:oauth:grant-type:jwt-bearer", "assertion":"{{JWT}}" }
  11. Click Save

Test the exchange by clicking the play symbol.
View the Execution Log to see the result. If successful you will get a 200 OK with the access token

Use the access token to add a row in a Google Sheets

  1. Click Add new action

  2. Give it a name like Add issue to sheet

  3. Select POST as method

  4. Enter https://sheets.googleapis.com/v4/spreadsheets/{{GOOGLE_SHEET_ID_AS_IN_URL}}/values/A1:append?valueInputOption=RAW in the URL field

  5. Expand headers and add
    Key: Authorization and Value: Bearer {{access_token}}
    the {{access_token}} will be replaced with the short lived token from the parent response.

  6. Enter the the following JSON

    { "values": [ [ "{{issue.key}}", "{{issue.summary}}" ] ] }
  7. Click Save

This will now

Now when ever you create an issue it will trigger the integration, which will create the JWT token and exchange it to a access token. The access token is then used in the child action to perform the append of a row with issue data.

 

References

https://developers.google.com/identity/protocols/oauth2/service-account#httprest

https://cloud.google.com/endpoints/docs/openapi/troubleshoot-jwt

https://developers.google.com/sheets/api/guides/concepts

https://nishothan-17.medium.com/google-sheet-apis-using-google-cloud-platform-gcp-credentials-8bca55521af