{"id":177237,"date":"2025-09-12T09:23:13","date_gmt":"2025-09-12T00:23:13","guid":{"rendered":"https:\/\/support.questetra.com\/?p=177237"},"modified":"2025-09-16T17:24:10","modified_gmt":"2025-09-16T08:24:10","slug":"google-sheets-sheet-overwrite-with-tsv-2025","status":"publish","type":"post","link":"https:\/\/support.questetra.com\/en\/addons\/google-sheets-sheet-overwrite-with-tsv-2025\/","title":{"rendered":"Google Sheets #Sheet: Overwrite with TSV"},"content":{"rendered":"\n

Google Sheets #Sheet: Overwrite with TSV<\/strong><\/h4>\n\n\n\n

translate<\/span> Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8 #Sheet: TSV\u3067\u4e0a\u66f8\u304d<\/a><\/p>\n\n\n\n

Pastes TSV data to the specified coordinates in the specified sheet. Only the selected columns can be pasted. Specify the column ID with “d” for datetime data and “n” for numeric data. Example of column specification: “0,2d,5n”.<\/p>\n\n\n\n

\"Auto<\/figure>\n\n\n\n
\n
Configs for this Auto Step<\/summary>
\n
\n
AuthzConfU<\/dt>
U: Select HTTP_Authz Setting *<\/span><\/dd>\n
StrConfA1<\/dt>
A1: Set TSV *<\/span>#{EL}<\/sup><\/span><\/dd>\n
StrConfA2<\/dt>
A2: Set Column IDs to be Thrown (eg: “0,1,5d,3n,6”) *<\/span>#{EL}<\/sup><\/span><\/dd>\n
StrConfB1<\/dt>
B1: Set ID of Spreadsheet into which Dataset will be inserted *<\/span>#{EL}<\/sup><\/span><\/dd>\n
StrConfB2<\/dt>
B2: Set ID of Sheet into which Dataset inserted (default: “0”)#{EL}<\/sup><\/span><\/dd>\n
StrConfB3X<\/dt>
B3X: Set X-Coordinate-Index to Start Overwrite at (default: “0”)#{EL}<\/sup><\/span><\/dd>\n
StrConfB3Y<\/dt>
B3Y: Set Y-Coordinate-Index to Start Overwrite at (default: “0”)#{EL}<\/sup><\/span><\/dd>\n<\/div>\n
StrConfB2b<\/dt>
B2b: Set Sheet Name instead of B2 (SheetID)#{EL}<\/sup><\/span><\/dd>\n
SelectConfInY<\/dt>
InY: Select NUMERIC for Number of Input TSV Lines (update)<\/dd>\n
SelectConfInX<\/dt>
InX: Select NUMERIC for Number of Input TSV Cols (update)<\/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