Google Sheets のセル合計

指定した Google Sheets の領域にある数値合計を数値型データ項目に格納し、通信ログを文字型データ項目に格納します。

    A. OAuth2通信許可設定名 (←[OAuth 2.0 設定])単一行記述必須
    B. Spreadsheet ID が格納されている文字列型データor選択肢型データを選択してください(存在しない場合、エラー)データ項目選択文字列型(単一行)or選択型(ラジオ/セレクト/検索)必須
    C. Sheet タイトルが格納されている文字列型データor選択肢型データを選択してください(存在しない場合、エラー)データ項目選択文字列型(単一行)or選択型(ラジオ/セレクト/検索)必須
    D. 領域が格納されている文字列型データor選択肢型データを選択してください (例 "D1:D1", "D2:D")データ項目選択文字列型(単一行)or選択型(ラジオ/セレクト/検索)必須
    E. 合計値が格納される数値型データを選択してください (更新)データ項目選択数値型必須
    F. 通信ログが格納される文字列型データを選択してください (更新)データ項目選択文字列型(複数行)必須
    -設定変数名設定ラベル
    Aconf_OAuth2OAuth2通信許可設定名 (←[OAuth 2.0 設定])
    Bconf_DataIdBSpreadsheet ID が格納されている文字列型データor選択肢型データを選択してください(存在しない場合、エラー)
    Cconf_DataIdCSheet タイトルが格納されている文字列型データor選択肢型データを選択してください(存在しない場合、エラー)
    Dconf_DataIdD領域が格納されている文字列型データor選択肢型データを選択してください (例 "D1:D1", "D2:D")
    Econf_DataIdE合計値が格納される数値型データを選択してください (更新)
    Fconf_DataIdF通信ログが格納される文字列型データを選択してください (更新)
    // Sum of a series of numbers in a Google Spreadsheet (ver. 20160913)
    // (c) 2016, Questetra, Inc. (the MIT License)
    
    // OAuth2 config
    // Authorization Endpoint URL: https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
    // Token Endpoint URL: https://accounts.google.com/o/oauth2/token
    // Scope: https://www.googleapis.com/auth/spreadsheets
    // Consumer Key: (Get by Google Developers Console)
    // Consumer Secret: (Get by Google Developers Console)
    
    //// == 工程コンフィグの参照 / Config Retrieving ==
    var oauth2 = configs.get( "conf_OAuth2" );
    var dataIdB = configs.get( "conf_DataIdB" ); // (returns Number)
    var dataIdC = configs.get( "conf_DataIdC" ); // (returns Number)
    var dataIdD = configs.get( "conf_DataIdD" ); // (returns Number)
    var dataIdE = configs.get( "conf_DataIdE" ); // (returns Number)
    var dataIdF = configs.get( "conf_DataIdF" ); // (returns Number)
    
    //// == ワークフローデータの参照 / Data Retrieving ==
    var docId = "";
    if( data.get( dataIdB ) instanceof java.util.ArrayList ){
      docId = data.get( dataIdB ).get(0).getValue() + "";
    }else if( data.get( dataIdB ) instanceof java.lang.String ){
      docId = data.get( dataIdB ) + "";
    }
    var sheet = "";
    if( data.get( dataIdC ) instanceof java.util.ArrayList ){
      sheet = data.get( dataIdC ).get(0).getValue() + "";
    }else if( data.get( dataIdC ) instanceof java.lang.String ){
      sheet = data.get( dataIdC ) + "";
    }
    var range = "";
    if( data.get( dataIdD ) instanceof java.util.ArrayList ){
      range = data.get( dataIdD ).get(0).getValue() + "";
    }else if( data.get( dataIdD ) instanceof java.lang.String ){
      range = data.get( dataIdD ) + "";
    }
    
    //// == 演算 / Calculating ==
    var accessLog = "";
    var responseJson = "";
    var sum = 0;
    var token = httpClient.getOAuth2Token( oauth2 );
    
    try{
      var uri = "https://sheets.googleapis.com/v4/spreadsheets/";
      uri += docId;
      uri += "/values/";
      uri += sheet;
      uri += "!";
      uri += range;
    
      var response = httpClient.begin()
        .bearer(token)
        .get( uri ); 
      accessLog += "---GET request--- " + response.getStatusCode() + "\n";
      responseJson = response.getResponseAsString();
      if( response.getStatusCode() == 200 ){
        var jsonObj = JSON.parse( responseJson );
        for( var i = 0; i < jsonObj.values.length; i++ ){
          for( var j = 0; j < jsonObj.values[i].length; j++ ){
            if( !isNaN( parseFloat( jsonObj.values[i][j] ) ) ){
              sum += parseFloat( jsonObj.values[i][j] );
            }
          }
        }
      }
    }catch(e){
      accessLog += e.message + "\n";
    }
    // for Debug
    // accessLog += responseJson + "\n";
    
    //// == ワークフローデータへの代入 / Data Updating ==
    retVal.put( dataIdE, java.math.BigDecimal( sum ) );
    retVal.put( dataIdF, accessLog );
    

    • B’: Bで選択したデータ項目に格納されている Google Spreadsheet ID **
    • C’: Cで選択したデータ項目に格納されている Sheet Title
    • D’: Dで選択したデータ項目に格納されている セル領域を指定する文字列 ***
    • Spreadsheet B’ の Sheet C’ の領域 D’ にある数値合計が、Eで選択したデータ項目に格納されます。
    • 通信ログがFで選択したデータ項目に格納されます
    • “+3.14″: 3.14、”314e-2″: 3.14、”090″: 90、”2016-12-23”: 2016、’文字始まり’: 0 となります(parseFloat() 加算)
    • ** https://docs.google.com/spreadsheets/d/1exampleEXAMPLEexampleEXAMPLEexampleEXAMPLE0/edit#gid=0
    • *** A列の全セル合計は「A:A」、A列B列の2行目まで4セルの合計は「A1:B2」と指定します(参考:A1 notation
    • カンマ除去版は桁区切り文字「,」が混入している場合に対応します