Google Sheets: ValueRanges, Sum
Google Sheets: ValueRanges, Sum
Sums the numeric data in a specified range. Cells that cannot be recognized as numbers are considered zero. It also supports simultaneous calculation of two ranges. For example, you can sum debits and credits at the same time.
Configs
  • U: Select HTTP_Authz Setting *
  • A1: Set FILE-ID in Drive *#{EL}
  • B1: Set ValueRange (e.g. “Sheet2!E:E”)#{EL}
  • C1: Select NUMERIC that stores Sum of a series of Cells (update)
  • B2: Set ValueRange (e.g. “Sheet2!E:E”)#{EL}
  • C2: Select NUMERIC that stores Sum of a series of Cells (update)
Script (click to open)
// GraalJS Script (engine type: 2)

//////// START "main()" /////////////////////////////////////////////////////////////////

main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const strAuthzSetting     = configs.get      ( "AuthzConfU" );   /// REQUIRED
  engine.log( " AutomatedTask Config: Authz Setting: " + strAuthzSetting );
const strInputfileId      = configs.get      ( "StrConfA1" );    /// REQUIRED
  if( strInputfileId    === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A1: FileID} is empty \n" );
  }
const strValueRange1      = configs.get      ( "StrConfB1" );    // NotRequired
const strValueRange2      = configs.get      ( "StrConfB2" );    // NotRequired
  if( strValueRange1 === "" && strValueRange2 === ""){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {ValueRange B1} and {ValueRange B2} are both empty \n" );
  }
const numPocketSum1       = configs.getObject( "SelectConfC1" ); // NotRequired
const numPocketSum2       = configs.getObject( "SelectConfC2" ); // NotRequired


//// == Data Retrieving / ワークフローデータの参照 ==
// (Nothing. Retrieved via Expression Language in Config Retrieving)


//// == Calculating / 演算 ==
/// Replace All Text via Requests
/// Sheets for Developers > API v4
/// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet
// request1, prepare
let request1Uri = "https://sheets.googleapis.com/v4/spreadsheets/" + strInputfileId + "/values:batchGet";
let request1    = httpClient.begin(); // HttpRequestWrapper
    request1    = request1.authSetting( strAuthzSetting ); // with "Authorization: Bearer XX"
    // https://questetra.zendesk.com/hc/en-us/articles/360024574471-R2300#HttpRequestWrapper
    request1    = request1.queryParam( "majorDimension", "COLUMNS" );
    request1    = request1.queryParam( "valueRenderOption", "UNFORMATTED_VALUE" ); 
                 // Even if formatted as currency, return "1.23" not "$1.23".
    request1    = request1.queryParam( "dateTimeRenderOption", "FORMATTED_STRING" ); 
                 // Date as strings (the spreadsheet locale) not SERIAL_NUMBER
    if( strValueRange1 !== "" ){
      request1  = request1.queryParam( "ranges", strValueRange1 );
    }
    if( strValueRange2 !== "" ){
      request1  = request1.queryParam( "ranges", strValueRange2 );
    }
// request1, try
const response1     = request1.get( request1Uri ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest1 Start: " + request1Uri );
const response1Code = response1.getStatusCode() + "";
const response1Body = response1.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response1Code );
if( response1Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response1Code + "\n" + response1Body + "\n" );
}
// response1, parse
/* engine.log( response1Body ); // debug
{
  "spreadsheetId": "1dSQcZSh2kF_KnuzgFydaOx7T24oURRJwpkCvB_qCV4w",
  "valueRanges": [
    {
      "range": "Sheet1!D1:D995",
      "majorDimension": "COLUMNS",
      "values": [
        [
          "Debit (借方)",
          "",
          2000000,
          1000000,
          500000,
          "",
          100000,
          400000,
          50000,
          50000,
          1000000,
          1000000
        ]
      ]
    },
    {
      "range": "Sheet1!E1:E995",
      "majorDimension": "COLUMNS",
      "values": [
        [
          "Credit (貸方)",
          4000000,
          "",
          "",
          "",
          500000
        ]
      ]
    }
  ]
}
*/
const response1Obj = JSON.parse( response1Body );
engine.log( " AutomatedTask ApiResponse: #of valueRanges: " + response1Obj.valueRanges.length );
let arrNumSums = [];
for( let i = 0; i < response1Obj.valueRanges.length; i++ ){
  let numTmp = 0;
  engine.log( " AutomatedTask ApiResponse: range: " + response1Obj.valueRanges[i].range );
  for( let j = 0; j < response1Obj.valueRanges[i].values.length; j++ ){
    for( let k = 0; k < response1Obj.valueRanges[i].values[j].length; k++ ){
      if( !isNaN( parseFloat( response1Obj.valueRanges[i].values[j][k] ) ) ){
        numTmp += parseFloat( response1Obj.valueRanges[i].values[j][k] );
      }
    }
  }
  arrNumSums.push( numTmp );
}
let numSum1 = 0;
let numSum2 = 0;
if(       strValueRange1 !== "" && strValueRange2 !== ""){
  numSum1 = arrNumSums[0];
  numSum2 = arrNumSums[1];
}else if( strValueRange1 !== "" && strValueRange2 === ""){
  numSum1 = arrNumSums[0];
}else if( strValueRange1 === "" && strValueRange2 !== ""){
  numSum2 = arrNumSums[0];
}else{
  throw new Error( "\n AutomatedTask UnexpectedSumError: " +
                   "\n" + response1Body + "\n" );
}


//// == Data Updating / ワークフローデータへの代入 ==
if( numPocketSum1    !== null ){
  engine.setData( numPocketSum1,    new java.math.BigDecimal( numSum1 ) );
}
if( numPocketSum2    !== null ){
  engine.setData( numPocketSum2,    new java.math.BigDecimal( numSum2 ) );
}


} //////// END "main()" /////////////////////////////////////////////////////////////////


/*
Notes:
- Imports the total number of numerical data in the spreadsheet as Workflow data automatically.
- The File ID can be obtained from the URI or the sharing settings screen.
    - https://docs.google.com/spreadsheets/d/{File-ID}/edit#gid=0
- How to specify the range depends on Google API A1 notation. (see APPENDIX)
- All numeric decorations on Sheet (such as decimal separators, rounding and suffixes) are ignored.
- The total value stored is truncated according to the Data Item definition. (e.g: 0.1 to 0, -0.1 to 0)
- Numeric judgment such as character strings depends on the specification of ECMAScript parseFloat().
    - The following examples all regarded as 3.14:
        - 3.14
        - 314e-2
        - 0.0314E+2
        - 3.14more non-digit characters
Notes-ja:
- スプレッドシート内の数値データ合計をWorkflowデータとして自動的に取り込めるようになります。
- ドキュメント ID は URL 等を参照します。
    - https://docs.google.com/spreadsheets/d/{File-ID}/edit#gid=0
- 範囲の指定方法は Google API の A1 notation に依ります (Appendix参照)
- スプレッドシート上での数値装飾(桁区切り表示や四捨五入表示や接尾語など)は全て無視されます
- 格納される合計値は、データ項目の定義に従って切り捨て処理されます。(例 0.1→0 -0.1→0)
- 文字列等の数値判定については、ECMAScript(JavaScript) parseFloat() の仕様に依ります
    - 以下のセル値はすべて "3.14" とみなされます。
        - 3.14
        - 314e-2
        - 0.0314E+2
        - 3.14more non-digit characters

APPENDIX-en
- A1 Nnotation
    - https://developers.google.com/sheets/api/guides/concepts#a1_notation
    - "Sheet1!A1:B2" refers to the first two cells in the top two rows of Sheet1.
    - "Sheet1!A:A" refers to all the cells in the first column of Sheet1.
    - "Sheet1!1:2" refers to the all the cells in the first two rows of Sheet1.
    - "Sheet1!A5:A" refers to all the cells of the first column of Sheet 1, from row 5 onward.
    - "A1:B2" refers to the first two cells in the top two rows of the first visible sheet.
    - "Sheet1" refers to all the cells in Sheet1.
- Setting example of "HTTP Authentication" (OAuth2)
    - 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.readonly
    - Client ID, Consumer Secret:
        - ( from https://console.developers.google.com/ )
        - Redirect URLs: https://s.questetra.net/oauth2callback
APPENDIX-ja
- A1記法(A1 Nnotation)
    - https://developers.google.com/sheets/api/guides/concepts#a1_notation
    - "Sheet1!A1:B2" 「Sheet1」の上2行の先頭2セル(合計4セル)を参照します。
    - "Sheet1!A:A" 「Sheet1」のA列の全てのセルを参照します。
    - "Sheet1!1:2" 「Sheet1」の上2行にあるすべてのセルを参照します。
    - "Sheet1!A5:A" 「Sheet1」のA列5行目以降のすべてのセルを参照します。
    - "A1:B2" 最初に表示されるシートの上2行の先頭2セル(合計4セル)を参照します。
    - "Sheet1" 「Sheet1」のすべてのセルを参照します。
- "HTTP認証"(OAuth2)の設定例
    - 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.readonly
    - Client ID, Consumer Secret:
        - ( from https://console.developers.google.com/ )
        - Redirect URLs: https://s.questetra.net/oauth2callback
*/

Download

2021-04-20 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/google-sheets-valueranges-sum-2021/
The Addon-import feature is available with Professional edition.

Notes

  • Allows you to import the numerical data total into the spreadsheet as Workflow data automatically.
  • The File ID can be obtained from the URI or the sharing settings screen.
    • https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0
  • How to specify the range depends on Google API A1 notation. (see APPENDIX)
  • All numeric decorations on the sheet (such as decimal separators, rounding and suffixes) are ignored.
  • The total value stored is truncated according to the Data Item definition. (e.g. 0.1 to 0, -0.1 to 0)
  • Numeric judgment of character strings depends on the specifications of ECMAScript parseFloat().
    • The following examples are all regarded as 3.14:
      • 3.14
      • 314e-2
      • 0.0314E+2
      • 3.14more non-digit characters

Capture

Sums the numerical data in the specified range. Cells that cannot be recognized as numbers are considered zero. It also supports simultaneous calculation of two ranges. For example, you can sum debits and credits at the same time.
Sums the numerical data in the specified range. Cells that cannot be recognized as numbers are considered zero. It also supports simultaneous calculation of two ranges. For example, you can sum debits and credits at the same time.

Appendix

See also

2 thoughts on “Google Sheets: ValueRanges, Sum”

  1. Pingback: Google Sheets: Values, Sum Numbers – Questetra Support

  2. Pingback: Google Sheets: ValueRanges, Export as TSV – Questetra Support

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: