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