{"id":81014,"date":"2019-10-18T11:45:57","date_gmt":"2019-10-18T02:45:57","guid":{"rendered":"https:\/\/support.questetra.com\/?p=81014"},"modified":"2022-06-01T15:39:52","modified_gmt":"2022-06-01T06:39:52","slug":"google-sheets-values-sum-numbers","status":"publish","type":"post","link":"https:\/\/support.questetra.com\/en\/addons\/google-sheets-values-sum-numbers\/","title":{"rendered":"Google Sheets: Values, Sum Numbers"},"content":{"rendered":"<div class=\"su-note\"  style=\"border-color:#e5dab2;border-radius:3px;-moz-border-radius:3px;-webkit-border-radius:3px;\"><div class=\"su-note-inner su-u-clearfix su-u-trim\" style=\"background-color:#FFF4CC;border-color:#ffffff;color:#333333;border-radius:3px;-moz-border-radius:3px;-webkit-border-radius:3px;\">\n<h3><i class=\"fal fa-exclamation-circle\"><\/i> PAGE UPDATED<\/h3>\n<div style=\"text-align: center;\"><i class=\"fal fa-truck fa-lg\"><\/i> <a href=\"https:\/\/support.questetra.com\/en\/addons\/google-sheets-valueranges-sum-2021\/\">https:\/\/support.questetra.com\/en\/addons\/google-sheets-valueranges-sum-2021\/<\/a><\/div>\n<\/div><\/div>\n\n\n\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"68\" data-attachment-id=\"89186\" data-permalink=\"https:\/\/support.questetra.com\/en\/maintenance\/maintenance-20251117\/attachment\/professional-banner-en\/\" data-orig-file=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/06\/professional-banner-en.png?fit=1200%2C68&amp;ssl=1\" data-orig-size=\"1200,68\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"professional-banner-en\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/06\/professional-banner-en.png?fit=1024%2C58&amp;ssl=1\" src=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/06\/professional-banner-en.png?resize=1200%2C68&#038;ssl=1\" alt=\"\" class=\"wp-image-89186\" srcset=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/06\/professional-banner-en.png?w=1200&amp;ssl=1 1200w, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/06\/professional-banner-en.png?resize=600%2C34&amp;ssl=1 600w, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/06\/professional-banner-en.png?resize=1024%2C58&amp;ssl=1 1024w, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/06\/professional-banner-en.png?resize=768%2C44&amp;ssl=1 768w\" sizes=\"auto, (max-width: 1000px) 100vw, 1000px\" \/><\/figure>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n<div class=\"su-box su-box-style-default\" id=\"\" style=\"border-color:#003e04;border-radius:0px;max-width:none\"><div class=\"su-box-title\" style=\"background-color:#267137;color:#ffffff;border-top-left-radius:0px;border-top-right-radius:0px\">Google Sheets: Values, Sum Numbers<\/div><div class=\"su-box-content su-u-clearfix su-u-trim\" style=\"border-bottom-left-radius:0px;border-bottom-right-radius:0px\">\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n<\/div><\/div>\n\n\n\n<p class=\"has-text-align-right wp-block-paragraph\">2019-10-17 (C) Questetra, Inc. (MIT License)<br>\n<a href=\"https:\/\/support.questetra.com\/en\/addons\/google-sheets-values-sum-numbers\/\">https:\/\/support.questetra.com\/en\/addons\/google-sheets-values-sum-numbers\/<\/a><\/p>\n\n\n<div class=\"su-spoiler su-spoiler-style-modern-light su-spoiler-icon-plus-square-1\" data-anchor=\"configs\" data-scroll-offset=\"0\" data-anchor-in-url=\"no\"><div class=\"su-spoiler-title\" tabindex=\"0\" role=\"button\"><span class=\"su-spoiler-icon\"><\/span>Configs<\/div><div class=\"su-spoiler-content su-u-clearfix su-u-trim\">\n\n\n\n<ul class=\"fa-ul\">\n<li>A: Select OAuth2 Config Name (at [OAuth 2.0 Setting])<span style=\"color:#990000;\"> *<\/span><\/li>\n<li> B: Set Document-ID of Spreadsheet File (44 chars in File URI)<span style=\"color:#990000;\"> *<\/span><span style=\"color:#000099;\"> <sup style=\"font-style:italic;\">#{EL}<\/sup><\/span><\/li>\n<li> C1: Set ValueRange (e.g. &#8220;Sheet1!D2:D&#8221; cf.&#8217;A1 Notation&#8217;)<span style=\"color:#990000;\"> *<\/span><span style=\"color:#000099;\"> <sup style=\"font-style:italic;\">#{EL}<\/sup><\/span><\/li>\n<li> D1: Select NUMERIC DATA for Total (update)<span style=\"color:#990000;\"> *<\/span><\/li>\n<li> C2: Set ValueRange (e.g. &#8220;Sheet2!E:E100&#8221;)<span style=\"color:#000099;\"> <sup style=\"font-style:italic;\">#{EL}<\/sup><\/span><\/li>\n<li> D2: Select NUMERIC DATA for Total (update)<\/li>\n<\/ul>\n\n\n<\/div><\/div>\n\n\n<div class=\"su-spoiler su-spoiler-style-modern-light su-spoiler-icon-plus-square-1 su-spoiler-closed\" data-anchor=\"script\" data-scroll-offset=\"0\" data-anchor-in-url=\"no\"><div class=\"su-spoiler-title\" tabindex=\"0\" role=\"button\"><span class=\"su-spoiler-icon\"><\/span>Script (click to open)<\/div><div class=\"su-spoiler-content su-u-clearfix su-u-trim\">\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-js\" data-lang=\"JavaScript\"><code>\n\/\/ (c) 2019, Questetra, Inc. (the MIT License)\n\n\/\/\/\/ == OAuth2 Setting example ==\n\/\/ Authorization Endpoint URL:\n\/\/  &quot;https:\/\/accounts.google.com\/o\/oauth2\/auth?access_type=offline&approval_prompt=force&quot;\n\/\/ Token Endpoint URL:\n\/\/  &quot;https:\/\/accounts.google.com\/o\/oauth2\/token&quot;\n\/\/ Scope:\n\/\/  &quot;https:\/\/www.googleapis.com\/auth\/spreadsheets.readonly&quot;\n\/\/ Client ID:\n\/\/  ( from https:\/\/console.developers.google.com\/ )\n\/\/ Consumer Secret:\n\/\/  ( from https:\/\/console.developers.google.com\/ )\n\/\/  *Redirect URL of Webapp OAuth-Client-ID: &quot;https:\/\/s.questetra.net\/oauth2callback&quot;\n\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\n\/\/\/\/\/\/\/\/ START &quot;main()&quot; \/\/\/\/\/\/\/\/\nmain();\nfunction main(){ \n\n\/\/\/\/ == Config Retrieving \/ \u5de5\u7a0b\u30b3\u30f3\u30d5\u30a3\u30b0\u306e\u53c2\u7167 ==\nconst oauth2      = configs.get( &quot;conf_OAuth2&quot;  ) + &quot;&quot;;     \/\/ required\nconst documentId  = configs.get( &quot;conf_DocumentId&quot; ) + &quot;&quot;;  \/\/ required\nconst valueRange1 = configs.get( &quot;conf_ValueRange1&quot; ) + &quot;&quot;; \/\/ required\nconst dataIdD1    = configs.get( &quot;conf_DataIdD1&quot; ) + &quot;&quot;;    \/\/ required\nconst valueRange2 = configs.get( &quot;conf_ValueRange2&quot; ) + &quot;&quot;; \/\/ not required\nconst dataIdD2    = configs.get( &quot;conf_DataIdD2&quot; ) + &quot;&quot;;    \/\/ not required\n\/\/ &#39;java.lang.String&#39; (String Obj) to javascript primitive &#39;string&#39;\n\nengine.log( &quot; AutomatedTask Config: Document ID: &quot; + documentId );\nengine.log( &quot; AutomatedTask Config: ValueRange1: &quot; + valueRange1 );\nengine.log( &quot; AutomatedTask Config: ValueRange2: &quot; + valueRange2 );\n\n\n\/\/\/\/ == Data Retrieving \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u306e\u53c2\u7167 ==\n\/\/ (nothing)\n\n\n\/\/\/\/ == Calculating \/ \u6f14\u7b97 ==\n\/\/\/ obtain OAuth2 Access Token\nconst token   = httpClient.getOAuth2Token( oauth2 );\n\n\/\/\/ get one or two ranges of values via Google Sheets API v4\n\/\/ https:\/\/developers.google.com\/sheets\/api\/reference\/rest\n\/\/  \/v4\/spreadsheets.values\/batchGet\nlet apiRequest = httpClient.begin(); \/\/ HttpRequestWrapper\n    apiRequest = apiRequest.bearer( token );\n    apiRequest = apiRequest.queryParam( &quot;ranges&quot;, valueRange1 ); \n    if( valueRange2 !== &quot;&quot;){\n      apiRequest = apiRequest.queryParam( &quot;ranges&quot;, valueRange2 ); \n    }\n    apiRequest = apiRequest.queryParam( &quot;majorDimension&quot;, &quot;COLUMNS&quot; ); \n    apiRequest = apiRequest.queryParam( &quot;valueRenderOption&quot;, &quot;UNFORMATTED_VALUE&quot; ); \n                 \/\/ Even if formatted as currency, return &quot;1.23&quot; not &quot;$1.23&quot;.\n    apiRequest = apiRequest.queryParam( &quot;dateTimeRenderOption&quot;, &quot;FORMATTED_STRING&quot; ); \n                 \/\/ Date as strings (the spreadsheet locale) not SERIAL_NUMBER\nconst apiUri = &quot;https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/&quot; +\n                documentId + &quot;\/values:batchGet&quot;;\nengine.log( &quot; AutomatedTask Trying: GET &quot; + apiUri );\nconst response = apiRequest.get( apiUri );\nconst responseCode = response.getStatusCode() + &quot;&quot;;\nengine.log( &quot; AutomatedTask ApiResponse: Status &quot; + responseCode );\nif( responseCode !== &quot;200&quot;){\n  throw new Error( &quot;\\n AutomatedTask UnexpectedResponseError: &quot; +\n         responseCode + &quot;\\n&quot; + response.getResponseAsString() + &quot;\\n&quot; );\n}\nconst responseStr = response.getResponseAsString() + &quot;&quot;;\nconst responseObj = JSON.parse( responseStr );\nengine.log( responseStr );\n\nlet sum1 = 0;\n  for( let j = 0; j &lt; responseObj.valueRanges[0].values.length; j++ ){\n    for( let k = 0; k &lt; responseObj.valueRanges[0].values[j].length; k++ ){\n      if( !isNaN( parseFloat( responseObj.valueRanges[0].values[j][k] ) ) ){\n        sum1 += parseFloat( responseObj.valueRanges[0].values[j][k] );\n      }\n    }\n  }\nengine.log( &quot; AutomatedTask Sum of ValueRange1: &quot; + sum1 );\n\nlet sum2 = 0;\nif( valueRange2 !== &quot;&quot;){\n  for( let j = 0; j &lt; responseObj.valueRanges[1].values.length; j++ ){\n    for( let k = 0; k &lt; responseObj.valueRanges[1].values[j].length; k++ ){\n      if( !isNaN( parseFloat( responseObj.valueRanges[1].values[j][k] ) ) ){\n        sum2 += parseFloat( responseObj.valueRanges[1].values[j][k] );\n      }\n    }\n  }\n  engine.log( &quot; AutomatedTask Sum of ValueRange2: &quot; + sum2 );\n}\n\n\n\/\/\/\/ == Data Updating \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 ==\nengine.setDataByNumber( dataIdD1, new java.math.BigDecimal( sum1 ) );\nif ( dataIdD2 !== &quot;&quot; ){ \n  engine.setDataByNumber( dataIdD2, new java.math.BigDecimal( sum2 ) );\n}\n\n\n} \/\/\/\/\/\/\/\/ END &quot;main()&quot; \/\/\/\/\/\/\/\/\n\n<\/code><\/pre><\/div>\n\n\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"alignright\"><img decoding=\"async\" src=\"data:image;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAC3ElEQVRYR8WXbUgTcRzHv\/85bZvF\nZmMwLKwXgkEUGFFKvSi6EoLe+M7IF3lGWtATQW9615uMKOlFaroFKc1XRvSiF14YPUBEtfKFpPlC\nI1TQ2q10Tr3dP3bzxm7e7e7m6u717+Fzv+c\/gcUfMeq\/vOuEa9EWq6dF5DAoqkGxHQRlkj5FBAQT\nIAiTBB1yiq6BqbPPYkZs6wL4Hh7yr4j2awRooYDDoNE4BTqLbULb7OmXM7l0cgKUBY62UNDbAEqN\nOFaRWSAgVyPsYKeWviaAO8jcJxSteTpWqFGCjmgTd07NliqAO8A8JkBDIZzLNigQirLcyWybawAK\n+efZztQioQBYzXlHtuK9g1fQUHkMdluRalCWRQH944O4+OaObtAISGtmTaQBVqt9XK3g3tX3oMqz\nTdP45J8ZiBDxevqLEYiFYptQKXdHGsATYO4CuKTmRQ9glJ\/E9fcPcKv2vFGIdp7lLid9SQDSkLEv\n\/tTqcyMANQPNYLbuMwRBgLhTcHqTw0oCKOs+coraSK9WjPUAYkIcr6Y+I7o8j43FTtT6d6F75Clu\nhh9ppo2ItDFy5kWfBOAJMgFQNOULoKbXO\/YcF3IVJUGQb+LYFECA+QSg+r8CAGGe5fakAHqYX+nF\nokKhl4K8IkAR4Zu5zXIEaK4GzgTgl+YxNPURy4kVhYrf5cUB\/+70rNBNAQCe5YhpgOnYHEb579jr\n24EPs1+xpdQHr8ONt9PDqKuoQYnNLoGZAzCRgiRA6Nug5Hj89w94N7ixqcSFuLCExqrjxgGyUmC4\nCAsGoChCE21YsBRktqGZQVSoIlQMovWOYrNtuGYUrw6jvJeRWQAAymWUNJBrHYeYG6ir2A+S2l26\nXzyxhLZwH9qH+9Vk1dextJRSR+iag6S81IfWnfXwOTy6zpMC4bkxdI08UZXVPEhkaUtPsjSElUfp\nv4iE6bNchrD0YSJDWPo0yyxjyx6nhvpuHUJ\/AerAujDWZMynAAAAAElFTkSuQmCC\" alt=\"\"\/><\/figure><\/div>\n\n\n<div class=\"su-divider su-divider-style-dashed\" style=\"margin:30px 0;border-width:8px;border-color:#009900\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\"><i class=\"fal fa-cloud-download-alt\"><\/i> Download<\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/drive.google.com\/a\/questetra.com\/file\/d\/1ZgUcJjP0OghVEfLFeshYJCAIHfi9SxOY\/view?usp=sharing\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"Google-Sheets-Values-Sum-Numbers.xml (opens in a new tab)\">Google-Sheets-Values-Sum-Numbers.xml<\/a><\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><i class=\"fal fa-images\"><\/i> Capture<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img data-recalc-dims=\"1\" decoding=\"async\" data-attachment-id=\"84005\" data-permalink=\"https:\/\/support.questetra.com\/en\/bpmn-icons\/onedrive-file-upload\/attachment\/setting-service-task-pdf-generation-en\/\" data-orig-file=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/01\/setting-service-task-pdf-generation-en.png?fit=959%2C833&amp;ssl=1\" data-orig-size=\"959,833\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"setting-service-task-pdf-generation-en\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/01\/setting-service-task-pdf-generation-en.png?fit=725%2C630&amp;ssl=1\" src=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/gsheets-sum-numbers.png?ssl=1\" alt=\"\" class=\"wp-image-84005\" style=\"border:10px solid #aaaaaa; padding:5px; margin:5px;\"><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"960\" height=\"540\" data-attachment-id=\"93268\" data-permalink=\"https:\/\/support.questetra.com\/en\/addons\/google-sheets-values-sum-numbers\/attachment\/google-sheets-values-sum-numbers-capture-en-2\/\" data-orig-file=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/08\/Google-Sheets-Values-Sum-Numbers-capture-en.png?fit=960%2C540&amp;ssl=1\" data-orig-size=\"960,540\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"Google-Sheets-Values-Sum-Numbers-capture-en\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/08\/Google-Sheets-Values-Sum-Numbers-capture-en.png?fit=960%2C540&amp;ssl=1\" src=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/08\/Google-Sheets-Values-Sum-Numbers-capture-en.png?resize=960%2C540&#038;ssl=1\" alt=\"\" class=\"wp-image-93268\" srcset=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/08\/Google-Sheets-Values-Sum-Numbers-capture-en.png?w=960&amp;ssl=1 960w, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/08\/Google-Sheets-Values-Sum-Numbers-capture-en.png?resize=560%2C315&amp;ssl=1 560w, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/08\/Google-Sheets-Values-Sum-Numbers-capture-en.png?resize=768%2C432&amp;ssl=1 768w\" sizes=\"auto, (max-width: 960px) 100vw, 960px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><i class=\"fal fa-lightbulb-exclamation\"><\/i> Notes<\/h3>\n\n\n\n<ol class=\"wp-block-list\"><li>Document ID is contained in the URL. <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/\" rel=\"nofollow\">https:\/\/docs.google.com\/spreadsheets\/d\/<\/a><strong>SPREADSHEETID<\/strong>\/edit#gid=0 <\/li><li>All numeric decorations on the spreadsheet (such as decimal separators, rounding and suffixes) are ignored.<\/li><li>The total value stored is truncated according to the Data Item definition. (e.g: 0.1 to 0, -0.1 to 0)<\/li><li>Numeric judgment such as character strings depends on the specification of ECMAScript (JavaScript) <a rel=\"noreferrer noopener\" aria-label=\"parseFloat() (opens in a new tab)\" href=\"https:\/\/developer.mozilla.org\/en-US\/docs\/Web\/JavaScript\/Reference\/Global_Objects\/parseFloat\" target=\"_blank\">parseFloat()<\/a>.<ol><li>The following examples all regarded as 3.14:<\/li><li>3.14<\/li><li>314e-2<\/li><li>0.0314E+2<\/li><li>3.14more non-digit characters<\/li><\/ol><\/li><li>How to specify the range depends on Google API <a rel=\"noreferrer noopener\" aria-label=\"A1 notation (opens in a new tab)\" href=\"https:\/\/developers.google.com\/sheets\/api\/guides\/concepts#a1_notation\" target=\"_blank\">A1 notation<\/a>.<ol><li>&#8220;Sheet1!A1:B2&#8221; refers to the first two cells in the top two rows of Sheet1.<\/li><li>&#8220;Sheet1!A:A&#8221; refers to all the cells in the first column of Sheet1.<\/li><li>&#8220;Sheet1!1:2&#8221; refers to the all the cells in the first two rows of Sheet1.<\/li><li>&#8220;Sheet1!A5:A&#8221; refers to all the cells of the first column of Sheet 1, from row 5 onward.<\/li><li>&#8220;A1:B2&#8221; refers to the first two cells in the top two rows of the first visible sheet.<\/li><li>&#8220;Sheet1&#8221; refers to all the cells in Sheet1.<\/li><\/ol><\/li><\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><i class=\"fal fa-balance-scale\"><\/i> See also<\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/support.questetra.com\/en\/addons\/googlesheets-appendcells\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"Google Sheets: Append New Row(opens in a new tab)\">Google Sheets: Append New Row<\/a><\/li><li><a href=\"https:\/\/support.questetra.com\/en\/bpmn-icons\/intermediate-error-catch-event-boundary-type\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"Intermediate Error Catch Event (Boundary Type)(opens in a new tab)\">Intermediate Error Catch Event (Boundary Type)<\/a><\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"<p>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.<\/p>\n","protected":false},"author":2,"featured_media":81015,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","_uag_custom_page_level_css":"","advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_seo_schema_type":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_wpcom_ai_launchpad_first_post":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"{title}\n\n{excerpt}\n\n{url}","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"_wpas_customize_per_network":false,"jetpack_post_was_ever_published":false},"categories":[168],"tags":[],"class_list":["post-81014","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-addons"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?fit=1200%2C675&ssl=1","uagb_featured_image_src":{"full":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?fit=1200%2C675&ssl=1",1200,675,false],"thumbnail":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=440%2C440&ssl=1",440,440,true],"medium":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?fit=560%2C315&ssl=1",560,315,true],"medium_large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?fit=768%2C432&ssl=1",768,432,true],"large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?fit=1024%2C576&ssl=1",1024,576,true],"1536x1536":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?fit=1200%2C675&ssl=1",1200,675,true],"2048x2048":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?fit=1200%2C675&ssl=1",1200,675,true],"newspack-article-block-landscape-large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=1200%2C675&ssl=1",1200,675,true],"newspack-article-block-portrait-large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=900%2C675&ssl=1",900,675,true],"newspack-article-block-square-large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=1200%2C675&ssl=1",1200,675,true],"newspack-article-block-landscape-medium":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=800%2C600&ssl=1",800,600,true],"newspack-article-block-portrait-medium":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=600%2C675&ssl=1",600,675,true],"newspack-article-block-square-medium":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=800%2C675&ssl=1",800,675,true],"newspack-article-block-landscape-intermediate":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=600%2C450&ssl=1",600,450,true],"newspack-article-block-portrait-intermediate":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=450%2C600&ssl=1",450,600,true],"newspack-article-block-square-intermediate":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=600%2C600&ssl=1",600,600,true],"newspack-article-block-landscape-small":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=400%2C300&ssl=1",400,300,true],"newspack-article-block-portrait-small":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=300%2C400&ssl=1",300,400,true],"newspack-article-block-square-small":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=400%2C400&ssl=1",400,400,true],"newspack-article-block-landscape-tiny":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=200%2C150&ssl=1",200,150,true],"newspack-article-block-portrait-tiny":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=150%2C200&ssl=1",150,200,true],"newspack-article-block-square-tiny":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?resize=200%2C200&ssl=1",200,200,true],"newspack-article-block-uncropped":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Sum-Numbers-en.png?fit=1200%2C675&ssl=1",1200,675,true]},"uagb_author_info":{"display_name":"IMAMURA, Genichi","author_link":"https:\/\/support.questetra.com\/en\/author\/imamuragenichi\/"},"uagb_comment_info":1,"uagb_excerpt":"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.","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9DiIh-l4G","jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":67321,"url":"https:\/\/support.questetra.com\/en\/addons\/googlesheets-sumnumberscom\/","url_meta":{"origin":81014,"position":0},"title":"Sum of cells in a Google Sheets (comma removal version)","author":"Hirotaka NISHI","date":"2016-09-13","format":false,"excerpt":"Stores the sum of specified range of a Google Sheet in a Numeric-type Data Item, and stores its communication log in a String-type Data Item. Comma removal version corresponds to the case where the decimal separator \u201c,\u201d is mixed *Note \u201c+3.14\u201d: 3.14, \u201c314e-2\u201d: 3.14, \u201c090\u201d: 90, \u201c2016-12-23\u201d: 2016, \u2018Begin with\u2026","rel":"","context":"In &quot;Add-ons&quot;","block_context":{"text":"Add-ons","link":"https:\/\/support.questetra.com\/en\/category\/addons\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2016\/09\/google-sheets-sum-numbers-comma-header.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2016\/09\/google-sheets-sum-numbers-comma-header.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2016\/09\/google-sheets-sum-numbers-comma-header.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2016\/09\/google-sheets-sum-numbers-comma-header.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2016\/09\/google-sheets-sum-numbers-comma-header.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":67330,"url":"https:\/\/support.questetra.com\/en\/addons\/googlesheets-sumnumbers\/","url_meta":{"origin":81014,"position":1},"title":"Sum of cells in a Google Sheet","author":"Hirotaka NISHI","date":"2016-09-13","format":false,"excerpt":"Stores the sum of specified range of a Google Sheet in a Numeric-type Data Item, and stores its communication log in a String-type Data Item. *Note \u201c+3.14\u201d: 3.14, \u201c314e-2\u201d: 3.14, \u201c090\u201d: 90, \u201c2016-12-23\u201d: 2016, \u2018Begin with letter\u2019: 0 (parseFloat() sum) ** https:\/\/docs.google.com\/spreadsheets\/d\/1exampleEXAMPLEexampleEXAMPLEexampleEXAMPLE0\/edit#gid=0 Spreadsheet ID: 1exampleEXAMPLEexampleEXAMPLEexampleEXAMPLE0 *** Specify the sum of\u2026","rel":"","context":"In &quot;Add-ons&quot;","block_context":{"text":"Add-ons","link":"https:\/\/support.questetra.com\/en\/category\/addons\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2016\/09\/google-sheets-sum-numbers-header.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2016\/09\/google-sheets-sum-numbers-header.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2016\/09\/google-sheets-sum-numbers-header.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2016\/09\/google-sheets-sum-numbers-header.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2016\/09\/google-sheets-sum-numbers-header.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":78314,"url":"https:\/\/support.questetra.com\/en\/addons\/tsv-string-sum-of-number-column\/","url_meta":{"origin":81014,"position":2},"title":"TSV String; Sum of Number Column","author":"IMAMURA, Genichi","date":"2019-08-05","format":false,"excerpt":"Calculates the sum of values in numeric column. If non-numeric data is mixed in the specified column, the record is regarded as zero and not added.","rel":"","context":"In &quot;\u30a2\u30c9\u30aa\u30f3&quot;","block_context":{"text":"\u30a2\u30c9\u30aa\u30f3","link":"https:\/\/support.questetra.com\/ja\/category\/addons\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/08\/TSV-String-Sum-of-Number-Column-en.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/08\/TSV-String-Sum-of-Number-Column-en.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/08\/TSV-String-Sum-of-Number-Column-en.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/08\/TSV-String-Sum-of-Number-Column-en.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/08\/TSV-String-Sum-of-Number-Column-en.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":81186,"url":"https:\/\/support.questetra.com\/en\/addons\/google-sheets-values-export-as-tsv\/","url_meta":{"origin":81014,"position":3},"title":"Google Sheets: Values, Export as TSV","author":"IMAMURA, Genichi","date":"2021-02-01","format":false,"excerpt":"Exports the values in the rectangular range as TSV text, which has the same number of tab delimiters on each line. Empty cells are regarded as the null string. Two range export are also supported: e.g. Freezed headings and recent data.","rel":"","context":"In &quot;Add-ons&quot;","block_context":{"text":"Add-ons","link":"https:\/\/support.questetra.com\/en\/category\/addons\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Export-as-TSV-en.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Export-as-TSV-en.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Export-as-TSV-en.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Export-as-TSV-en.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Export-as-TSV-en.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":79741,"url":"https:\/\/support.questetra.com\/en\/data-items\/numeric-type\/","url_meta":{"origin":81014,"position":4},"title":"Numeric-Type","author":"Peter Glover","date":"2019-09-30","format":false,"excerpt":"Displays a text field that accepts only numeric values.","rel":"","context":"In &quot;Data Items&quot;","block_context":{"text":"Data Items","link":"https:\/\/support.questetra.com\/en\/category\/data-items\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/09\/Numeric.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/09\/Numeric.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/09\/Numeric.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/09\/Numeric.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/09\/Numeric.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":79781,"url":"https:\/\/support.questetra.com\/en\/data-items\/table-type\/","url_meta":{"origin":81014,"position":5},"title":"Table-Type","author":"Peter Glover","date":"2021-01-22","format":false,"excerpt":"Displays a table that can use the String, Numeric, Select or Date formats.","rel":"","context":"In &quot;Data Items&quot;","block_context":{"text":"Data Items","link":"https:\/\/support.questetra.com\/en\/category\/data-items\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/09\/Table.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/09\/Table.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/09\/Table.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/09\/Table.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/09\/Table.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]}],"amp_enabled":false,"_links":{"self":[{"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/posts\/81014","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/comments?post=81014"}],"version-history":[{"count":11,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/posts\/81014\/revisions"}],"predecessor-version":[{"id":122302,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/posts\/81014\/revisions\/122302"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/media\/81015"}],"wp:attachment":[{"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/media?parent=81014"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/categories?post=81014"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/tags?post=81014"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}