{"id":165300,"date":"2024-08-30T16:23:03","date_gmt":"2024-08-30T07:23:03","guid":{"rendered":"https:\/\/support.questetra.com\/?p=165300"},"modified":"2024-11-20T17:41:10","modified_gmt":"2024-11-20T08:41:10","slug":"google-sheets-cells-bulk-update","status":"publish","type":"post","link":"https:\/\/support.questetra.com\/ja\/addons\/google-sheets-cells-bulk-update\/","title":{"rendered":"Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8: \u8907\u6570\u30bb\u30eb\u5024, \u4e00\u62ec\u66f4\u65b0\uff08\u7bc4\u56f2\u6307\u5b9a\uff09"},"content":{"rendered":"\n

Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8: \u8907\u6570\u30bb\u30eb\u5024, \u4e00\u62ec\u66f4\u65b0\uff08\u7bc4\u56f2\u6307\u5b9a\uff09<\/strong><\/h4>\n\n\n\n

Google Sheets: Spreadsheets Cells Value, Bulk Update (range)<\/a><\/p>\n\n\n\n

\u30b7\u30fc\u30c8\u5185\u306e\u6307\u5b9a\u3055\u308c\u305f\u7bc4\u56f2\u306e\u30bb\u30eb\uff08\u30c6\u30fc\u30d6\u30eb\u578b\u306e\u8868\u3092\u60f3\u5b9a\uff09\u306e\u30c7\u30fc\u30bf\u3092\u4e00\u62ec\u66f4\u65b0\u3057\u307e\u3059\u3002\uff08\u6539\u884c\u3092\u542b\u3080\u5024\u306f\u6307\u5b9a\u3067\u304d\u307e\u305b\u3093\uff09<\/p>\n\n\n\n

\"Auto<\/figure>\n\n\n\n
\n
Configs for this Auto Step<\/summary>
\n
\n
AuthzConfA<\/dt>
A: HTTP\u8a8d\u8a3c\u8a2d\u5b9a\u3092\u9078\u629e\u3057\u3066\u304f\u3060\u3055\u3044 *<\/span><\/dd>\n
StrConfB1<\/dt>
B1: \u5024\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: \u5024\u633f\u5165\u5148\u306e\u30b7\u30fc\u30c8\u540d\u3092\u30bb\u30c3\u30c8\u3057\u3066\u304f\u3060\u3055\u3044#{EL}<\/sup><\/span><\/dd>\n
StrConfC1<\/dt>
C1: \u5bfe\u8c61\u7bc4\u56f2\u306e\u30bb\u30ebfrom *<\/span>#{EL}<\/sup><\/span><\/dd>\n
StrConfC2<\/dt>
C2: \u5bfe\u8c61\u7bc4\u56f2\u306e\u30bb\u30ebto *<\/span>#{EL}<\/sup><\/span><\/dd>\n
StrConfC3<\/dt>
C3: \u30bb\u30eb\u3092\u66f4\u65b0\u3059\u308b\u305f\u3081\u306e\u8a2d\u5b9aTSV#{EL}<\/sup><\/span><\/dd>\n\n<\/dl>\n<\/div><\/details><\/div>\n<\/div>\n\n\n\n
\n
Script (click to open)<\/summary>
\n
\/\/ GraalJS Script (engine type: 3)\n\n\n\/\/\/\/\/\/\/\/ START "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\nmain();\nfunction main(){ \n\n\/\/\/\/ == Config Retrieving \/ \u5de5\u7a0b\u30b3\u30f3\u30d5\u30a3\u30b0\u306e\u53c2\u7167 ==\nconst strAuthzSetting = configs.get( "AuthzConfA" ); \/\/ required\n  engine.log( " AutomatedTask Config: Authz Setting: " + strAuthzSetting );\nconst strFileId       = configs.get( "StrConfB1" ) + ""; \/\/ required\nconst strSheetName    = configs.get( "StrConfB2" ) + "";\n\nconst cell1           = configs.get( "StrConfC1" );\nconst cell2           = configs.get( "StrConfC2" );\nconst tsv             = configs.get( "StrConfC3" );\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\n\/\/\/\/ Overwrite the value in the cell\n\/\/\/ Sets values in a range of a spreadsheet.\n\/\/\/ (Google Sheets > API v4)\n\/\/\/ https:\/\/developers.google.com\/sheets\/api\/reference\/rest\/v4\/spreadsheets.values\/batchUpdate?hl=ja\n\n\/\/ request1, prepare\n\nlet request1Obj = {};\n    request1Obj.valueInputOption = "USER_ENTERED";\n    request1Obj.data = [];\n    request1Obj.data[0] = {};\n    request1Obj.data[0].range = "'" + strSheetName + "'!" + cell1 + ":" + cell2;\n    request1Obj.data[0].majorDimension = "COLUMNS";\n    request1Obj.data[0].values = [];\n\n\/*\n\/\/\u8ee2\u7f6e\u3057\u3066\u306a\u3044\u5834\u5408\n    const arrLine = tsv.split(\/\\r\\n|\\n\/);\n engine.log("arrLine:"+arrLine.length)\n    for ( let i = 0; i < arrLine.length; i++ ) {\n engine.log("i:"+i)\n      let array = [];\n      const arrCell = arrLine[i].split("\\t");\n engine.log("arrCell:"+arrCell.length)\n      for ( let j = 0; j < arrCell.length; j++ ) {\n engine.log("j:"+j)\n engine.log("arrCell[j]:"+arrCell[j])\n        array.push(arrCell[j]);\n      }\n      request1Obj.data[0].values[i] = array;\n    }\n*\/\n\n\/\/TSV\u3092\u8ee2\u7f6e\u3057\u3064\u3064\u4e8c\u6b21\u5143\u914d\u5217\u306b\u5c55\u958b\n    const arrLine = tsv.split(\/\\r\\n|\\n\/);\n\/\/ engine.log("arrLine:"+arrLine.length)\n    for ( let i = 0; i < arrLine.length; i++ ) {\n\/\/ engine.log("i:"+i)\n      const arrCell = arrLine[i].split("\\t");\n\/\/ engine.log("arrCell:"+arrCell.length)\n      for ( let j = 0; j < arrCell.length; j++ ) {\n\/\/ engine.log("j:"+j)\n\/\/ engine.log("arrCell[j]:"+arrCell[j])\n        if (i == 0) {\n          let array = [];\n          request1Obj.data[0].values[j] = array;\n          request1Obj.data[0].values[j][i] = arrCell[j]\n        } else {\n          request1Obj.data[0].values[j][i] = arrCell[j]\n        }\n      }\n    }\n\nengine.log("JSON.stringify( request1Obj ):" + JSON.stringify( request1Obj ) + "");\n\nlet postUri1  = "https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/" +\n               strFileId + "\/values:batchUpdate";\nlet request1 = httpClient.begin(); \/\/ HttpRequestWrapper\n    request1 = request1.authSetting( strAuthzSetting ); \/\/ with "Authorization: Bearer XX"\n    request1 = request1.body( JSON.stringify( request1Obj ), "application\/json" );\n\n\/\/ request1, try\nconst response1     = request1.post( postUri1 ); \/\/ HttpResponseWrapper\n  engine.log( " AutomatedTask ApiRequest1 Start: " + postUri1 );\nconst response1Code = response1.getStatusCode() + "";\nconst response1Body = response1.getResponseAsString() + "";\n  engine.log( " AutomatedTask ApiResponse Status: " + response1Code );\nif( response1Code !== "200"){\n  throw new Error( "\\n AutomatedTask UnexpectedResponseError: " +\n                    response1Code + "\\n" + response1Body + "\\n" );\n}\n\nconst response1Obj = JSON.parse( response1Body );\nengine.log( " AutomatedTask ApiRequest1 updateRange: " + response1Obj.updatedRange );\n\n\n\/\/\/\/ == Data Updating \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 ==\n\/\/ (No Output except Console Log and response0Obj.values[0][0] )\n\n\n} \/\/\/\/\/\/\/\/ END "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\n\n<\/code><\/pre><\/div>\n<\/div><\/details><\/div>\n<\/div>\n\n\n\n
<\/path><\/svg> Download<\/gwmw><\/h3>\n\n\n\n