Google スプレッドシート: 範囲データ, 数値合計

Google スプレッドシート: 範囲データ, 数値合計 (Google Sheets: Values, Sum Numbers)

指定範囲にある数値を合計します。数値と認識できないセルの値はゼロとみなされます。2範囲の同時計算もサポートします。たとえば総勘定元帳の借方貸方を同時に合算できるため、予算の消化進捗をスムーズに集計できるようになります。

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

Configs
  • A: 通信許可の設定名([OAuth2.0設定]メニュー)を選択してください *
  • B: Spreadsheetファイルの Document-ID をセットしてください (ファイルURI内の44文字) * #{EL}
  • C1: データ範囲をセットしてください (例 “Sheet1!D2:D” cf. A1 Notation) * #{EL}
  • D1: 合計の値が格納される数値型データを選択してください (更新) *
  • C2: データ範囲をセットしてください (例 “Sheet1!E:E100”) #{EL}
  • D2: 合計の値が格納される数値型データを選択してください (更新)
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

Google Sheets: Values, Sum Numbers

Notes

  1. ドキュメント ID は、URL に含まれています。https://docs.google.com/spreadsheets/d/(sheet ID)/edit#gid=0
  2. スプレッドシート上での数値装飾(桁区切り表示や四捨五入表示や接尾語など)は全て無視されます
  3. 格納される合計値は、データ項目の定義に従って切り捨て処理されます。(例 0.1→0 -0.1→0)
  4. 文字列等の数値判定については、ECMAScript(JavaScript) parseFloat() の仕様に依ります
    1. 以下の値はすべて 3.14 とみなされます。
    2. 3.14
    3. 314e-2
    4. 0.0314E+2
    5. 3.14more non-digit characters
  5. 範囲の指定方法は Google API の A1 notation に依ります
    1. “Sheet1!A1:B2” は、シート名 “Sheet1” の、上から2行の先頭から2セルを指します
    2. “Sheet1!A:A” は、シート名 “Sheet1” の、先頭列(A列)の全てのセルを指します
    3. “Sheet1!1:2” は、シート名 “Sheet1” の、上から2行の全てのセルを指します
    4. “Sheet1!A5:A” は、シート名 “Sheet1” の、先頭列(A列)の5行目(A5セル)以降すべてのセルを指します
    5. “A1:B2” は、先頭に表示されているシートの、上から2行のそれぞれ先頭から2セル(2列)を指します
    6. “Sheet1” は、シート名 “Sheet1” の、全てのセルを指します

See also

コメントを残す

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

%d人のブロガーが「いいね」をつけました。