Stripe: 入金, TSV 抽出 (Stripe: Payouts, Extract as TSV)
決済プラットフォームStripe上の入金データを複数行のTSV文字列として抽出します。ステータスと入金(予定)日で絞り込めます。
Configs
  • C1: API トークンを設定した認証設定 *
  • C2: 入金ステータスで絞り込む場合に選んでください。
  • C3:入金予定日の範囲開始日をセットしてください。( 例 “2022-12-01” )#{EL}
  • C4:入金予定日の範囲終了日をセットしてください。#{EL}
  • C5:取得数の最大値をセットしてください。(デフォルト 10 制限 100)
  • C6: 作成された入金TSVが格納される文字型データ項目を指定してください(更新)
  • C7: 入金データの件数が格納される数値型データ項目を指定してください(更新)
  • C8: 取得数が最大値を超える時にtrueが格納される文字型データ項目を指定してください(更新)
Script (click to open)


main();

function main(){

    const auth = configs.get("conf_auth");
    const status = configs.get("conf_status");
    engine.log("selected payments status: " + status);
 
    const arrivalDateRangeFromStr = configs.get("conf_arrival_date_range_from");
    const arrivalDateRangeToStr = configs.get("conf_arrival_date_range_to");
    engine.log("arrivalDateRangeFromStr:" + arrivalDateRangeFromStr);
    engine.log("arrivalDateRangeToStr:" + arrivalDateRangeToStr);

    let arrivalDateRangeFrom = -1;
    let arrivalDateRangeTo = -1;
    if(arrivalDateRangeFromStr !== ""){
        arrivalDateRangeFrom = dateToEpochTime(arrivalDateRangeFromStr);
    }
    if(arrivalDateRangeToStr !== ""){
        arrivalDateRangeTo = dateToEpochTime(arrivalDateRangeToStr);
    }

    const tempDDView = configs.getObject("conf_limit");
    let limit = 0;

    if(tempDDView !== null){
        limit = engine.findDataByNumber(tempDDView.getNumber());
    }else{
        limit = Number(configs.get("conf_limit"));
    }

    const payouts = listAllPayouts(auth, status, arrivalDateRangeFrom, arrivalDateRangeTo, limit);

    const conf_tsv = configs.get("conf_tsv");
    const conf_num = configs.get("conf_num");
    const conf_has_more = configs.get("conf_has_more");
    if(conf_tsv !== ""){
        engine.setDataByNumber(configs.get("conf_tsv"), createPayoutsTsv(payouts));
    }
    if(conf_num !== ""){
        engine.setDataByNumber(configs.get("conf_num"), new java.math.BigDecimal(payouts.data.length));
    }
    if(conf_has_more !== ""){
        engine.setDataByNumber(configs.get("conf_has_more"), String(payouts.has_more));
    }

}

/*
status: "", pending, paid, failed, or canceled
arrivalDateRangeFrom, arrivalDateRangeTo: Payout Arrival Dates (From,To) are integer Unix timestamp(second).
- If the value is minus, it is not filtered by Payout Arrival Date.
limit: can range between 1 and 100, and the default is 10.
*/
function listAllPayouts(auth, status, arrivalDateRangeFrom, arrivalDateRangeTo, limit){

    const request = httpClient.begin();
    request.authSetting(auth);

    if(status !== ""){
        request.queryParam("status", status);
    }

    if(arrivalDateRangeFrom > 0){
        request.queryParam("arrival_date[gte]", String(arrivalDateRangeFrom));
    }

    if(arrivalDateRangeTo > 0){
        request.queryParam("arrival_date[lte]", String(arrivalDateRangeTo));
    }

    if(limit >=1 && limit <= 100){
        request.queryParam("limit", String(limit));
    }else{
        engine.log("");
        engine.log("Limit can range between 1 and 100, and the default is 10.");
    }

    const response = request.get("https://api.stripe.com/v1/payouts");

    const httpStatus = response.getStatusCode();
    const responseStr = response.getResponseAsString();
    engine.log("HTTP Status: " + String(httpStatus));

    const payouts = JSON.parse(responseStr)
    if(httpStatus===200){
        engine.log(payouts.data.length + " payouts");
        return payouts;
    }else{
        engine.log(responseStr);
        throw "listAllPayouts Error: HTTP Status was not 200.";
    }

}

/*
Integer Unix timestamp(second) will be returned.
strDate must be "YYYY-MM-DD" or "YYYY-MM-DD HH:MM".
If strDate format is wrong, -1 is returned.
*/
function dateToEpochTime(strDate){

    const regBpmsYMD   = /^\d{4}-\d{2}-\d{2}$/;
    const regBpmsYMDHM = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}$/;

    let dateFormat;
    
    if(regBpmsYMD.test(strDate)){
       dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
    }else if(regBpmsYMDHM.test(strDate)){
       dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm");
    }else{
        engine.log("Argument must be 'YYYY-MM-DD' or 'YYYY-MM-DD HH:MM'.");
        return -1;
    }

    const date = dateFormat.parse(strDate);
    const timestamp = date.getTime()/1000;
    engine.log(strDate + ":" + timestamp);
    return timestamp;

}

/*
String Format Date("YYYY-MM-DD") will be returned.
timestamp: Integer Unix timestamp(second)
*/
function epochTimeToDateString(timestamp){
    engine.log("timestamp:" + timestamp);
    return new java.text.SimpleDateFormat("yyyy-MM-dd").format(new java.sql.Date(timestamp * 1000));
}

//TSV which consists of the values of Payout Object(JSON) Elements will be retured.(except for metadata)
function createPayoutsTsv(payouts){
    let tsv = "";
    for(let i = 0 ; i < payouts.data.length ; i++){
        tsv += payouts.data[i].id + "\t";
        tsv += payouts.data[i].amount + "\t";
        tsv += epochTimeToDateString(payouts.data[i].arrival_date) + "\t";
        tsv += payouts.data[i].automatic + "\t";
        tsv += payouts.data[i].balance_transaction + "\t";
        tsv += epochTimeToDateString(payouts.data[i].created) + "\t";
        tsv += payouts.data[i].currency + "\t";
        tsv += payouts.data[i].description + "\t";
        tsv += payouts.data[i].destination + "\t";
        tsv += payouts.data[i].failure_balance_transaction + "\t";
        tsv += payouts.data[i].failure_code + "\t";
        tsv += payouts.data[i].failure_message + "\t";
        tsv += payouts.data[i].livemode + "\t";
        tsv += payouts.data[i].method + "\t";
        tsv += payouts.data[i].original_payout + "\t";
        tsv += payouts.data[i].reversed_by + "\t";
        tsv += payouts.data[i].source_type + "\t";
        tsv += payouts.data[i].statement_descriptor + "\t";
        tsv += payouts.data[i].status + "\t";
        tsv += payouts.data[i].type;
        tsv += "\n";
    }
    return tsv;
}

/*
Notes:
- Can include the automated step "listing all payouts process" into the workflow. (No code)
    - When the matter reaches, a listing all payouts request is automatically sent to the Stripe API.
    - https://stripe.com/docs/api/payouts/list
- The Stripe API key "secret key" is required for HTTP_Authz Setting.
    - https://dashboard.stripe.com/apikeys
    - https://dashboard.stripe.com/test/apikeys
- If you want to test-operate the workflow app, use the "test key" of the Stripe API key.
    - Set the secret key starting with `sk_text_` to "HTTP Authorization Aetting > Token Fixed Value".
        - Name: `StripeSecretKeyForXYZ`
        - Token: `sk_test_123456789012345678901234`
    - For actual operation, set a secret key starting with `sk_live_`.
- Arrival Date must be in the format "YYYY-MM-DD" or "YYYY-MM-DD HH:MM".
- Extracted TSV has 20 columns. (All elements of Payout except for "metadata")
- [Details of Payout Object](https://stripe.com/docs/api/payouts/object)
    - [0] Payout ID
    - [1] Amount: Amount to be transferred.
    - [2] Arrival Date: Date the payout is expected to arrive in the bank. (YYYY-MM-DD)
    - [3] Automatic: If the payout was created by an automated payout schedule.("true" or "false")
    - [4] Balance Transaction ID: ID of the balance transaction 
    - [5] Created Date: Date when  the payout data was created.(YYYY-MM-DD)
    - [6] Currency: ("usd" "jpy" etc.)
    - [7] Description
    - [8] Destination: ID of the bank account or card the payout was sent to.
    - [9] Failure Balance Transaction(Balance Transaction ID)
    - [10] Failure Code: [Types of payout failures](https://stripe.com/docs/api/payouts/failures)
    - [11] Failure Message
    - [12] Livemode
    - [13] Method: The method used to send this payout("standard" or "instant")
    - [14] Original Payout: (Payout ID)
    - [15] Reversed By: (Payout ID)
    - [16] Source Type: ("card", "fpx" or "bank_account")
    - [17] Statement Descriptor
    - [18] Status: ("paid","pending","in_transit","canceled" or "failed")
    - [19] Type: ("bank_account" or "card")

Notes(ja):
- ワークフロー内に自動工程『入金 TSV 抽出処理』を組み込むことができるようになります。(ノーコード実装)
    - 案件が到達すると Stripe API に対して入金抽出リクエストが送信されます。
    - https://stripe.com/docs/api/payouts/list
- 通信設定には、Stripe APIキーの "シークレットキー" が必要です。
    - https://dashboard.stripe.com/apikeys
    - https://dashboard.stripe.com/test/apikeys
- ワークフローアプリをテスト運用したい場合は、テスト用のシークレットキー "テストキー" を利用します。
    - `sk_test_` で始まるテストキーを『HTTP認証設定 > トークン直接指定』に設定します。
        - Name: `StripeSecretKeyForXYZ` 
        - Token: `sk_test_123456789012345678901234` 
    - 本運用する場合は `sk_live_` で始まるシークレットキーを設定します。抽出されるTSVは20列。(metadata 以外の全てのPayout要素)
- 入金予定日は、"YYYY-MM-DD" または "YYYY-MM-DD HH:MM" の形式で指定します。
- [Payout(入金) オブジェクトの詳細](https://stripe.com/docs/api/payouts/object)
    - [0] Payout ID: 入金データID
    - [1] Amount: 入金金額
    - [2] Arrival Date: 入金日(YYYY-MM-DD)
    - [3] Automatic: この入金が自動で作られたかどうか("true" or "false")
    - [4] Balance Transaction ID: 入金に関連する取引ID
    - [5] Created Date: 入金データ生成日(YYYY-MM-DD)
    - [6] Currency: 通貨("usd" "jpy" etc.)
    - [7] Description: 説明
    - [8] Destination: 入金先の銀行口座(BANK_ACCOUNT)またはクレジットカード等(CARD)のオブジェクトID
    - [9] Failure Balance Transaction: (取引ID)
    - [10] Failure Code: [Types of payout failures](https://stripe.com/docs/api/payouts/failures)
    - [11] Failure Message
    - [12] Livemode
    - [13] Method: 入金方法("standard" or "instant")
    - [14] Original Payout: (Payout ID)
    - [15] Reversed By: (Payout ID)
    - [16] Source Type: ("card", "fpx" or "bank_account")
    - [17] Statement Descriptor
    - [18] Status: ("paid","pending","in_transit","canceled" or "failed")
    - [19] Type: ("bank_account" or "card")

*/


Download

2022-06-01 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/ja/addons/stripe-payouts-list-all-as-tsv/
Addonファイルのインポートは Professional でのみご利用いただけます
自由改変可能な JavaScript (ECMAScript) コードです。いかなる保証もありません。

Notes

  • ワークフロー内に自動工程『入金 TSV 抽出処理』を組み込むことができるようになります。(ノーコード実装)
  • 通信設定には、Stripe APIキーの “シークレットキー” が必要です。
  • ワークフローアプリをテスト運用したい場合は、テスト用のシークレットキー “テストキー” を利用します。
    • sk_test_ で始まるテストキーを『HTTP認証設定 > トークン直接指定』に設定します。
      • Name: StripeSecretKeyForXYZ
      • Token: sk_test_123456789012345678901234
    • 本運用する場合は sk_live_ で始まるシークレットキーを設定します。抽出されるTSVは20列。(metadata 以外の全てのPayout要素)
  • 入金予定日は、”YYYY-MM-DD” または “YYYY-MM-DD HH:MM” の形式で指定します。Payout(入金) オブジェクトの詳細
    • [0] Payout ID: 入金データID
    • [1] Amount: 入金金額
    • [2] Arrival Date: 入金日(YYYY-MM-DD)
    • [3] Automatic: この入金が自動で作られたかどうか(“true” or “false”)
    • [4] Balance Transaction ID: 入金に関連する取引ID
    • [5] Created Date: 入金データ生成日(YYYY-MM-DD)
    • [6] Currency: 通貨(“usd” “jpy” etc.)
    • [7] Description: 説明
    • [8] Destination: 入金先の銀行口座(BANK_ACCOUNT)またはクレジットカード等(CARD)のオブジェクトID
    • [9] Failure Balance Transaction: (取引ID)
    • [10] Failure Code: Types of payout failures
    • [11] Failure Message
    • [12] Livemode
    • [13] Method: 入金方法(“standard” or “instant”)
    • [14] Original Payout: (Payout ID)
    • [15] Reversed By: (Payout ID)
    • [16] Source Type: (“card”, “fpx” or “bank_account”)
    • [17] Statement Descriptor
    • [18] Status: (“paid”,”pending”,”in_transit”,”canceled” or “failed”)
    • [19] Type: (“bank_account” or “card”)

Capture

See also