{"id":183480,"date":"2026-05-22T14:00:46","date_gmt":"2026-05-22T05:00:46","guid":{"rendered":"https:\/\/support.questetra.com\/?p=183480"},"modified":"2026-06-22T14:14:13","modified_gmt":"2026-06-22T05:14:13","slug":"google-sheets-cells-bulk-update-202604","status":"publish","type":"post","link":"https:\/\/support.questetra.com\/en\/addons\/google-sheets-cells-bulk-update-202604\/","title":{"rendered":"Google Sheets: Spreadsheets Cells Value, Bulk Update (range)"},"content":{"rendered":"\n

Google Sheets: Spreadsheets Cells Value, Bulk Update (range)<\/strong><\/h4>\n\n\n\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<\/a><\/p>\n\n\n\n

Bulk update cells data at the specified range in a sheet. (Values containing line breaks cannot be specified)<\/p>\n\n\n\n

\"Auto<\/figure>\n\n\n\n
\n
Configs for this Auto Step<\/summary>
\n
\n
AuthzConfA<\/dt>
A: Select HTTP_Authz Setting *<\/span><\/dd>\n
StrConfB1<\/dt>
B1: Set ID of Spreadsheet into which Value will be inserted *<\/span>#{EL}<\/sup><\/span><\/dd>\n
StrConfB2<\/dt>
B2: Set Name of Sheet into which Value will be inserted#{EL}<\/sup><\/span><\/dd>\n
StrConfC1<\/dt>
C1: Target Range From Cell *<\/span>#{EL}<\/sup><\/span><\/dd>\n
StrConfC2<\/dt>
C2: Target Range To Cell *<\/span>#{EL}<\/sup><\/span><\/dd>\n
StrConfC3<\/dt>
C3: Settings TSV of Update Cells#{EL}<\/sup><\/span><\/dd>\n\n<\/dl>\n<\/div><\/details><\/div>\n<\/div>\n\n\n\n
\n
Script (click to open)<\/summary>
\n
\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( oauth2 ); \/\/ 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\nScriptEncoded\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 oauth2 = configs.getObject( "AuthzConfA" ); \/\/ required\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( oauth2 ); \/\/ 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<\/code><\/pre><\/div>\n<\/div><\/details><\/div>\n<\/div>\n\n\n\n
Download<\/span><\/h3>\n\n\n\n