Google Sheets: Values, Sum Numbers

Google Sheets: Values, Sum Numbers

Sums the numeric values in the specified range. Values that cannot be recognized as numeric values are regarded as zero. Two ranges of simultaneous calculations are also supported. e.g. The budgeting progress in the general ledger is summarized.

2019-10-17 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/google-sheets-values-sum-numbers/

Configs
  • A: Select OAuth2 Config Name (at [OAuth 2.0 Setting]) *
  • B: Set Document-ID of Spreadsheet File (44 chars in File URI) * #{EL}
  • C1: Set ValueRange (e.g. “Sheet1!D2:D” cf.’A1 Notation’) * #{EL}
  • D1: Select NUMERIC DATA for Total (update) *
  • C2: Set ValueRange (e.g. “Sheet2!E:E100”) #{EL}
  • D2: Select NUMERIC DATA for Total (update)
Script
// (c) 2019, Questetra, Inc. (the MIT License)

//// == OAuth2 Setting example ==
// 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:
//  ( from https://console.developers.google.com/ )
// Consumer Secret:
//  ( from https://console.developers.google.com/ )
//  *Redirect URL of Webapp OAuth-Client-ID: "https://s.questetra.net/oauth2callback"

//// 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.

//////// START "main()" ////////
main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const oauth2      = configs.get( "conf_OAuth2"  ) + "";     // required
const documentId  = configs.get( "conf_DocumentId" ) + "";  // required
const valueRange1 = configs.get( "conf_ValueRange1" ) + ""; // required
const dataIdD1    = configs.get( "conf_DataIdD1" ) + "";    // required
const valueRange2 = configs.get( "conf_ValueRange2" ) + ""; // not required
const dataIdD2    = configs.get( "conf_DataIdD2" ) + "";    // not required
// 'java.lang.String' (String Obj) to javascript primitive 'string'

engine.log( " AutomatedTask Config: Document ID: " + documentId );
engine.log( " AutomatedTask Config: ValueRange1: " + valueRange1 );
engine.log( " AutomatedTask Config: ValueRange2: " + valueRange2 );


//// == Data Retrieving / ワークフローデータの参照 ==
// (nothing)


//// == Calculating / 演算 ==
/// obtain OAuth2 Access Token
const token   = httpClient.getOAuth2Token( oauth2 );

/// get one or two ranges of values via Google Sheets API v4
// https://developers.google.com/sheets/api/reference/rest
//  /v4/spreadsheets.values/batchGet
let apiRequest = httpClient.begin(); // HttpRequestWrapper
    apiRequest = apiRequest.bearer( token );
    apiRequest = apiRequest.queryParam( "ranges", valueRange1 ); 
    if( valueRange2 !== ""){
      apiRequest = apiRequest.queryParam( "ranges", valueRange2 ); 
    }
    apiRequest = apiRequest.queryParam( "majorDimension", "COLUMNS" ); 
    apiRequest = apiRequest.queryParam( "valueRenderOption", "UNFORMATTED_VALUE" ); 
                 // Even if formatted as currency, return "1.23" not "$1.23".
    apiRequest = apiRequest.queryParam( "dateTimeRenderOption", "FORMATTED_STRING" ); 
                 // Date as strings (the spreadsheet locale) not SERIAL_NUMBER
const apiUri = "https://sheets.googleapis.com/v4/spreadsheets/" +
                documentId + "/values:batchGet";
engine.log( " AutomatedTask Trying: GET " + apiUri );
const response = apiRequest.get( apiUri );
const responseCode = response.getStatusCode() + "";
engine.log( " AutomatedTask ApiResponse: Status " + responseCode );
if( responseCode !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
         responseCode + "\n" + response.getResponseAsString() + "\n" );
}
const responseStr = response.getResponseAsString() + "";
const responseObj = JSON.parse( responseStr );
engine.log( responseStr );

let sum1 = 0;
  for( let j = 0; j < responseObj.valueRanges[0].values.length; j++ ){
    for( let k = 0; k < responseObj.valueRanges[0].values[j].length; k++ ){
      if( !isNaN( parseFloat( responseObj.valueRanges[0].values[j][k] ) ) ){
        sum1 += parseFloat( responseObj.valueRanges[0].values[j][k] );
      }
    }
  }
engine.log( " AutomatedTask Sum of ValueRange1: " + sum1 );

let sum2 = 0;
if( valueRange2 !== ""){
  for( let j = 0; j < responseObj.valueRanges[1].values.length; j++ ){
    for( let k = 0; k < responseObj.valueRanges[1].values[j].length; k++ ){
      if( !isNaN( parseFloat( responseObj.valueRanges[1].values[j][k] ) ) ){
        sum2 += parseFloat( responseObj.valueRanges[1].values[j][k] );
      }
    }
  }
  engine.log( " AutomatedTask Sum of ValueRange2: " + sum2 );
}


//// == Data Updating / ワークフローデータへの代入 ==
engine.setDataByNumber( dataIdD1, new java.math.BigDecimal( sum1 ) );
if ( dataIdD2 !== "" ){ 
  engine.setDataByNumber( dataIdD2, new java.math.BigDecimal( sum2 ) );
}


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

Download

Capture

Notes

  1. Document ID is contained in the URL. https://docs.google.com/spreadsheets/d/(Sheet ID)/edit#gid=0
  2. All numeric decorations on the spreadsheet (such as decimal separators, rounding and suffixes) are ignored.
  3. The total value stored is truncated according to the Data Item definition. (e.g: 0.1 to 0, -0.1 to 0)
  4. Numeric judgment such as character strings depends on the specification of ECMAScript (JavaScript) parseFloat().
    1. The following examples all regarded as 3.14:
    2. 3.14
    3. 314e-2
    4. 0.0314E+2
    5. 3.14more non-digit characters
  5. How to specify the range depends on Google API A1 notation.
    1. “Sheet1!A1:B2” refers to the first two cells in the top two rows of Sheet1.
    2. “Sheet1!A:A” refers to all the cells in the first column of Sheet1.
    3. “Sheet1!1:2” refers to the all the cells in the first two rows of Sheet1.
    4. “Sheet1!A5:A” refers to all the cells of the first column of Sheet 1, from row 5 onward.
    5. “A1:B2” refers to the first two cells in the top two rows of the first visible sheet.
    6. “Sheet1” refers to all the cells in Sheet1.

See also

1 thought on “Google Sheets: Values, Sum Numbers”

  1. Pingback: Google Sheets: Values, 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: