Google Sheets Cells Sum

Stores the sum of specified range of a Google Spreadsheets into a Numeric type data item, and stores its communication log in a String type data item.

    A. Set OAuth2 Config Name (at [OAuth 2.0 Setting])Single-line inputRequired
    B. Select STRING/SELECT for Spreadsheet ID (Non-existent Error)Data selectString(single line)orSelect(radio/select/search)Required
    C. Select STRING/SELECT for Sheet Title (Non-existent Error)Data selectString(single line)orSelect(radio/select/search)Required
    D. Select STRING/SELECT for a Range (e.g. "D2:D")Data selectString(single line)orSelect(radio/select/search)Required
    E. Select NUMERIC DATA for Sum of a Series of Numbers (update)Data selectNumericRequired
    F. Select STRING DATA for Access Log (update)Data selectString(multiple line)Required
    -variable namevariable label
    Aconf_OAuth2Set OAuth2 Config Name (at [OAuth 2.0 Setting])
    Bconf_DataIdBSelect STRING/SELECT for Spreadsheet ID (Non-existent Error)
    Cconf_DataIdCSelect STRING/SELECT for Sheet Title (Non-existent Error)
    Dconf_DataIdDSelect STRING/SELECT for a Range (e.g. "D2:D")
    Econf_DataIdESelect NUMERIC DATA for Sum of a Series of Numbers (update)
    Fconf_DataIdFSelect STRING DATA for Access Log (update)
    // 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’: Google Spreadsheet ID ** stored in the Data Item selected at config B
    • C’: Sheet Title stored in the Data Item selected at config C
    • D’: Strings *** for specifying Cell range in the Data Item selected at config D
    • The sum of the cells of range D’ of the sheet C’ of Spreadsheet B’ will be stored in the Data Item selected at E
    • The communication logs are stored in the data item selected at F
    • “+3.14”: 3.14, “314e-2”: 3.14, “090”: 90, “2016-12-23”: 2016, ‘Begin with letter’: 0 (parseFloat() sum)
    • ** https://docs.google.com/spreadsheets/d/1exampleEXAMPLEexampleEXAMPLEexampleEXAMPLE0/edit#gid=0
    • *** Specify the sum of all cells of column A as “A:A”, the sum of the four cells of till the second row of column A and B as “A1:B2” (Reference: A1 notation)
    • Comma removal version corresponds to the case where the decimal separator “,” is mixed