Google Sheets: ValueRanges, Export as TSV<\/div>
Stores a specified rectangular range of data as a TSV String. Empty cells are regarded as an empty string. Exporting two ranges at the same time is also supported. For example, the heading line and the 10th and subsequent lines can be exported 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!1:1”)#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> C1: Select STRING that stores TSV (update)<\/li>\n
- <\/i><\/span> B2: Set ValueRange (e.g. “Sheet2!10:15”)#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> C2: Select STRING that stores TSV (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 strPocketTsv1 = configs.getObject( "SelectConfC1" ); \/\/ NotRequired\nconst strPocketTsv2 = 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", "ROWS" );\n request1 = request1.queryParam( "valueRenderOption", "FORMATTED_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\/* \nengine.log( response1Body ); \/\/ debug\n{\n "spreadsheetId": "1dSQcZSh2kF_KnuzgFydaOx7T24oURRJwpkCvB_qCV4w",\n "valueRanges": [\n {\n "range": "Sheet1!A1:Z3",\n "majorDimension": "ROWS",\n "values": [\n [\n "Date",\n "Account (\u79d1\u76ee)",\n "Description (\u6458\u8981)",\n "Debit (\u501f\u65b9)",\n "Credit (\u8cb8\u65b9)",\n "Balance (\u6b8b\u9ad8)"\n ],\n [\n "2019-10-01",\n "\\u003cBUDGET\\u003e",\n "",\n "",\n "4,000,000.00",\n "4,000,000.00"\n ],\n [\n "2019-10-17",\n "Design Fee",\n "Web Display: BPM ",\n "2,000,000.00",\n "",\n "2,000,000.00"\n ]\n ]\n },\n {\n "range": "Sheet1!A6:Z8",\n "majorDimension": "ROWS",\n "values": [\n [\n "2019-11-10",\n "\\u003cBUDGET2\\u003e",\n "",\n "",\n "500,000.00",\n "1,000,000.00"\n ],\n [\n "2019-11-10",\n "Print",\n "Poster",\n "100,000.00",\n "",\n "900,000.00"\n ],\n [\n "2019-11-25",\n "Print",\n "Pamphlet",\n "400,000.00",\n "",\n "500,000.00"\n ]\n ]\n }\n ]\n}\n*\/\nconst response1Obj = JSON.parse( response1Body );\nengine.log( " AutomatedTask ApiResponse: #of valueRanges: " + response1Obj.valueRanges.length );\nlet arrStrTsvs = [];\nfor( let i = 0; i < response1Obj.valueRanges.length; i++ ){\n engine.log( " AutomatedTask ApiResponse: range: " + response1Obj.valueRanges[i].range );\n let numTsvWidth = 0;\n for( let j = 0; j < response1Obj.valueRanges[i].values.length; j++ ){\n if( numTsvWidth < response1Obj.valueRanges[i].values[j].length ){\n numTsvWidth = response1Obj.valueRanges[i].values[j].length;\n }\n }\n let strTmp = "";\n engine.log( " AutomatedTask ApiResponse: tmpTsv" + (i + 1) + " width: " + numTsvWidth );\n for( let j = 0; j < response1Obj.valueRanges[i].values.length; j++ ){\n for( let k = 0; k < numTsvWidth; k++ ){\n if( k < response1Obj.valueRanges[i].values[j].length ){\n strTmp += response1Obj.valueRanges[i].values[j][k];\n }\n if( k != response1Obj.valueRanges[i].values[j].length - 1 ){\n strTmp += "\\t";\n }\n }\n if( j != response1Obj.valueRanges[i].values.length - 1 ){\n strTmp += "\\n";\n }\n }\n arrStrTsvs.push( strTmp );\n}\n\/\/ each TSV, which range\nlet strTsv1 = "";\nlet strTsv2 = "";\nif( strValueRange1 !== "" && strValueRange2 !== ""){\n strTsv1 = arrStrTsvs[0];\n strTsv2 = arrStrTsvs[1];\n}else if( strValueRange1 !== "" && strValueRange2 === ""){\n strTsv1 = arrStrTsvs[0];\n}else if( strValueRange1 === "" && strValueRange2 !== ""){\n strTsv2 = arrStrTsvs[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( strPocketTsv1 !== null ){\n engine.setData( strPocketTsv1, strTsv1 );\n}\nif( strPocketTsv2 !== null ){\n engine.setData( strPocketTsv2, strTsv2 );\n}\n\n\n} \/\/\/\/\/\/\/\/ END "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\n\n\/*\nNotes:\n- The rectangular area in the spreadsheet can be automatically included as Workflow data.\n- The File ID can be obtained from the URI or the sharing settings screen.\n - 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- The exported TSV string has a uniform number of columns (number of cells) in each row.\n- There is no line feed code at the end of the exported TSV string.\n - When combining multiple TSVs, it is necessary to insert a line feed code.\nNotes-ja:\n- \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u5185\u306e\u77e9\u5f62\u7bc4\u56f2\u304cWorkflow\u30c7\u30fc\u30bf\u3068\u3057\u3066\u81ea\u52d5\u7684\u306b\u53d6\u308a\u8fbc\u307e\u308c\u308b\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002\n- \u30c9\u30ad\u30e5\u30e1\u30f3\u30c8 ID \u306f URL \u3092\u53c2\u7167\u3059\u308b\u306a\u3069\u3057\u3066\u8a2d\u5b9a\u3057\u307e\u3059\u3002\n - 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- \u51fa\u529b\u3055\u308c\u308bTSV\u6587\u5b57\u5217\u306f\u3001\u3044\u305a\u308c\u306e\u884c\u3082\u5747\u4e00\u306e\u5217\u6570\uff08\u30bb\u30eb\u6570\uff09\u3068\u306a\u308a\u307e\u3059\u3002\n- \u51fa\u529b\u3055\u308c\u308bTSV\u6587\u5b57\u5217\u306e\u672b\u5c3e\u306b\u6539\u884c\u30b3\u30fc\u30c9\u306f\u3042\u308a\u307e\u305b\u3093\u3002\n - \u8907\u6570\u306eTSV\u3092\u7d50\u5408\u3059\u308b\u5834\u5408\u306b\u306f\u3001\u6539\u884c\u30b3\u30fc\u30c9\u3092\u9014\u4e2d\u633f\u5165\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002\n\nAPPENDIX-en\n- A1 Notation\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 Notation\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>\n\n\n\n
<\/i> Download<\/h3>\n\n\n\n
- <\/ul>\n\n\n\n
- Google-Sheets-ValueRanges-Export-as-TSV-2021.xml<\/a>\n
- \n
- 2021-04-23 (C) Questetra, Inc. (MIT License)<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Google-Sheets-ValueRanges-Export-as-TSV-202<\/a>3<\/a>.xml<\/a>\n
- \n
- 2023-08-21 (C) Questetra, Inc. (MIT License)<\/li>\n\n\n\n
- for “GraalJS standard (engine-type 3)” on v15.0 or above<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Google-Sheets-ValueRanges-Export-as-TSV-2026.xml<\/a>\n
- \n
- 2026-04-27 (C) Questetra, Inc. (MIT License)<\/li>\n\n\n\n
- Due to the deprecation of the methods
getOAuth2Token(String)<\/code> at v18.<\/li>\n\n\n\n- https:\/\/support.questetra.com\/ja\/versions\/version-180\/<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
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
- \n
- The rectangular area in the spreadsheet can be automatically included as Workflow data.<\/li>\n\n\n\n
- The File ID can be obtained from the URI or the sharing settings screen.\n
- \n
- docs.google.com\/spreadsheets\/d\/{File-ID}\/edit#gid=0<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- How to specify the range depends on Google API A1 notation. (see APPENDIX)<\/li>\n\n\n\n
- The exported TSV string has a uniform number of columns in each row.<\/li>\n\n\n\n
- There is no line feed code at the end of the exported TSV string.\n
- \n
- When combining multiple TSVs it is necessary to insert a line feed code.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
<\/i> Capture<\/h3>\n\n\n\n
![\"\"](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/10\/Google-Sheets-Values-Export-as-TSV-capture-en.png?resize=1200%2C675&ssl=1\")
<\/figure>\n\n\n\n![\"Stores](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/04\/Google-Sheets-ValueRanges-Export-as-TSV-2021-execution-en.png?resize=1200%2C675&ssl=1\")
<\/a><\/figure>\n\n\n\n![\"Stores](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/04\/Google-Sheets-ValueRanges-Export-as-TSV-2021-config-en-847x630.png?resize=847%2C630&ssl=1\")
<\/figure>\n\n\n\n<\/i> Appendix<\/h3>\n\n\n\n
- \n
- A1 Notation\n
- \n
- https:\/\/developers.google.com\/sheets\/api\/guides\/concepts#a1_notation<\/a><\/li>\n\n\n\n
- “Sheet1!A1:B2” refers to the first two cells in the top two rows of Sheet1.<\/li>\n\n\n\n
- “Sheet1!A:A” refers to all the cells in the first column of Sheet1.<\/li>\n\n\n\n
- “Sheet1!1:2” refers to all the cells in the first two rows of Sheet1.<\/li>\n\n\n\n
- “Sheet1!A5:A” refers to all the cells of the first column of Sheet 1, from row 5 onward.<\/li>\n\n\n\n
- “A1:B2” refers to the first two cells in the top two rows of the first visible sheet.<\/li>\n\n\n\n
- “Sheet1” refers to all the cells in Sheet1.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- HTTP Authentication (OAuth2) Settings example\n
- \n
- Authorization Endpoint URL:\n
- \n
- https:\/\/accounts.google.com\/o\/oauth2\/auth?access_type=offline&approval_prompt=force<\/a><\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Token Endpoint URL:\n
- https:\/\/accounts.google.com\/o\/oauth2\/auth?access_type=offline&approval_prompt=force<\/a><\/li>\n<\/ul>\n<\/li>\n\n\n\n
- https:\/\/developers.google.com\/sheets\/api\/guides\/concepts#a1_notation<\/a><\/li>\n\n\n\n
- A1 Notation\n
- When combining multiple TSVs it is necessary to insert a line feed code.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
- https:\/\/support.questetra.com\/ja\/versions\/version-180\/<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
- \n
- <\/i><\/span> A1: Set FILE-ID in Drive *<\/span>#{EL}<\/sup><\/span><\/li>\n