{"id":177242,"date":"2025-09-12T09:28:16","date_gmt":"2025-09-12T00:28:16","guid":{"rendered":"https:\/\/support.questetra.com\/?p=177242"},"modified":"2025-09-12T12:27:42","modified_gmt":"2025-09-12T03:27:42","slug":"google-sheets-sheet-overwrite-with-tsv-2025","status":"publish","type":"post","link":"https:\/\/support.questetra.com\/ja\/addons\/google-sheets-sheet-overwrite-with-tsv-2025\/","title":{"rendered":"Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8 #Sheet: TSV\u3067\u4e0a\u66f8\u304d"},"content":{"rendered":"\n

Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8 #Sheet: TSV\u3067\u4e0a\u66f8\u304d<\/strong><\/h4>\n\n\n\n

translate<\/span> Google Sheets #Sheet: Overwrite with TSV<\/a><\/p>\n\n\n\n

\u6307\u5b9a\u30b7\u30fc\u30c8\u306e\u6307\u5b9a\u5ea7\u6a19\u306bTSV\u30c7\u30fc\u30bf\u3092\u8cbc\u308a\u4ed8\u3051\u307e\u3059\u3002\u9078\u629e\u3057\u305f TSV \u5217\u304c\u8cbc\u308a\u4ed8\u3051\u3089\u308c\u307e\u3059\u3002\u306a\u304a\u3001\u65e5\u4ed8\u6642\u523b\u30c7\u30fc\u30bf\u306e\u5834\u5408\u306f\u300cd\u300d\u3092\u4ed8\u3057\u3066\u3001\u6570\u5024\u30c7\u30fc\u30bf\u306e\u5834\u5408\u306f\u300cn\u300d\u3092\u4ed8\u3057\u3066\u5217\u6307\u5b9a\u3057\u307e\u3059\u3002\u5217\u9078\u629e\u306e\u4f8b: “0,2d,5n”\u3002<\/p>\n\n\n\n

\"Auto<\/figure>\n\n\n\n
\n
Configs for this Auto Step<\/summary>
\n
\n
AuthzConfU<\/dt>
U: HTTP\u8a8d\u8a3c\u8a2d\u5b9a\u3092\u9078\u629e\u3057\u3066\u304f\u3060\u3055\u3044 *<\/span><\/dd>\n
StrConfA1<\/dt>
A1: TSV \u3092\u30bb\u30c3\u30c8\u3057\u3066\u304f\u3060\u3055\u3044 *<\/span>#{EL}<\/sup><\/span><\/dd>\n
StrConfA2<\/dt>
A2: \u9001\u51fa\u3055\u308c\u308b\u5217ID\u3092\u30bb\u30c3\u30c8\u3057\u3066\u304f\u3060\u3055\u3044 (\u4f8b: “0,1,5d,3n,6”) *<\/span>#{EL}<\/sup><\/span><\/dd>\n
StrConfB1<\/dt>
B1: \u30c7\u30fc\u30bf\u633f\u5165\u5148\u306e\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8ID\uff08Spreadsheet ID\uff09\u3092\u30bb\u30c3\u30c8\u3057\u3066\u304f\u3060\u3055\u3044 *<\/span>#{EL}<\/sup><\/span><\/dd>\n
StrConfB2<\/dt>
B2: \u30c7\u30fc\u30bf\u633f\u5165\u5148\u306e\u30b7\u30fc\u30c8ID\uff08Sheet ID\uff09\u3092\u30bb\u30c3\u30c8\u3057\u3066\u304f\u3060\u3055\u3044 \uff08\u30c7\u30d5\u30a9\u30eb\u30c8: “0”\uff09#{EL}<\/sup><\/span><\/dd>\n
StrConfB3X<\/dt>
B3X: \u4e0a\u66f8\u304d\u958b\u59cb\u30bb\u30eb\u306eX\u5ea7\u6a19\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3092\u30bb\u30c3\u30c8\u3057\u3066\u304f\u3060\u3055\u3044 \uff08\u30c7\u30d5\u30a9\u30eb\u30c8: “0”\uff09#{EL}<\/sup><\/span><\/dd>\n
StrConfB3Y<\/dt>
B3Y: \u4e0a\u66f8\u304d\u958b\u59cb\u30bb\u30eb\u306eY\u5ea7\u6a19\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3092\u30bb\u30c3\u30c8\u3057\u3066\u304f\u3060\u3055\u3044 \uff08\u30c7\u30d5\u30a9\u30eb\u30c8: “0”\uff09#{EL}<\/sup><\/span><\/dd>\n<\/div>\n
StrConfB2b<\/dt>
B2b: B2 (SheetID) \u306e\u4ee3\u308f\u308a\u306b\u30b7\u30fc\u30c8\u540d\u3067\u30bb\u30c3\u30c8#{EL}<\/sup><\/span><\/dd>\n
SelectConfInY<\/dt>
InY: \u30a4\u30f3\u30d7\u30c3\u30c8TSV\u306e\u884c\u6570(\u9ad8\u3055)\u3092\u683c\u7d0d\u3059\u308b\u6570\u5024\u578b\u30c7\u30fc\u30bf\u3092\u9078\u629e\u3057\u3066\u304f\u3060\u3055\u3044 (\u66f4\u65b0)<\/dd>\n
SelectConfInX<\/dt>
InX: \u30a4\u30f3\u30d7\u30c3\u30c8TSV\u306e\u5217\u6570(\u5e45)\u3092\u683c\u7d0d\u3059\u308b\u6570\u5024\u578b\u30c7\u30fc\u30bf\u3092\u9078\u629e\u3057\u3066\u304f\u3060\u3055\u3044 (\u66f4\u65b0)<\/dd>\n<\/div>\n\n<\/dl>\n<\/div><\/details><\/div>\n<\/div>\n\n\n\n
\n
Script (click to open)<\/summary>
\n
\/\/ Script Example of Business Process Automation\n\/\/ for 'engine type: 3' ("GraalJS standard mode")\n\/\/ cf. 'engine type: 2' ("GraalJS Nashorn compatible mode") (renamed from "GraalJS" at 20230526)\n\n\n\/\/\/\/\/\/\/\/ START "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\nmain();\nfunction main(){ \n\n\/\/\/\/ == Config Retrieving \/ \u5de5\u7a0b\u30b3\u30f3\u30d5\u30a3\u30b0\u306e\u53c2\u7167 ==\nconst qAuthzSetting   = configs.getObject ("AuthzConfU"); \/\/ REQUIRED\n  if (qAuthzSetting === null) {\n    throw new Error("\\n AutomatedTask ConfigError: Config {AuthzConfU} invalid (OAUTH2) \\n");\n  }\n  engine.log(" AutomatedTask Config: Authz Setting: " + qAuthzSetting.getName());\n\nconst strTsv          = configs.get       ( "StrConfA1" );      \/\/ REQUIRED\n  if( strTsv === "" ){\n    throw new Error( "\\n AutomatedTask ConfigError:" +\n                     " Config {A1: TSV} is empty \\n" );\n  }\n  const arr2dTsv      = parseAsRectangular ( strTsv );          \/\/ as arr2dTsv[Y][X]\n  if (arr2dTsv.length === 0) {\n    throw new Error( "\\n AutomatedTask DataError: TSV has no non-blank rows \\n" );\n  }\nconst strColIds       = configs.get       ( "StrConfA2" );      \/\/ REQUIRED\n  if( strColIds === "" ){\n    throw new Error( "\\n AutomatedTask ConfigError:" +\n                     " Config {A2: Column IDs} not specified \\n" );\n  }\n  let arrColIds       = strColIds.split(",");\n  engine.log( " AutomatedTask Config: Column Ids (" +\n                strColIds + ") [" + \n                arrColIds.length + "] will be inserted" );\nconst strDocId        = configs.get       ( "StrConfB1" );      \/\/ REQUIRED\n  if( strDocId === "" ){\n    throw new Error( "\\n AutomatedTask ConfigError:" +\n                     " Config {B1: Spreadsheet ID} not specified \\n" );\n  }\nconst strSheetId      = configs.get       ( "StrConfB2" );      \/\/ not required\n  let numSheetId      = strSheetId !== "" ?\n                        parseInt( strSheetId, 10 ) :\n                        0;\nconst strStartX       = configs.get       ( "StrConfB3X" );     \/\/ not required\n  let numStartX       = strStartX !== "" ?\n                        parseInt( strStartX, 10 ) :\n                        0;\nconst strStartY       = configs.get       ( "StrConfB3Y" );     \/\/ not required\n  let numStartY       = strStartY !== "" ?\n                        parseInt( strStartY, 10 ) :\n                        0;\nconst strSheetName    = configs.get       ( "StrConfB2b");      \/\/ not required\n\nconst numPocketInY    = configs.getObject ( "SelectConfInY" );  \/\/ not required\nconst numPocketInX    = configs.getObject ( "SelectConfInX" );  \/\/ not required\n\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\n\n\/\/\/\/\/\/ == Calculating \/ \u6f14\u7b97 ==\n\n\/\/\/\/ \u25bc\u25bc\u25bc get SheetId by SheetName \u25bc\u25bc\u25bc  \u203b if(strSheetName !== "")\n\nif( strSheetName !== "" ){\n  engine.log( " AutomatedTask: Try to get SheetId by SheetName. (connecting ...)" );\n\n  \/\/\/ request0, prepare\n  \/\/ Google Workspace > Google Sheets > spreadsheets > Method: spreadsheets.get\n  \/\/ https:\/\/developers.google.com\/workspace\/sheets\/api\/reference\/rest\/v4\/spreadsheets\/get\n\n  let request0uri = "https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/" + strDocId;\n  let request0    = httpClient.begin(); \/\/ HttpRequestWrapper\n      request0    = request0.authSetting( qAuthzSetting ); \/\/ with "Authorization: Bearer XX"\n      \/\/ https:\/\/questetra.zendesk.com\/hc\/en-us\/articles\/360024574471-R2300#HttpRequestWrapper\n\n  \/\/\/ request0, try\n  const response0 = request0.get( request0uri );             \/\/ HttpResponseWrapper\n  const response0code = response0.getStatusCode() + "";      \/\/ (primitive string)\n  const response0body = response0.getResponseAsString() + "";\n  engine.log( " AutomatedTask ApiResponse Status: " + response0code );\n  if( response0code !== "200"){\n    throw new Error( "\\n AutomatedTask UnexpectedResponseError: " +\n                      response0code + "\\n" + response0body + "\\n" );\n  }\n\n  \/\/\/ response0, parse\n  \/* engine.log( response0body ); \/\/ debug\n  {\n    "spreadsheetId": "17rwXXXXxS34yyyyYr-KzzzzX0_iWWWWOfHdxxxxRCLM",\n    "properties": { \u2026 },\n    "sheets": [\n      {\n        "properties": {\n          "sheetId": 0,\n          "title": "sheet1",\n          "index": 0,\n          "sheetType": "GRID",\n          "gridProperties": {\n            "rowCount": 100,\n            "columnCount": 20,\n            "frozenRowCount": 1\n          }\n        }\n      }\n    ],\n    "spreadsheetUrl": "https:\/\/docs.google.com\/spreadsheets\/d\/17rw......OfHdxxxxRCLM\/edit"\n  }\n  *\/\n  const response0obj = JSON.parse( response0body );\n\n  let boolFound = false;\n  for( let i = 0; i < response0obj.sheets.length; i++ ){\n    engine.log( " - " + response0obj.sheets[i].properties.title );\n    if( response0obj.sheets[i].properties.title === strSheetName ){\n      numSheetId = response0obj.sheets[i].properties.sheetId;\n      engine.log( " AutomatedTask SheetName '" + strSheetName +\n                    "': SheetId " + numSheetId );\n      boolFound = true;\n      break;\n    } \/\/ not found, then default SheetID "0"\n  }\n  if (! boolFound ) {\n    engine.log(" AutomatedTask RuntimeWarning: " + strSheetName + " not found. Using sheetId=0.");\n  }\n\n}\n\n\n\n\/\/\/\/ \u25bc\u25bc\u25bc Update Cells \u25bc\u25bc\u25bc\nconst regBpmsYMD = \/^\\d{4}-\\d{2}-\\d{2}$\/;\n\n\/\/\/\/ request1, prepare body\n\/\/ Google Workspace > Google Sheets > spreadsheets > Method: spreadsheets.batchUpdate ( Requests: updateCells )\n\/\/ https:\/\/developers.google.com\/workspace\/sheets\/api\/reference\/rest\/v4\/spreadsheets\/request#UpdateCellsRequest\n\nlet request1obj = {};\n    request1obj.requests = [];\n    request1obj.requests[0] = {};\n    request1obj.requests[0].updateCells = {};\n    request1obj.requests[0].updateCells.fields = "userEnteredValue,userEnteredFormat.numberFormat";\n\/\/    request1obj.requests[0].updateCells.fields = "*";\n    request1obj.requests[0].updateCells.start = {};\n    request1obj.requests[0].updateCells.start.sheetId = numSheetId;\n    request1obj.requests[0].updateCells.start.rowIndex = numStartY;\n    request1obj.requests[0].updateCells.start.columnIndex = numStartX;\n    request1obj.requests[0].updateCells.rows = [];\n\nfor (let i = 0; i < arr2dTsv.length; i++) {  \/\/ arr2dTsv[Y][X]\n  request1obj.requests[0].updateCells.rows[i] = { values: [] };\n\n  for (let j = 0; j < arrColIds.length; j++) {\n    request1obj.requests[0].updateCells.rows[i].values[j] = {};\n    const v = request1obj.requests[0].updateCells.rows[i].values[j];\n\n    const cid = arrColIds[j].trim();\n    const numTmpColId = parseInt(cid, 10);\n    const raw = (arr2dTsv[i][numTmpColId] ?? "");\n    const rawStr = String(raw);\n\n    if (cid.slice(-1) === "n") { \/\/ Numeric\n      v.userEnteredValue = {};\n      const n = parseFloat(rawStr.replace(\/,\/g, ""));\n      if (Number.isNaN(n)) {\n        v.userEnteredValue.stringValue = "";\n      } else {\n        v.userEnteredValue.numberValue = n;\n      }\n\n    } else if (cid.slice(-1) === "d") { \/\/ Date\n      v.userEnteredValue  = {};\n      v.userEnteredFormat = { numberFormat: {} };\n\n      let strDatetimeTmp = rawStr.replace(\/\\\/\/g, "-");\n      if (regBpmsYMD.test(strDatetimeTmp)) { strDatetimeTmp += "T00:00:00"; }\n      const dateTmp = new Date(strDatetimeTmp);\n\n      if (isNaN(dateTmp.getTime())) {\n        v.userEnteredValue.stringValue = "";\n      } else {\n        let numSerial = dateTmp.getTime() \/ 86400000 + 25569;\n        numSerial += engine.getTimeZoneOffsetInMinutes() \/ (60 * 24); \/\/ \u73fe\u884c\u30ed\u30b8\u30c3\u30af\u8e0f\u8972\n        v.userEnteredFormat.numberFormat.type = "DATE";\n        v.userEnteredValue.numberValue = numSerial;\n      }\n\n    } else { \/\/ String\n      v.userEnteredValue = { stringValue: rawStr };\n    }\n  }\n}\n\n\n\/\/\/\/ request1, prepare header\n\/\/ Google Workspace > Google Sheets > spreadsheets > Method: spreadsheets.batchUpdate\n\/\/ https:\/\/developers.google.com\/workspace\/sheets\/api\/reference\/rest\/v4\/spreadsheets\/batchUpdate\n\nlet request1uri = "https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/" + strDocId + ":batchUpdate";\n\nlet request1    = httpClient.begin(); \/\/ HttpRequestWrapper\n    request1    = request1.authSetting( qAuthzSetting ); \/\/ with "Authorization: Bearer XX"\n    request1    = request1.body( JSON.stringify( request1obj ), "application\/json" );\n\n\n\/\/\/\/ request1, try\nconst response1     = request1.post( request1uri );        \/\/ HttpResponseWrapper\nengine.log( " AutomatedTask ApiRequest1 Start: " + request1uri );\nconst response1code = response1.getStatusCode() + "";      \/\/ (primitive string)\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\n\/\/\/\/ response1, parse\n\/\/ (nothing)\n\/* engine.log( response1body ); \/\/ debug\n{\n  "spreadsheetId": "1rviXXXXXHcRYYYYjFN1ZZZZfM6EWWWWqifaVVVVzgSM",\n  "replies": [\n    {}\n  ]\n}\n\n\n*\/\n\n\n\n\n\/\/\/\/ == Data Updating \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 ==\nif ( numPocketInY !== null ){ \n  engine.setData( numPocketInY, new java.math.BigDecimal( arr2dTsv.length ) );\n}\nif ( numPocketInX !== null ){ \n  engine.setData( numPocketInX, new java.math.BigDecimal( arr2dTsv[0].length ) );\n}\n\n\n} \/\/\/\/\/\/\/\/ END "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\n\n\n\n\/**\n * Parses a TSV (Tab-Separated Values) string and returns a 2D array with rectangular shape.\n * This function ensures that each row in the resulting 2D array has the same number of columns,\n * equal to the widest row in the original TSV input. Shorter rows are padded with empty strings.\n * Blank lines in the input are ignored.\n * Additionally, the function logs the minimum and maximum column counts (excluding blank lines),\n * along with the total number of lines and count of blank lines.\n * \n * TSV\uff08\u30bf\u30d6\u533a\u5207\u308a\u5024\uff09\u5f62\u5f0f\u306e\u6587\u5b57\u5217\u3092\u89e3\u6790\u3057\u30012\u6b21\u5143\u306e\u914d\u5217\u3068\u3057\u3066\u6574\u5f62\u3057\u3066\u8fd4\u3057\u307e\u3059\u3002\n * \u5165\u529b\u3055\u308c\u305fTSV\u6587\u5b57\u5217\u3092\u884c\u5358\u4f4d\u3067\u5206\u5272\u3057\u3001\u5404\u884c\u306e\u30bb\u30eb\u6570\u3092\u305d\u308d\u3048\u3066\u300c\u9577\u65b9\u5f62\uff08\u77e9\u5f62\uff09\u300d\u306e\u5f62\u306b\u6574\u3048\u307e\u3059\u3002\n * \u6700\u3082\u30bb\u30eb\u6570\u304c\u591a\u3044\u884c\u306b\u5408\u308f\u305b\u3066\u3001\u30bb\u30eb\u6570\u304c\u8db3\u308a\u306a\u3044\u884c\u306b\u306f\u7a7a\u6587\u5b57\u5217\uff08""\uff09\u3092\u88dc\u5b8c\u3057\u307e\u3059\u3002\n * \u7a7a\u884c\uff08\u4e2d\u8eab\u304c\u7a7a\u306e\u884c\uff09\u306f\u7121\u8996\u3055\u308c\u307e\u3059\u3002\n * \u307e\u305f\u3001\u6700\u5c0f\u5217\u6570\u30fb\u6700\u5927\u5217\u6570\u30fb\u5168\u4f53\u306e\u884c\u6570\u30fb\u7a7a\u884c\u306e\u6570\u3092\u30ed\u30b0\u306b\u51fa\u529b\u3057\u307e\u3059\uff08engine.log\uff09\u3002\n * \n * \n * @param {string} strTsv - The raw TSV string, with rows separated by newline characters (`\\n`) \n *                          and cells separated by tab characters (`\\t`).\n * \n * @returns {string[][]} A two-dimensional array where each sub-array represents a row from the TSV input,\n *                       padded with empty strings to ensure uniform column width.\n * \n * @example\n * const tsv = "a\\tb\\tc\\n1\\t2\\nx\\ty\\tz\\tw\\n";\n * const result = parseAsRectangular(tsv);\n * \/\/ result:\n * \/\/ [\n * \/\/   ["a", "b", "c", ""],\n * \/\/   ["1", "2", "", ""],\n * \/\/   ["x", "y", "z", "w"]\n * \/\/ ]\n *\/\n\nfunction parseAsRectangular( strTsv ){\n  const arrTsv = strTsv.split("\\n");\n\n  \/\/\/ Get numMinWidth and numMaxWidth (blank lines are excluded)\n  let numMinWidth   = Infinity; \/\/ cf. String-Type Max: 1 million\n  let numMaxWidth   = 0;\n  let numBlanklines = 0;\n  for( let i = 0; i < arrTsv.length; i++ ){\n    if( arrTsv[i] === "" ){ \/\/ Skip blank lines\n      numBlanklines += 1;\n      continue;\n    }\n    let arrCells = arrTsv[i].split("\\t");\n    if( numMinWidth > arrCells.length ){ numMinWidth = arrCells.length; }\n    if( numMaxWidth < arrCells.length ){ numMaxWidth = arrCells.length; }\n  }\n  engine.log( " AutomatedTask TsvDataCheck:" + \n              " MinWidth:" + numMinWidth +\n              " MaxWidth:" + numMaxWidth +\n              " Lines:" + arrTsv.length +\n              " (BlankLines:" + numBlanklines + ")" );\n\n  \/\/\/ Get numMinWidth and numMaxWidth (blank lines are excluded)\n  let arr2dTsv      = [];\n  for( let i = 0; i < arrTsv.length; i++ ){\n    if( arrTsv[i] === "" ){ \/\/ Skip blank lines\n      continue;\n    }\n    let arrTmp = [];\n    let arrCells = arrTsv[i].split("\\t");\n    for( let j = 0; j < numMaxWidth; j++ ){\n      if( j < arrCells.length ){\n        arrTmp[j] = arrCells[j];\n      }else{\n        arrTmp[j] = "";\n      }\n    }\n    arr2dTsv.push( arrTmp );\n  }\n\n  return arr2dTsv;\n}\n\n\n\n\n\/*\n\u25bcNOTES:\n- By placing this [Automated Step] in a workflow diagram, an API request will be generated each time a Case Process reaches it.\n    - "Questetra BPM Suite" server (client side) \u21d2 "Google Spreadsheets" API (server side)\n    - Tab Separated Values text (TSV string) data will overwrite all data in the sheet.\n- See the URL for the "Document ID" (Spreadsheet ID) and "Sheet ID" (Sheet ID).\n    - `https:\/\/docs.google.com\/spreadsheets\/d\/{SPREADSHEETID}\/edit#gid={SHEETID}`\n    - If a "Sheet Name" (SheetName) is set (Expert mode), the SheetName takes priority and the SheetID setting is ignored.\n- Workflow apps that include this [Automated Step] require [HTTP Authentication Settings].\n    - Create [Authentication Information] (OAuth client) in the "Google Cloud Console."\n        - https:\/\/console.cloud.google.com\/apis\/credentials\n        - Redirect URI: `https:\/\/s.questetra.net\/oauth2callback`\n        - \u21d2 Obtain a [Client ID] and [Client Secret]\n    - Configure the access token acquisition method in the [HTTP Authentication Settings] of this WorkflowApp.\n        - Set the [Authorization Endpoint URL]:\n            - `https:\/\/accounts.google.com\/o\/oauth2\/auth?access_type=offline&approval_prompt=force`\n        - Set the [Token Endpoint URL]:\n            - `https:\/\/accounts.google.com\/o\/oauth2\/token`\n        - Example [Scope] setting:\n            - `https:\/\/www.googleapis.com\/auth\/spreadsheets`\n        - (Other [Scope] setting examples):\n            - `https:\/\/www.googleapis.com\/auth\/drive`\n            - `https:\/\/www.googleapis.com\/auth\/drive.file`\n            - `https:\/\/www.googleapis.com\/auth\/spreadsheets`\n            - `https:\/\/www.googleapis.com\/auth\/spreadsheets https:\/\/www.googleapis.com\/auth\/presentations`\n        - \u21d2 Click [Get Token]. (You will grant the API requests.)\n            - The refresh token will be stored.\n            - Refresh token \u2192 Access token \u2192 API communication\n\n\u25bcNOTES-ja:\n- \u3053\u306e\uff3b\u81ea\u52d5\u5de5\u7a0b\uff3d\u3092\u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u56f3\u306b\u914d\u7f6e\u3059\u308c\u3070\u3001\u6848\u4ef6\u30d7\u30ed\u30bb\u30b9\u304c\u5230\u9054\u3059\u308b\u305f\u3073\u306b\u300cAPI\u30ea\u30af\u30a8\u30b9\u30c8\u300d\u304c\u767a\u751f\u3057\u307e\u3059\n    - "Questetra BPM Suite" \u30b5\u30fc\u30d0\uff08\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u5074\uff09\u3000\u21d2\u3000"Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8" \u306e API\uff08\u30b5\u30fc\u30d0\u5074\uff09\n    - Tab Separated Values \u30c6\u30ad\u30b9\u30c8\uff08TSV\u6587\u5b57\u5217\uff09\u306e\u30c7\u30fc\u30bf\u3067\u3001\u30b7\u30fc\u30c8\u5185\u306e\u30c7\u30fc\u30bf\u304c\u4e00\u62ec\u4e0a\u66f8\u304d\u3055\u308c\u307e\u3059\n- "\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8ID"\uff08Spreadsheet ID\uff09\u304a\u3088\u3073 "\u30b7\u30fc\u30c8ID"\uff08Sheet ID\uff09\u306f\u3001\u30b7\u30fc\u30c8\u3092\u8868\u793a\u3057\u305f\u969b\u306e URL \u3092\u53c2\u7167\u3057\u3066\u304f\u3060\u3055\u3044\n    - `https:\/\/docs.google.com\/spreadsheets\/d\/{SPREADSHEETID}\/edit#gid={SHEETID}`\n    - "\u30b7\u30fc\u30c8\u540d" (SheetName) \u304c\u8a2d\u5b9a\u3055\u308c\u305f\u5834\u5408\u3001SheetName \u8a2d\u5b9a\u304c\u512a\u5148\u3055\u308c\u307e\u3059\uff08SheetID \u8a2d\u5b9a\u306f\u7121\u8996\u3055\u308c\u307e\u3059\uff09\n- \u3053\u306e\uff3b\u81ea\u52d5\u5de5\u7a0b\uff3d\u3092\u542b\u3080\u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30a2\u30d7\u30ea\u306b\u306f\u3001\uff3bHTTP \u8a8d\u8a3c\u8a2d\u5b9a\uff3d\u304c\u5fc5\u8981\u3067\u3059\n    - "Google Cloud \u30b3\u30f3\u30bd\u30fc\u30eb" \u306b\u3066\u3001\uff3b\u8a8d\u8a3c\u60c5\u5831\uff3d\uff08OAuth\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\uff09\u3092\u4f5c\u6210\u3057\u307e\u3059\n        - https:\/\/console.cloud.google.com\/apis\/credentials\n        - \u30ea\u30c0\u30a4\u30ec\u30af\u30c8URI: `https:\/\/s.questetra.net\/oauth2callback`\n        - \u21d2\uff3b\u30af\u30e9\u30a4\u30a2\u30f3\u30c8ID\uff3d\uff08ClientId\uff09\u3068\uff3b\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u30b7\u30fc\u30af\u30ec\u30c3\u30c8\uff3d (Client Secret) \u3092\u53d6\u5f97\n    - \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30a2\u30d7\u30ea\u7b49\u306e\uff3bHTTP \u8a8d\u8a3c\u8a2d\u5b9a\uff3d\u306b\u3066\u3001\u30a2\u30af\u30bb\u30b9\u30c8\u30fc\u30af\u30f3\u53d6\u5f97\u65b9\u6cd5\u3092\u8a2d\u5b9a\u3057\u307e\u3059\n        - \uff3b\u8a8d\u53ef\u30a8\u30f3\u30c9\u30dd\u30a4\u30f3\u30c8URL\uff3d(Authorization Endpoint URL) \u306e\u8a2d\u5b9a:\n            - `https:\/\/accounts.google.com\/o\/oauth2\/auth?access_type=offline&approval_prompt=force`\n        - \uff3b\u30c8\u30fc\u30af\u30f3\u30a8\u30f3\u30c9\u30dd\u30a4\u30f3\u30c8URL\uff3d(Token Endpoint URL) \u306e\u8a2d\u5b9a:\n            - `https:\/\/accounts.google.com\/o\/oauth2\/token`\n        - \uff3b\u30b9\u30b3\u30fc\u30d7\uff3d\u8a2d\u5b9a\u4f8b:\n            - `https:\/\/www.googleapis.com\/auth\/spreadsheets`\n        - \uff08\u4ed6\u306e\uff3b\u30b9\u30b3\u30fc\u30d7\uff3d\u8a2d\u5b9a\u4f8b\uff09:\n            - `https:\/\/www.googleapis.com\/auth\/drive`\n            - `https:\/\/www.googleapis.com\/auth\/drive.file`\n            - `https:\/\/www.googleapis.com\/auth\/spreadsheets`\n            - `https:\/\/www.googleapis.com\/auth\/spreadsheets https:\/\/www.googleapis.com\/auth\/presentations`\n        - \u21d2\uff3b\u30c8\u30fc\u30af\u30f3\u306e\u53d6\u5f97\uff3d(Get Token) \u3092\u30af\u30ea\u30c3\u30af\u3057\u307e\u3059\u3002\uff08\u300cAPI\u30ea\u30af\u30a8\u30b9\u30c8\u300d\u3092\u8a31\u8afe\u3057\u307e\u3059\uff09\n            - \u30ea\u30d5\u30ec\u30c3\u30b7\u30e5\u30c8\u30fc\u30af\u30f3 (Refresh Token) \u304c\u683c\u7d0d\u3055\u308c\u307e\u3059\u3002\n            - \u30ea\u30d5\u30ec\u30c3\u30b7\u30e5\u30c8\u30fc\u30af\u30f3 (Refresh Token) \u2192 \u30a2\u30af\u30bb\u30b9\u30c8\u30fc\u30af\u30f3 (Access Token)  \u2192 API\u901a\u4fe1\n\n\u25bcAPPENDIX:\n- Numeric strings are evaluated using the `parseFloat()` function after removing commas (thousand separators).\n    - Cells that fail evaluation are left blank.\n- Datetime strings are evaluated using the `Date()` function.\n    - Cells that fail evaluation are left blank.\n    - `new Date('December 17, 1995 03:24:00')`\n    - `new Date('1995-12-17T03:24:00')`\n    - `new Date('1995-12-17 03:24')`\n    - `new Date('1995-12-17')`\n    - Note that "T00:00:00" is automatically added (to convert to local time) only for the "YYYY-MM-DD" and "YYYY\/MM\/DD" formats.\n- TSV (Tab Separated Time) Values) text assumes rectangular data.\n    - "Monthly Sales Summary," "Monthly Access Summary," "PIVOT Table," etc.\n    - Non-rectangular data is automatically formatted with an empty string (adjusted to the longest number of columns).\n    - Blank lines (including trailing newlines) are ignored.\n    - CF: "Questetra BPMS: Process, Batch Extract by Filter as TSV"\n        - https:\/\/support.questetra.com\/addons\/questetra-bpms-process-batch-extract-by-filter-as-tsv-2021\/\n- Charts in Google Sheets update automatically.\n    - Charts referenced in Google Slides do not update automatically.\n    - CF: "Google Slides: Page, Refresh Charts"\n        - https:\/\/support.questetra.com\/ja\/addons\/google-slides-page-refresh-charts-2021\/\n    - CF: "Google Slides: Page, Export as PNG"\n        - https:\/\/support.questetra.com\/ja\/addons\/google-slides-page-export-as-png-2021\/\n- Overridden via the Google Sheets API v4 "UpdateCellsRequest"\n    - Unlike the "DeleteRangeRequest", the chart's data range is not deleted.\n\n\u25bcAPPENDIX-ja:\n- \u6570\u5024\u6587\u5b57\u5217\u306f\u3001\u30ab\u30f3\u30de\uff08\u6841\u533a\u5207\u308a\u6587\u5b57\uff09\u304c\u9664\u53bb\u3055\u308c\u305f\u4e0a\u3067\u3001`parseFloat()` \u95a2\u6570\u3067\u8a55\u4fa1\u3055\u308c\u307e\u3059\n    - \u8a55\u4fa1\u306b\u5931\u6557\u3057\u305f\u30bb\u30eb\u306f\u3001\u4f55\u3082\u8ffd\u8a18\u3055\u308c\u307e\u305b\u3093\n- \u65e5\u6642\u6587\u5b57\u5217\u306f\u3001`Date()` \u95a2\u6570\u3067\u8a55\u4fa1\u3055\u308c\u307e\u3059\n    - \u8a55\u4fa1\u306b\u5931\u6557\u3057\u305f\u30bb\u30eb\u306f\u3001\u4f55\u3082\u8ffd\u8a18\u3055\u308c\u307e\u305b\u3093\n    - `new Date('December 17, 1995 03:24:00')`\n    - `new Date('1995-12-17T03:24:00')`\n    - `new Date('1995-12-17 03:24')`\n    - `new Date('1995-12-17')`\n    - \u306a\u304a "YYYY-MM-DD" \u66f8\u5f0f\u304a\u3088\u3073 "YYYY\/MM\/DD" \u66f8\u5f0f\u306e\u5834\u5408\u306b\u9650\u308a "T00:00:00" \u304c\u81ea\u52d5\u4ed8\u52a0\uff08\u30ed\u30fc\u30ab\u30eb\u30bf\u30a4\u30e0\u5316\uff09\u3055\u308c\u307e\u3059\n- TSV\uff08Tab Separated Values\uff09\u30c6\u30ad\u30b9\u30c8\u306f\u3001\u77e9\u5f62\u30c7\u30fc\u30bf\u3092\u524d\u63d0\u3068\u3057\u307e\u3059\n    - "\u6708\u6b21\u58f2\u4e0a\u96c6\u8a08"\u3001"\u6708\u6b21\u30a2\u30af\u30bb\u30b9\u96c6\u8a08"\u3001"PIVOT\u30c6\u30fc\u30d6\u30eb"\u3001\u306a\u3069\n    - \u77e9\u5f62\u3067\u306a\u3044\u30c7\u30fc\u30bf\u306f\u3001\u7a7a\u6587\u5b57\u5217\u306b\u3088\u3063\u3066\u81ea\u52d5\u6574\u5f62\u3055\u308c\u307e\u3059\uff08\u6700\u3082\u9577\u3044\u5217\u6570\u306b\u5408\u308f\u305b\u308b\uff09\n    - \u7a7a\u884c\uff08\u672b\u5c3e\u6539\u884c\u3092\u542b\u3080\uff09\u306f\u7121\u8996\u3055\u308c\u307e\u3059\n    - CF: "Questetra BPMS #\u6848\u4ef6: \u4fdd\u5b58\u6e08\u30d5\u30a3\u30eb\u30bf\u3067TSV\u4e00\u62ec\u62bd\u51fa"\n        - https:\/\/support.questetra.com\/ja\/addons\/questetra-bpms-process-batch-extract-by-filter-as-tsv-2021\/\n- Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u5185\u306e\u30b0\u30e9\u30d5\uff08Charts\uff09\u306f\u81ea\u52d5\u7684\u306b\u66f4\u65b0\u3055\u308c\u307e\u3059\n    - Google \u30b9\u30e9\u30a4\u30c9\u3067\u53c2\u7167\u3055\u308c\u3066\u3044\u308b\u30b0\u30e9\u30d5\uff08Charts\uff09\u306f\u81ea\u52d5\u7684\u306b\u306f\u66f4\u65b0\u3055\u308c\u307e\u305b\u3093\n    - CF: "Google \u30b9\u30e9\u30a4\u30c9 #\u30da\u30fc\u30b8: \u30b0\u30e9\u30d5\u5168\u66f4\u65b0"\n        - https:\/\/support.questetra.com\/ja\/addons\/google-slides-page-refresh-charts-2021\/\n    - CF: "Google \u30b9\u30e9\u30a4\u30c9 #\u30da\u30fc\u30b8: PNG\u30a8\u30af\u30b9\u30dd\u30fc\u30c8"\n        - https:\/\/support.questetra.com\/ja\/addons\/google-slides-page-export-as-png-2021\/\n- Google Sheets API v4 \u306e "UpdateCellsRequest" \u3092\u901a\u3058\u3066\u66f8\u304d\u63db\u3048\u3089\u308c\u307e\u3059\n    - "DeleteRangeRequest" \u3068\u306f\u7570\u306a\u308a\u30b0\u30e9\u30d5\uff08Charts\uff09\u306e\uff3b\u30c7\u30fc\u30bf\u7bc4\u56f2\uff3d\u304c\u524a\u9664\u3055\u308c\u308b\u3053\u3068\u306f\u3042\u308a\u307e\u305b\u3093\n\n*\/\n\n<\/code><\/pre><\/div>\n<\/div><\/details><\/div>\n<\/div>\n\n\n\n
Download<\/span><\/h3>\n\n\n\n