【iOS】スプレッドシート上で課金アイテムの注文番号からユーザーを特定する機能の実装

概要

ユーザーが購入した課金アイテムの注文番号から該当のユーザーを特定する機能をスプレッドシート上で完結するようにしたのでその紹介

背景&目的

• 現在運用しているプロダクトでユーザーからアカウント(=ユーザーID)を特定して欲しいというお問い合わせがちょこちょこあった(過去使っていたんだけどアカウント忘れちゃった的なお問い合わせ)
• 過去に購入した課金アイテムの注文番号をユーザーから共有してもらい特定を行っていた
• 上記フローが、運用チーム(お問い合わせ)→エンジニア(ユーザー特定)→運用チーム(お問い合わせ返信)のフローになっていた
• そこで運用チーム(=非エンジニア)だけで完結するように今回の機能を実現しました

全体流れ

1. Google App Script(GAS) で App Store Server API を用いたスクリプトを実装
   • 注文番号から 購入時間 と トランザクションID を取得する
2. Redash で「購入時間」と「トランザクションID」を入力して、ユーザー情報を取得するクエリを作成する
3. GAS と Redash のつなぎ込みを行い、スプレッドシートにユーザー情報を反映する

※後述の GAS 等のコードは一部抜粋で完全なものでは無いのでご注意下さい

実装

GAS x App Store Server API

App Store Server API 下準備

App Store Server API を実行するために必要な環境変数の読み込み

function getCredentials() {
  const scriptProperties = PropertiesService.getScriptProperties();
  const privateKeyP8 = scriptProperties.getProperty("PRIVATE_KEY_P8");
  const issuerId     = scriptProperties.getProperty("ISSUER_ID");
  const keyId        = scriptProperties.getProperty("KEY_ID");
  const bundleId     = scriptProperties.getProperty("BUNDLE_ID");

  // 必要に応じて null チェックなどを行う
  return {
    privateKeyP8: privateKeyP8,
    issuerId: issuerId,
    keyId: keyId,
    bundleId: bundleId
  };
}

環境変数	説明
PRIVATE_KEY_P8	App Store Server API の 秘密鍵(生成後1度のみDL可能なファイル)
ISSUER_ID	App Store Server API の Issuer ID
KEY_ID	App Store Server API のキーID
BUNDLE_ID	アプリのバンドルID

App Store Server API 実行周り

App Store Server API の lookup エンドポイントよりペイロードを取得

function getTransactionInfo(orderNumber) {
  // (1) Apple Developerで発行したサーバーAPIキー(p8形式)の中身
  //     実際には安全な保管が推奨
  var credentials = getCredentials()

  // (2) Apple API用のJWTをES256署名で生成
  var token = generateES256Token(
    credentials.privateKeyP8,  // p8秘密鍵文字列
    credentials.issuerId,
    credentials.keyId,
    credentials.bundleId
  );
  Logger.log("Generated JWT: " + token);

  // (3) APIエンドポイント (注文番号lookup)
  var url = "https://api.storekit.itunes.apple.com/inApps/v1/lookup/" + orderNumber;

  // (4) GETリクエストを送信
  var options = {
    method: "get",
    headers: {
      "Authorization": "Bearer " + token
    },
    muteHttpExceptions: true
  };
  var response = UrlFetchApp.fetch(url, options);

  // (5) JSONパース
  var apiResponse = JSON.parse(response.getContentText());
  Logger.log("Lookup Response: " + JSON.stringify(apiResponse, null, 2));

  // レスポンスに signedTransactions が無い場合は終了
  if (!apiResponse.signedTransactions || apiResponse.signedTransactions.length === 0) {
    Logger.log("signedTransactionsが見つかりません。");
    return;
  }

  // (6) 最初のトランザクションJWTをデコード
  var firstTransactionJWT = apiResponse.signedTransactions[0];
  var payloadObj = decodeJWTPayload(firstTransactionJWT);

  Logger.log("purchaseDate  : " + payloadObj.purchaseDate);
  Logger.log("transactionId : " + payloadObj.transactionId);

  return payloadObj  
}

JWT周り

/**
 * ES256署名でJWTを生成する
 * (jsrsasignを使う)
 * @param {string} privateKeyP8 - p8形式の秘密鍵 (-----BEGIN PRIVATE KEY----- ... )
 * @param {string} issuerId     - Apple Developer Issuer ID
 * @param {string} keyId        - Apple Developer Key ID
 * @param {string} bundleId     - 対象アプリのBundle ID
 * @return {string} 生成したJWT文字列
 */
function generateES256Token(privateKeyP8, issuerId, keyId, bundleId) {
  // (1) ヘッダとペイロード作成
  var header = {
    alg: "ES256",
    kid: keyId,
    typ: "JWT"
  };

  var iat = Math.floor(Date.now() / 1000);  // 現在時刻(秒)
  var exp = iat + (20 * 60);               // 例: 20分後

  var payload = {
    iss: issuerId,
    iat: iat,
    exp: exp,
    aud: "appstoreconnect-v1",
    bid: bundleId
  };

  // (2) jsrsasignで鍵をパース
  //     KEYUTIL.getKey() にp8形式の文字列を渡すとECキーを取得できる。
  var ecKey = KEYUTIL.getKey(privateKeyP8);

  // (3) JWT署名 (ES256)
  //     KJUR.jws.JWS.sign(param) で署名付きJWTを生成する方法もありますが、
  //     下記のようにアルゴリズムを指定する形がわかりやすいです。
  var sHeader   = JSON.stringify(header);
  var sPayload  = JSON.stringify(payload);
  var signedJWT = KJUR.jws.JWS.sign(null, sHeader, sPayload, ecKey);

  return signedJWT;
}

/**
 * JWTを「署名検証なし」でペイロードだけ取り出す。
 * (RubyでJWTHelper.decode(token, key, false, algorithm: 'ES256').first と
 *  同様に単純にデコードするイメージ)
 * @param {string} jwt - JWT文字列 (ヘッダ.ペイロード.署名)
 * @return {Object} ペイロードJSON
 */
function decodeJWTPayload(jwt) {
  var parts = jwt.split(".");
  if (parts.length < 2) {
    throw new Error("Invalid JWT format");
  }

  // ペイロード部分(Base64Url)をデコード
  var payloadB64 = parts[1].replace(/-/g, "+").replace(/_/g, "/");
  // Base64パディング
  while (payloadB64.length % 4 !== 0) {
    payloadB64 += "=";
  }
  var payloadJson = Utilities.newBlob(Utilities.base64Decode(payloadB64)).getDataAsString();
  return JSON.parse(payloadJson);
}

jsrsasign 追加

• 理由：GAS はデフォルトでは ES256 署名をそのまま使えないため外部ライブラリを追加する必要がある
• 対応：jsrsasign-all-min.js に追加
  • そのままではエラーになる(navigator や window が存在しない)ため下記コードを追加

navigator や window が無い場合の処理を追加してエラーを回避

/*
【Reference】：https://github.com/kjur/jsrsasign/blob/master/jsrsasign-all-min.js
*/

/* 下記を追加 */
// navigatorが存在しない場合に、空オブジェクトをセットする
if (typeof navigator === "undefined") {
  var navigator = { appName: "", appVersion: "" };
}
if (typeof window === "undefined") {
  var window = { appName: "", appVersion: "" };
}

/*
 * jsrsasign(all) 11.1.0 (2024-02-01) (c) 2010-2023 Kenji Urushima | kjur.github.io/jsrsasign/license
 */
var VERSION = "11.1.0";
var VERSION_FULL = "jsrsasign(all) 11.1.0 (2024-02-01) (c) 2010-2023 Kenji Urushima | kjur.github.io/jsrsasign/license";

...略

Redash クエリ

各サービス・プロダクト毎にDB・テーブル構成が違うのでここでは具体的なクエリは紹介しませんが、基本的なポイントとしては

• クエリの入力は 購入時間(=purchase_date) と トランザクションID(transaction_id)
• レシート情報があるテーブル内で該当するトランザクションIDがあるレコードを取得
  • レコードが多いテーブルだと処理に時間がかかり Redash サーバー等に負荷がかかるため購入時間で条件を絞ってクエリを重くならないようしてます
• 取得したレコードをユーザー情報があるテーブルと紐づけて欲しい情報を出力する

(※伏せ字ばかりで恐縮ですが、こんな感じのクエリ出力のイメージです)

GAS と Redash のつなぎこみ

function getData() {
  var sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);

  // 1) 開始行、終了行を取得
  const startRow = Number(sheet.getRange("B2").getValue());
  const endRow   = Number(sheet.getRange("C2").getValue());

  // 3) 行をループしながら処理
  for (let row = startRow; row <= endRow; row++) {
    // A列から注文番号を取得
    const orderNumber = sheet.getRange(row, 1).getValue(); // ①
    if (!orderNumber) {
      Logger.log(`Row ${row}: 注文番号がありません。`);
      throw new Error(`Row ${row}: 注文番号がありません。`);
    }
    Logger.log(`Row ${row}: 注文番号 = ${orderNumber}`);

    // トランザクション情報取得
    var transactionInfo = getTransactionInfo(orderNumber) // ②
    if (!transactionInfo) {
      throw new Error(`Row ${row}: transactionInfo が取得できませんでした。スキップします。`);
    }
    let params = {
      'purchase_date': `${transactionInfo.purchaseDate}`,
      'transaction_id': `${transactionInfo.transactionId}`
    };
    var results = fetch_latest_query_result(984, params); // ③
    console.log("results:", results)

    // ④
    sheet.getRange(row, 2).setValue(results[0]['ユーザーID'])
    sheet.getRange(row, 3).setValue(results[0]['メールアドレス'])
    sheet.getRange(row, 4).setValue(results[0]['課金時刻'])
    sheet.getRange(row, 5).setValue(results[0]['管理画面リンク'])
  }
}

• ①：スプレッドシート上の注文番号を取得
• ②：App Store Server API を用いて注文番号からトランザクション情報を取得
• ③：購入時間とトランザクションIDを入力として Redash のクエリを実行
• ④：クエリ結果をスプレッドシートに反映

まとめ

• App Store Server API を用いて課金アイテムの注文番号からトランザクション情報を取得する
  • Google App Script で上記 API を利用するために jsrsasign ライブラリを利用する
• トランザクション情報を Redash のクエリに投げ、ユーザー情報を取得する
• クエリ結果のユーザー情報をスプレッドシート上に反映

これでスプレッドシート上で注文番号を記載し、適当なボタンを置いてスクリプトを実行すればユーザー情報が取得できるようになります。誰でも出来るようになったので作業フローにエンジニアが不要なり待ち時間等も発生しなくなって効率化できました🎉

参考

• Creating API keys to authorize API requests
• Look Up Order ID
• 基本から理解するJWTとJWT認証の仕組み