Google Sheets: ValueRanges, Sum<\/div>
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.<\/div><\/div>\n\n\n
<\/span>Configs<\/div>
\n
- \n
- <\/i><\/span> U: Select HTTP_Authz Setting *<\/span><\/li>\n
- <\/i><\/span> A1: Set FILE-ID in Drive *<\/span>#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> B1: Set ValueRange (e.g. “Sheet2!E:E”)#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> C1: Select NUMERIC that stores Sum of a series of Cells (update)<\/li>\n
- <\/i><\/span> B2: Set ValueRange (e.g. “Sheet2!E:E”)#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> C2: Select NUMERIC that stores Sum of a series of Cells (update)<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n
<\/span>Script (click to open)<\/div>\n\n\n\n\/\/ GraalJS Script (engine type: 2)\n\n\/\/\/\/\/\/\/\/ START "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\nmain();\nfunction main(){ \n\n\/\/\/\/ == Config Retrieving \/ \u5de5\u7a0b\u30b3\u30f3\u30d5\u30a3\u30b0\u306e\u53c2\u7167 ==\nconst strAuthzSetting = configs.get ( "AuthzConfU" ); \/\/\/ REQUIRED\n engine.log( " AutomatedTask Config: Authz Setting: " + strAuthzSetting );\nconst strInputfileId = configs.get ( "StrConfA1" ); \/\/\/ REQUIRED\n if( strInputfileId === "" ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {A1: FileID} is empty \\n" );\n }\nconst strValueRange1 = configs.get ( "StrConfB1" ); \/\/ NotRequired\nconst strValueRange2 = configs.get ( "StrConfB2" ); \/\/ NotRequired\n if( strValueRange1 === "" && strValueRange2 === ""){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {ValueRange B1} and {ValueRange B2} are both empty \\n" );\n }\nconst numPocketSum1 = configs.getObject( "SelectConfC1" ); \/\/ NotRequired\nconst numPocketSum2 = configs.getObject( "SelectConfC2" ); \/\/ NotRequired\n\n\n\/\/\/\/ == Data Retrieving \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u306e\u53c2\u7167 ==\n\/\/ (Nothing. Retrieved via Expression Language in Config Retrieving)\n\n\n\/\/\/\/ == Calculating \/ \u6f14\u7b97 ==\n\/\/\/ Replace All Text via Requests\n\/\/\/ Sheets for Developers > API v4\n\/\/\/ https:\/\/developers.google.com\/sheets\/api\/reference\/rest\/v4\/spreadsheets.values\/batchGet\n\/\/ request1, prepare\nlet request1Uri = "https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/" + strInputfileId + "\/values:batchGet";\nlet request1 = httpClient.begin(); \/\/ HttpRequestWrapper\n request1 = request1.authSetting( strAuthzSetting ); \/\/ with "Authorization: Bearer XX"\n \/\/ https:\/\/questetra.zendesk.com\/hc\/en-us\/articles\/360024574471-R2300#HttpRequestWrapper\n request1 = request1.queryParam( "majorDimension", "COLUMNS" );\n request1 = request1.queryParam( "valueRenderOption", "UNFORMATTED_VALUE" ); \n \/\/ Even if formatted as currency, return "1.23" not "$1.23".\n request1 = request1.queryParam( "dateTimeRenderOption", "FORMATTED_STRING" ); \n \/\/ Date as strings (the spreadsheet locale) not SERIAL_NUMBER\n if( strValueRange1 !== "" ){\n request1 = request1.queryParam( "ranges", strValueRange1 );\n }\n if( strValueRange2 !== "" ){\n request1 = request1.queryParam( "ranges", strValueRange2 );\n }\n\/\/ request1, try\nconst response1 = request1.get( request1Uri ); \/\/ HttpResponseWrapper\nengine.log( " AutomatedTask ApiRequest1 Start: " + request1Uri );\nconst response1Code = response1.getStatusCode() + "";\nconst response1Body = response1.getResponseAsString() + "";\nengine.log( " AutomatedTask ApiResponse Status: " + response1Code );\nif( response1Code !== "200"){\n throw new Error( "\\n AutomatedTask UnexpectedResponseError: " +\n response1Code + "\\n" + response1Body + "\\n" );\n}\n\/\/ response1, parse\n\/* engine.log( response1Body ); \/\/ debug\n{\n "spreadsheetId": "1dSQcZSh2kF_KnuzgFydaOx7T24oURRJwpkCvB_qCV4w",\n "valueRanges": [\n {\n "range": "Sheet1!D1:D995",\n "majorDimension": "COLUMNS",\n "values": [\n [\n "Debit (\u501f\u65b9)",\n "",\n 2000000,\n 1000000,\n 500000,\n "",\n 100000,\n 400000,\n 50000,\n 50000,\n 1000000,\n 1000000\n ]\n ]\n },\n {\n "range": "Sheet1!E1:E995",\n "majorDimension": "COLUMNS",\n "values": [\n [\n "Credit (\u8cb8\u65b9)",\n 4000000,\n "",\n "",\n "",\n 500000\n ]\n ]\n }\n ]\n}\n*\/\nconst response1Obj = JSON.parse( response1Body );\nengine.log( " AutomatedTask ApiResponse: #of valueRanges: " + response1Obj.valueRanges.length );\nlet arrNumSums = [];\nfor( let i = 0; i < response1Obj.valueRanges.length; i++ ){\n let numTmp = 0;\n engine.log( " AutomatedTask ApiResponse: range: " + response1Obj.valueRanges[i].range );\n for( let j = 0; j < response1Obj.valueRanges[i].values.length; j++ ){\n for( let k = 0; k < response1Obj.valueRanges[i].values[j].length; k++ ){\n if( !isNaN( parseFloat( response1Obj.valueRanges[i].values[j][k] ) ) ){\n numTmp += parseFloat( response1Obj.valueRanges[i].values[j][k] );\n }\n }\n }\n arrNumSums.push( numTmp );\n}\nlet numSum1 = 0;\nlet numSum2 = 0;\nif( strValueRange1 !== "" && strValueRange2 !== ""){\n numSum1 = arrNumSums[0];\n numSum2 = arrNumSums[1];\n}else if( strValueRange1 !== "" && strValueRange2 === ""){\n numSum1 = arrNumSums[0];\n}else if( strValueRange1 === "" && strValueRange2 !== ""){\n numSum2 = arrNumSums[0];\n}else{\n throw new Error( "\\n AutomatedTask UnexpectedSumError: " +\n "\\n" + response1Body + "\\n" );\n}\n\n\n\/\/\/\/ == Data Updating \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 ==\nif( numPocketSum1 !== null ){\n engine.setData( numPocketSum1, new java.math.BigDecimal( numSum1 ) );\n}\nif( numPocketSum2 !== null ){\n engine.setData( numPocketSum2, new java.math.BigDecimal( numSum2 ) );\n}\n\n\n} \/\/\/\/\/\/\/\/ END "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\n\n\/*\nNotes:\n- Imports the total number of numerical data in the spreadsheet as Workflow data automatically.\n- The File ID can be obtained from the URI or the sharing settings screen.\n - https:\/\/docs.google.com\/spreadsheets\/d\/{File-ID}\/edit#gid=0\n- How to specify the range depends on Google API A1 notation. (see APPENDIX)\n- All numeric decorations on Sheet (such as decimal separators, rounding and suffixes) are ignored.\n- The total value stored is truncated according to the Data Item definition. (e.g: 0.1 to 0, -0.1 to 0)\n- Numeric judgment such as character strings depends on the specification of ECMAScript parseFloat().\n - The following examples all regarded as 3.14:\n - 3.14\n - 314e-2\n - 0.0314E+2\n - 3.14more non-digit characters\nNotes-ja:\n- \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u5185\u306e\u6570\u5024\u30c7\u30fc\u30bf\u5408\u8a08\u3092Workflow\u30c7\u30fc\u30bf\u3068\u3057\u3066\u81ea\u52d5\u7684\u306b\u53d6\u308a\u8fbc\u3081\u308b\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002\n- \u30c9\u30ad\u30e5\u30e1\u30f3\u30c8 ID \u306f URL \u7b49\u3092\u53c2\u7167\u3057\u307e\u3059\u3002\n - https:\/\/docs.google.com\/spreadsheets\/d\/{File-ID}\/edit#gid=0\n- \u7bc4\u56f2\u306e\u6307\u5b9a\u65b9\u6cd5\u306f Google API \u306e A1 notation \u306b\u4f9d\u308a\u307e\u3059 (Appendix\u53c2\u7167)\n- \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u4e0a\u3067\u306e\u6570\u5024\u88c5\u98fe\uff08\u6841\u533a\u5207\u308a\u8868\u793a\u3084\u56db\u6368\u4e94\u5165\u8868\u793a\u3084\u63a5\u5c3e\u8a9e\u306a\u3069\uff09\u306f\u5168\u3066\u7121\u8996\u3055\u308c\u307e\u3059\n- \u683c\u7d0d\u3055\u308c\u308b\u5408\u8a08\u5024\u306f\u3001\u30c7\u30fc\u30bf\u9805\u76ee\u306e\u5b9a\u7fa9\u306b\u5f93\u3063\u3066\u5207\u308a\u6368\u3066\u51e6\u7406\u3055\u308c\u307e\u3059\u3002\uff08\u4f8b 0.1\u21920 -0.1\u21920\uff09\n- \u6587\u5b57\u5217\u7b49\u306e\u6570\u5024\u5224\u5b9a\u306b\u3064\u3044\u3066\u306f\u3001ECMAScript\uff08JavaScript\uff09 parseFloat() \u306e\u4ed5\u69d8\u306b\u4f9d\u308a\u307e\u3059\n - \u4ee5\u4e0b\u306e\u30bb\u30eb\u5024\u306f\u3059\u3079\u3066 "3.14" \u3068\u307f\u306a\u3055\u308c\u307e\u3059\u3002\n - 3.14\n - 314e-2\n - 0.0314E+2\n - 3.14more non-digit characters\n\nAPPENDIX-en\n- A1 Nnotation\n - https:\/\/developers.google.com\/sheets\/api\/guides\/concepts#a1_notation\n - "Sheet1!A1:B2" refers to the first two cells in the top two rows of Sheet1.\n - "Sheet1!A:A" refers to all the cells in the first column of Sheet1.\n - "Sheet1!1:2" refers to the all the cells in the first two rows of Sheet1.\n - "Sheet1!A5:A" refers to all the cells of the first column of Sheet 1, from row 5 onward.\n - "A1:B2" refers to the first two cells in the top two rows of the first visible sheet.\n - "Sheet1" refers to all the cells in Sheet1.\n- Setting example of "HTTP Authentication" (OAuth2)\n - Authorization Endpoint URL:\n - https:\/\/accounts.google.com\/o\/oauth2\/auth?access_type=offline&approval_prompt=force\n - Token Endpoint URL:\n - https:\/\/accounts.google.com\/o\/oauth2\/token\n - Scope:\n - https:\/\/www.googleapis.com\/auth\/spreadsheets.readonly\n - Client ID, Consumer Secret:\n - ( from https:\/\/console.developers.google.com\/ )\n - Redirect URLs: https:\/\/s.questetra.net\/oauth2callback\nAPPENDIX-ja\n- A1\u8a18\u6cd5\uff08A1 Nnotation\uff09\n - https:\/\/developers.google.com\/sheets\/api\/guides\/concepts#a1_notation\n - "Sheet1!A1:B2" \u300cSheet1\u300d\u306e\u4e0a2\u884c\u306e\u5148\u982d2\u30bb\u30eb\uff08\u5408\u8a084\u30bb\u30eb\uff09\u3092\u53c2\u7167\u3057\u307e\u3059\u3002\n - "Sheet1!A:A" \u300cSheet1\u300d\u306eA\u5217\u306e\u5168\u3066\u306e\u30bb\u30eb\u3092\u53c2\u7167\u3057\u307e\u3059\u3002\n - "Sheet1!1:2" \u300cSheet1\u300d\u306e\u4e0a2\u884c\u306b\u3042\u308b\u3059\u3079\u3066\u306e\u30bb\u30eb\u3092\u53c2\u7167\u3057\u307e\u3059\u3002\n - "Sheet1!A5:A" \u300cSheet1\u300d\u306eA\u52175\u884c\u76ee\u4ee5\u964d\u306e\u3059\u3079\u3066\u306e\u30bb\u30eb\u3092\u53c2\u7167\u3057\u307e\u3059\u3002\n - "A1:B2" \u6700\u521d\u306b\u8868\u793a\u3055\u308c\u308b\u30b7\u30fc\u30c8\u306e\u4e0a2\u884c\u306e\u5148\u982d2\u30bb\u30eb\uff08\u5408\u8a084\u30bb\u30eb\uff09\u3092\u53c2\u7167\u3057\u307e\u3059\u3002\n - "Sheet1" \u300cSheet1\u300d\u306e\u3059\u3079\u3066\u306e\u30bb\u30eb\u3092\u53c2\u7167\u3057\u307e\u3059\u3002\n- "HTTP\u8a8d\u8a3c"\uff08OAuth2\uff09\u306e\u8a2d\u5b9a\u4f8b\n - Authorization Endpoint URL:\n - https:\/\/accounts.google.com\/o\/oauth2\/auth?access_type=offline&approval_prompt=force\n - Token Endpoint URL:\n - https:\/\/accounts.google.com\/o\/oauth2\/token\n - Scope:\n - https:\/\/www.googleapis.com\/auth\/spreadsheets.readonly\n - Client ID, Consumer Secret:\n - ( from https:\/\/console.developers.google.com\/ )\n - Redirect URLs: https:\/\/s.questetra.net\/oauth2callback\n*\/\n\n<\/code><\/pre><\/div>\n\n\n<\/div><\/div>\n\n\n\n<\/a><\/figure><\/div>\n\n\n\n
<\/i> Download<\/h3>\n\n\n\n
- Google-Sheets-ValueRanges-Sum-2021.xml<\/a><\/li><\/ul>\n\n\n\n
2021-04-20 (C) Questetra, Inc. (MIT License)
https:\/\/support.questetra.com\/addons\/google-sheets-valueranges-sum-2021\/<\/a>
<\/i> The Addon-import feature is available with Professional<\/strong><\/span> edition.<\/p>\n\n\n<\/div>\n\n\n\n<\/i> Notes<\/h3>\n\n\n\n
- Allows you to import the numerical data total into the spreadsheet as Workflow data automatically.<\/li>
- The File ID can be obtained from the URI or the sharing settings screen.
https:\/\/docs.google.com\/spreadsheets\/d\/SPREADSHEETID\/edit#gid=0<\/code><\/li><\/ul><\/li>- How to specify the range depends on Google API A1 notation. (see APPENDIX)<\/li>
- All numeric decorations on the sheet (such as decimal separators, rounding and suffixes) are ignored.<\/li>
- The total value stored is truncated according to the Data Item definition. (e.g. 0.1 to 0, -0.1 to 0)<\/li>
- Numeric judgment of character strings depends on the specifications of ECMAScript parseFloat().
- The following examples are all regarded as 3.14:
- 3.14<\/li>
- 314e-2<\/li>
- 0.0314E+2<\/li>
- 3.14more non-digit characters<\/li><\/ul><\/li><\/ul><\/li><\/ul>\n\n\n\n
<\/i> Capture<\/h3>\n\n\n\n
![\"Sums](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/04\/Google-Sheets-ValueRanges-Sum-execution-en.png?resize=1200%2C675&ssl=1\")
<\/a><\/figure>\n\n\n\n![\"Sums](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/04\/Google-Sheets-ValueRanges-Sum-config-en-914x630.png?resize=914%2C630&ssl=1\")
<\/a><\/figure>\n\n\n\n<\/i> Appendix<\/h3>\n\n\n\n
- A1 Notation
- https:\/\/developers.google.com\/sheets\/api\/guides\/concepts#a1_notation<\/a><\/li>
- “Sheet1!A1:B2” refers to the first two cells in the top two rows of Sheet1.<\/li>
- “Sheet1!A:A” refers to all the cells in the first column of Sheet1.<\/li>
- “Sheet1!1:2” refers to the all the cells in the first two rows of Sheet1.<\/li>
- “Sheet1!A5:A” refers to all the cells of the first column of Sheet 1, from row 5 onward.<\/li>
- “A1:B2” refers to the first two cells in the top two rows of the first visible sheet.<\/li>
- “Sheet1” refers to all the cells in Sheet1.<\/li><\/ul><\/li>
- Setting example of “HTTP Authentication” (OAuth2)
- Authorization Endpoint URL:
- https:\/\/accounts.google.com\/o\/oauth2\/auth?access_type=offline&approval_prompt=force<\/a><\/li><\/ul><\/li>
- Token Endpoint URL:
- https:\/\/accounts.google.com\/o\/oauth2\/auth?access_type=offline&approval_prompt=force<\/a><\/li><\/ul><\/li>
- https:\/\/developers.google.com\/sheets\/api\/guides\/concepts#a1_notation<\/a><\/li>
- A1 Notation
- <\/i><\/span> A1: Set FILE-ID in Drive *<\/span>#{EL}<\/sup><\/span><\/li>\n