Jira Cloud ScriptRunner中Groovy更新Google Sheet遇JWS鉴权难题

我的目标 #

通过Jira问题的远程链接，更新关联Google Sheet中的指定单元格。已完成Google服务账号配置，实现Jira与Google Workspace的服务器间安全访问，能读取Sheet数据并执行Jira自动化操作，但需要在Groovy脚本内完成Sheet单元格更新。

遇到的挑战 #

• ScriptRunner环境限制：仅能导入java.*开头的基础Java库，无法使用Google官方的Sheet或Auth SDK，只能依赖java.security、java.util.Base64等工具类。
• JWT签名卡壳：已能构造JWT Header和Claim Set，但在生成JWS（JSON Web Signature）时，私钥处理环节报错，无法完成Google认证令牌的获取。

当前错误信息 #

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

卡住的代码片段 #

import static java.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

def googleSheetId = '1PYLlm83mo-XY5_gmCWjEzdIXYjy-gmCWvfLYvcvp7DM'
def googleServiceAccountKey = '' // 这里填入你的服务账号私钥字符串

// JWT Header
String headerJWTjson = '{"alg":"RS256","typ":"JWT","kid":"your-key-id"}'
def headerJWTbytes = headerJWTjson.getBytes(UTF_8)
def encodedJWTheader = Base64.getUrlEncoder().withoutPadding().encodeToString(headerJWTbytes)

// JWT Claim Set
String claimSetJWTiss = "871527165788-fk3inbc211l1dnjce94pf52c3t9snqj0.apps.googleusercontent.com"
String claimSetJWTscope = "https://www.googleapis.com/auth/worksheets" // 注意这里权限范围有误
String claimSetJWTaud = "https://oauth2.googleapis.com/token" 
def now = new Date().getTime() / 1000
def claimSetJWTiat = now.round(0) as Long
def claimSetJWTexp = claimSetJWTiat + 3600

String claimSetJWTjson = """
{
    "iss": "${claimSetJWTiss}",
    "scope": "${claimSetJWTscope}",
    "aud": "${claimSetJWTaud}",
    "exp": ${claimSetJWTexp},
    "iat": ${claimSetJWTiat}
}"""
def claimSetJWTbytes = claimSetJWTjson.getBytes(UTF_8)
def encodedJWTclaimSet = Base64.getUrlEncoder().withoutPadding().encodeToString(claimSetJWTbytes)

// JSON Web Signature (JWS) 构造待签名内容：格式错误，不需要大括号
def jwsInput = "${encodedJWTheader}.${encodedJWTclaimSet}"
byte[] inputBytes = jwsInput.getBytes(UTF_8)

// 错误的私钥处理部分
def privateKeyString = googleServiceAccountKey
def sha256 = privateKeyString.digest('SHA-256')
PrivateKey privateKey = KeyFactory.getInstance("RSA").generatePrivate(new X509EncodedKeySpec(Base64.getDecoder().decode(sha256)))

解决思路与修正后的代码 #

核心问题分析 #

1. 私钥格式错误：你误用了X509EncodedKeySpec（用于公钥解析），RSA私钥必须用PKCS8EncodedKeySpec。Google服务账号私钥是PKCS8格式，需先移除头尾的-----BEGIN PRIVATE KEY-----和-----END PRIVATE KEY-----标记，再Base64解码。
2. JWS输入格式错误：待签名内容应为编码后的Header.编码后的Claim Set，不需要额外添加大括号。
3. 权限范围错误：Google Sheets的编辑权限范围是https://www.googleapis.com/auth/spreadsheets，不是worksheets。

修正后的完整代码 #

import static java.nio.charset.StandardCharsets.UTF_8
import java.security.spec.PKCS8EncodedKeySpec
import java.security.KeyFactory
import java.security.PrivateKey
import java.security.Signature
import java.util.Base64
import groovy.json.JsonSlurper

// 配置参数
def googleServiceAccountKey = """-----BEGIN PRIVATE KEY-----
你的服务账号私钥完整内容
-----END PRIVATE KEY-----"""
def clientEmail = "871527165788-fk3inbc211l1dnjce94pf52c3t9snqj0.apps.googleusercontent.com"
def keyId = "你的私钥对应的kid（从服务账号密钥JSON中获取）"
def googleSheetId = '1PYLlm83mo-XY5_gmCWjEzdIXYjy-gmCWvfLYvcvp7DM'
def targetRange = "Sheet1!A1:B2" // 要更新的单元格范围

// 1. 构造JWT Header
def header = [
    alg: "RS256",
    typ: "JWT",
    kid: keyId
]
def headerJson = new groovy.json.JsonBuilder(header).toString()
def encodedHeader = Base64.getUrlEncoder().withoutPadding().encodeToString(headerJson.getBytes(UTF_8))

// 2. 构造JWT Claim Set
def now = System.currentTimeMillis() / 1000 as Long
def claims = [
    iss: clientEmail,
    scope: "https://www.googleapis.com/auth/spreadsheets",
    aud: "https://oauth2.googleapis.com/token",
    exp: now + 3600,
    iat: now
]
def claimsJson = new groovy.json.JsonBuilder(claims).toString()
def encodedClaims = Base64.getUrlEncoder().withoutPadding().encodeToString(claimsJson.getBytes(UTF_8))

// 3. 生成JWS签名
def jwsInput = "${encodedHeader}.${encodedClaims}"
byte[] inputBytes = jwsInput.getBytes(UTF_8)

// 处理私钥：移除头尾标记，清理空白后Base64解码
def privateKeyPem = googleServiceAccountKey.replaceAll("-----BEGIN PRIVATE KEY-----", "")
                                          .replaceAll("-----END PRIVATE KEY-----", "")
                                          .replaceAll("\\s+", "")
byte[] privateKeyBytes = Base64.getDecoder().decode(privateKeyPem)
PKCS8EncodedKeySpec keySpec = new PKCS8EncodedKeySpec(privateKeyBytes)
PrivateKey privateKey = KeyFactory.getInstance("RSA").generatePrivate(keySpec)

// 用SHA256withRSA算法签名
Signature signature = Signature.getInstance("SHA256withRSA")
signature.initSign(privateKey)
signature.update(inputBytes)
byte[] signatureBytes = signature.sign()
def encodedSignature = Base64.getUrlEncoder().withoutPadding().encodeToString(signatureBytes)

// 4. 组装完整JWT
def jwt = "${encodedHeader}.${encodedClaims}.${encodedSignature}"

// 5. 请求Google获取访问令牌
def tokenUrl = "https://oauth2.googleapis.com/token"
def tokenBody = [
    grant_type: "urn:ietf:params:oauth:grant-type:jwt-bearer",
    assertion: jwt
]
def tokenResponse = new URL(tokenUrl).openConnection() {
    requestMethod = "POST"
    setRequestProperty("Content-Type", "application/x-www-form-urlencoded")
    doOutput = true
    outputStream.withWriter { writer ->
        writer.write(tokenBody.collect { k, v -> "${k}=${URLEncoder.encode(v.toString(), UTF_8)}" }.join("&"))
    }
}
def tokenJson = new JsonSlurper().parse(tokenResponse.inputStream)
def accessToken = tokenJson.access_token

// 6. 调用Google Sheets API更新单元格
def updateUrl = "https://sheets.googleapis.com/v4/spreadsheets/${googleSheetId}/values/${targetRange}?valueInputOption=RAW"
def updateBody = [
    values: [
        ["Jira问题ID", "PROJ-123"],
        ["更新状态", "已完成"]
    ]
]
def updateResponse = new URL(updateUrl).openConnection() {
    requestMethod = "PUT"
    setRequestProperty("Authorization", "Bearer ${accessToken}")
    setRequestProperty("Content-Type", "application/json")
    doOutput = true
    outputStream.withWriter(UTF_8) { writer ->
        new groovy.json.JsonBuilder(updateBody).writeTo(writer)
    }
}

// 输出更新结果
if (updateResponse.responseCode == 200) {
    println "Sheet单元格更新成功"
} else {
    println "更新失败：响应码${updateResponse.responseCode}，内容：${updateResponse.inputStream.text}"
}

注意事项 #

• 确保服务账号邮箱已被添加为目标Google Sheet的编辑者（在Sheet共享设置中配置）。
• 私钥字符串需完整保留，或通过replaceAll清理所有空白字符后处理。
• JWT的过期时间（exp）不能超过当前时间1小时，否则Google会拒绝请求。

内容的提问来源于stack exchange，提问作者Ben