admin管理员组

文章数量:1122832

community members.

I sincerely hope that one of you has tried solving this challenge, which is driving me up the wall.

My Objective

I need to update some cells in a Google Sheet associated with a Jira issue via one of its remote links. I figured out how to create and configure a Google App with a service account to enable secure server-to-server access between Jira and the Google Workspace where the sheet is located. I can read data from the associated Google Sheets and perform many automated Jira functions. Once those actions are done, I need to update cells in the Google Sheet before the Groovy script completes.

My Challange

1. Constraints of ScriptRunner's Groovy environment

The Groovy environment available in ScriptRunner has constraints, making this a challenge. Only JAVA libraries starting with java.* can be imported and used as classes in this Groovy environment. This means I cannot import and access the Google Sheet and Google Auth libraries and classes, making achieving my object much more straightforward. Only the Java libraries are available, e.g., java.security., to use the Java security classes and java.util.Base64 to use the encoding, etc.

2. I have not worked with Google Authorisations I can read information from the Google sheet, but updating the sheet requires authentication involving private keys and encoding, and and... I understand how to create the JWT header and JWT claim set, but I am stuck on how to create the JSON Web Signature (JWS), which is required to obtain the authentication token from Google.

Here is the link to the Google source I'm referring to: and specifically the section highlighted in image below:

Once I can construct the JSON WEB SIGNATURE (JWS), I will use that to obtain the authorisation token required by the Google sheet batch update API endpoint.

If anyone managed to write a Groovy script in ScriptRunner that uses a Google Service Account to update a Google Sheet, any examples or help to achieve this would be greatly appreciated.

Code to the point where I am stuck

I will update this code as we progress until it works as expected/intended to serve as a reference for others

import static ava.nio.charset.StandardCharsets.UTF_8
import java.security.spec.X509EncodedKeySpec
import java.security.KeyFactory
import java.security.PrivateKey
import java.security.Signature
import java.util.Base64
import groovy.time.*

def googleSheetId = '1PYLlm83mo-XY5_gmCWjEzdIXYjy-gmCWvfLYvcvp7DM'
def googleServiceAccountKey = ''
// JWT Header:
String headerJWTjson = '{"alg":"RS256","typ":"JWT","kid":"' + GOOGLE_SERVICE_ACCOUNT_KEY + '"}'
def headerJWTbytes = headerJWTjson.getBytes(UTF_8)
def encodedJWTheader = headerJWTbytes.encodeBase64Url().toString()
// JWT Claim Set
String claimSetJWTiss = "871527165788-fk3inbc211l1dnjce94pf52c3t9snqj0.apps.googleusercontent"
String claimSetJWTscope = ";
String claimSetJWTaud = "; 
def now = new Date().getTime() / 1000
def claimSetJWTiat = now.round(0)
def claimSetJWTexp = claimSetJWTiat + 3600
String claimSetJWTjson = '''
{
    "iss": "''' + claimSetJWTiss + '''",
    "scope": "''' + claimSetJWTscope + '''",
    "aud": "''' + claimSetJWTaud + '''",
    "exp": ''' + claimSetJWTexp + ''',
    "iat": ''' + claimSetJWTiat + '''
}'''
def claimSetJWTbytes = claimSetJWTjson.getBytes("UTF-8")
def encodedJWTclaimSet  = claimSetJWTbytes.encodeBase64Url().toString()
// JSON Web Signature (JWS)
def jsonWebSignatureInput = '{' + encodedJWTheader + '}.{' + encodedJWTclaimSet + '}'
byte[] inputBytes = jsonWebSignatureInput.getBytes("UTF-8") // encode the input as UTF-8
def privateKeyString = googleServiceAccountKey
def sha256 = privateKeyString.digest('SHA-256')
// ------------------------------------------
// This is where I'm stuck at the moment...
// ------------------------------------------
// I tried this, but I doubt whether it is what needs to be done:
PrivateKey privateKey = KeyFactory.getInstance("RSA").generatePrivate( new X509EncodedKeySpec(Base64.getDecoder().decode(sha256)))
 // because it results in the following message:

java.security.spec.InvalidKeySpecException: Only RSAPrivate(Crt)KeySpec and PKCS8EncodedKeySpec supported for RSA private keys

// The next step is to construct the payload for the authentication API endpoint. 
// It is the combination of the JWT Header and JWT ClaimSet and the JWS (Signature) to authenticate with 
// ... code to continue here...

本文标签：