{"id":183925,"date":"2026-05-18T15:45:53","date_gmt":"2026-05-18T06:45:53","guid":{"rendered":"https:\/\/support.questetra.com\/?p=183925"},"modified":"2026-07-17T12:30:36","modified_gmt":"2026-07-17T03:30:36","slug":"google-sheets-multiple-cells-update-202604","status":"publish","type":"post","link":"https:\/\/support.questetra.com\/en\/addons\/google-sheets-multiple-cells-update-202604\/","title":{"rendered":"Google Sheets: Spreadsheets Multiple Cells Value, Update"},"content":{"rendered":"\n

Google Sheets: Spreadsheets Multiple Cells Value, Update<\/strong><\/h4>\n\n\n\n

Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8: \u8907\u6570\u30bb\u30eb\u5024, \u66f4\u65b0\uff08\u500b\u5225\u6307\u5b9a\uff09<\/a><\/p>\n\n\n\n

Overwrites mulitiple cells of a spreadsheet. The value will be parsed as if user typed text into the Google Sheets UI. For example, if date formated, the value “12-24” will be converted to “12\/24\/2021”. Or, “’12-31” will be updated as the string “12-31”.<\/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: Set Cell1 Coordinate into which Value inserted (eg “A1”) *<\/span>#{EL}<\/sup><\/span><\/dd>\n
StrConfD1<\/dt>
D1: Set Value1 to Overwrite#{EL}<\/sup><\/span><\/dd>\n
StrConfC2<\/dt>
C2: Set Cell2 Coordinate into which Value inserted (eg “A1”)#{EL}<\/sup><\/span><\/dd>\n
StrConfD2<\/dt>
D2: Set Value2 to Overwrite#{EL}<\/sup><\/span><\/dd>\n
StrConfC3<\/dt>
C3: Set Cell3 Coordinate into which Value inserted (eg “A1”)#{EL}<\/sup><\/span><\/dd>\n
StrConfD3<\/dt>
D3: Set Value3 to Overwrite#{EL}<\/sup><\/span><\/dd>\n
StrConfC4<\/dt>
C4: Set Cell4 Coordinate into which Value inserted (eg “A1”)#{EL}<\/sup><\/span><\/dd>\n
StrConfD4<\/dt>
D4: Set Value4 to Overwrite#{EL}<\/sup><\/span><\/dd>\n
StrConfC5<\/dt>
C5: Set Cell5 Coordinate into which Value inserted (eg “A1”)#{EL}<\/sup><\/span><\/dd>\n
StrConfD5<\/dt>
D5: Set Value5 to Overwrite#{EL}<\/sup><\/span><\/dd>\n
StrConfC6<\/dt>
C6: Set Cell6 Coordinate into which Value inserted (eg “A1”)#{EL}<\/sup><\/span><\/dd>\n
StrConfD6<\/dt>
D6: Set Value6 to Overwrite#{EL}<\/sup><\/span><\/dd>\n
StrConfC7<\/dt>
C7: Set Cell7 Coordinate into which Value inserted (eg “A1”)#{EL}<\/sup><\/span><\/dd>\n
StrConfD7<\/dt>
D7: Set Value7 to Overwrite#{EL}<\/sup><\/span><\/dd>\n
StrConfC8<\/dt>
C8: Set Cell8 Coordinate into which Value inserted (eg “A1”)#{EL}<\/sup><\/span><\/dd>\n
StrConfD8<\/dt>
D8: Set Value8 to Overwrite#{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 ==\n  const oauth2 = configs.getObject( \"AuthzConfA\" ); \/\/ required\n  const strAuthzSetting = configs.get( \"AuthzConfA\" );\n  engine.log( \" AutomatedTask Config: Authz Setting: \" + strAuthzSetting );\nconst strFileId       = configs.get( \"StrConfB1\" ) + \"\"; \/\/ required\nconst strSheetName    = configs.get( \"StrConfB2\" ) + \"\";\n\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\/update\n\n\n\/\/8\u30bb\u30eb\u5206\u3092\u30eb\u30fc\u30d7\nfor (let i = 1; i <= 8; i++ ) {\n  const strCellId      = configs.get( \"StrConfC\" + i ) + \"\";\n  const strValue       = configs.get( \"StrConfD\" + i ) + \"\";\n  if (strCellId) {\n\n    \/\/ request1, prepare\n    let request1Obj = {};\n        request1Obj.values = [];\n        request1Obj.values[0] = [];\n        request1Obj.values[0][0] = strValue;\n    let putUri1  = \"https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/\" +\n                   strFileId + \"\/values\/\" + strSheetName + \"!\" + strCellId;\n    let request1 = httpClient.begin(); \/\/ HttpRequestWrapper\n        request1 = request1.authSetting( oauth2 ); \/\/ with \"Authorization: Bearer XX\"\n        request1 = request1.queryParam( \"valueInputOption\", \"USER_ENTERED\" );\n        request1 = request1.body( JSON.stringify( request1Obj ), \"application\/json\" );\n\n    \/\/ request1, try\n    const response1     = request1.put( putUri1 ); \/\/ HttpResponseWrapper\n      engine.log( \" AutomatedTask ApiRequest1 Start: \" + putUri1 );\n    const response1Code = response1.getStatusCode() + \"\";\n    const response1Body = response1.getResponseAsString() + \"\";\n      engine.log( \" AutomatedTask ApiResponse Status: \" + response1Code );\n    if( response1Code !== \"200\"){\n      throw new Error( \"\\n AutomatedTask UnexpectedResponseError: \" +\n                        response1Code + \"\\n\" + response1Body + \"\\n\" );\n    }\n\n    \/\/ response1, parse\n    \/* engine.log( response1Body ); \/\/ debug\n    {\n      \"spreadsheetId\": \"1SK4mXXXXXq7ZriYYYYYOJHmIZZZZZAtjynWWWWW-qlE\",\n      \"updatedRange\": \"Sheet1!C3\",\n      \"updatedRows\": 1,\n      \"updatedColumns\": 1,\n      \"updatedCells\": 1\n    }\n    *\/\n    const response1Obj = JSON.parse( response1Body );\n    engine.log( \" AutomatedTask ApiRequest1 updateRange: \" + response1Obj.updatedRange );\n\n  }\n}\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\/*\nNOTES\n- If the sheet name is omitted, Value will be inserted into the first visible sheet.\n- Cell Coordinates in A1 notation (where \"A\" is the column and \"1\" is the row of the cell)\n- If set the value to an empty string, a cell will be an empty value (deleted).\n- It also supports overwriting with multi-line strings.\n- For merged cells, the value can only be stored in the upper left cell.\n\nNOTES-ja\n- \u30b7\u30fc\u30c8\u540d\u3092\u7701\u7565\u3057\u305f\u5834\u5408\u3001\u300c\u975e\u8868\u793a\u3067\u306a\u3044\u5148\u982d\u30b7\u30fc\u30c8\u300d\u306b\u633f\u5165\u3055\u308c\u307e\u3059\u3002\n- \u30bb\u30eb\u5ea7\u6a19\u306fA1\u8a18\u6cd5\u3067\u6307\u5b9a\u3057\u307e\u3059\uff08\"A\" \u306f\u30bb\u30eb\u306e\u5217\u756a\u53f7\u3001\"1\" \u306f\u30bb\u30eb\u306e\u884c\u756a\u53f7\u3067\u3059\uff09\n- \u4e0a\u66f8\u304d\u5024\u304c\u7a7a\u6587\u5b57\u5217\u306e\u5834\u5408\u3001\u30bb\u30eb\u30c7\u30fc\u30bf\u306f\u7a7a\u306b\u306a\u308a\u307e\u3059\u3002\uff08\u524a\u9664\u3055\u308c\u307e\u3059\uff09\n- \u8907\u6570\u884c\u6587\u5b57\u5217\u3067\u306e\u4e0a\u66f8\u304d\u306b\u3082\u5bfe\u5fdc\u3057\u3066\u3044\u307e\u3059\u3002\n- \u7d50\u5408\u30bb\u30eb\uff08Merged Cell\uff09\u306e\u5834\u5408\u3001\u5024\u306f\u300c\u5de6\u4e0a\u306e\u30bb\u30eb\u300d\u306b\u306e\u307f\u4fdd\u6301\u3055\u308c\u307e\u3059\u3002\n*\/\n\n\n\/*\nAPPENDIX\n- OAuth Setting sample [HTTP Authorization Setting]\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    - to get your \"ClientId\" and \"Secret\"\n        - Access to https:\/\/console.developers.google.com\/apis\/credentials\n- Authorization Scopes (one of the following)\n    - https:\/\/www.googleapis.com\/auth\/drive\n    - https:\/\/www.googleapis.com\/auth\/drive.file\n    - https:\/\/www.googleapis.com\/auth\/spreadsheets\n    - About the levels of access\n        - https:\/\/developers.google.com\/identity\/protocols\/oauth2\/scopes#sheets\n\nAPPENDIX-ja\n- OAuth \u8a2d\u5b9a\u4f8b\uff08\uff3bHTTP\u8a8d\u8a3c\u8a2d\u5b9a\uff3d\u306e\u4f8b\uff09\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    - \u306a\u304a \"ClientId\" \u3068 \"Secret\" \u306f\u958b\u767a\u8005\u30b3\u30f3\u30bd\u30fc\u30eb\u306b\u30a2\u30af\u30bb\u30b9\u3057\u3066\u53d6\u5f97\u3057\u307e\u3059\u3002\n        - Access to https:\/\/console.developers.google.com\/apis\/credentials\n- \u8a8d\u53ef\u30b9\u30b3\u30fc\u30d7\uff08\u4ee5\u4e0b\u306e\u3044\u305a\u308c\u304b\uff09\n    - https:\/\/www.googleapis.com\/auth\/drive\n    - https:\/\/www.googleapis.com\/auth\/drive.file\n    - https:\/\/www.googleapis.com\/auth\/spreadsheets\n    - \u305d\u308c\u305e\u308c\u306e\u30a2\u30af\u30bb\u30b9\u30ec\u30d9\u30eb\u306b\u3064\u3044\u3066\n        - https:\/\/developers.google.com\/identity\/protocols\/oauth2\/scopes#sheets\n*\/\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 ==\n  const oauth2 = configs.getObject( "AuthzConfA" ); \/\/ required\n  const strAuthzSetting = configs.get( "AuthzConfA" );\n  engine.log( " AutomatedTask Config: Authz Setting: " + strAuthzSetting );\nconst strFileId       = configs.get( "StrConfB1" ) + ""; \/\/ required\nconst strSheetName    = configs.get( "StrConfB2" ) + "";\n\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\/update\n\n\n\/\/8\u30bb\u30eb\u5206\u3092\u30eb\u30fc\u30d7\nfor (let i = 1; i <= 8; i++ ) {\n  const strCellId      = configs.get( "StrConfC" + i ) + "";\n  const strValue       = configs.get( "StrConfD" + i ) + "";\n  if (strCellId) {\n\n    \/\/ request1, prepare\n    let request1Obj = {};\n        request1Obj.values = [];\n        request1Obj.values[0] = [];\n        request1Obj.values[0][0] = strValue;\n    let putUri1  = "https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/" +\n                   strFileId + "\/values\/" + strSheetName + "!" + strCellId;\n    let request1 = httpClient.begin(); \/\/ HttpRequestWrapper\n        request1 = request1.authSetting( oauth2 ); \/\/ with "Authorization: Bearer XX"\n        request1 = request1.queryParam( "valueInputOption", "USER_ENTERED" );\n        request1 = request1.body( JSON.stringify( request1Obj ), "application\/json" );\n\n    \/\/ request1, try\n    const response1     = request1.put( putUri1 ); \/\/ HttpResponseWrapper\n      engine.log( " AutomatedTask ApiRequest1 Start: " + putUri1 );\n    const response1Code = response1.getStatusCode() + "";\n    const response1Body = response1.getResponseAsString() + "";\n      engine.log( " AutomatedTask ApiResponse Status: " + response1Code );\n    if( response1Code !== "200"){\n      throw new Error( "\\n AutomatedTask UnexpectedResponseError: " +\n                        response1Code + "\\n" + response1Body + "\\n" );\n    }\n\n    \/\/ response1, parse\n    \/* engine.log( response1Body ); \/\/ debug\n    {\n      "spreadsheetId": "1SK4mXXXXXq7ZriYYYYYOJHmIZZZZZAtjynWWWWW-qlE",\n      "updatedRange": "Sheet1!C3",\n      "updatedRows": 1,\n      "updatedColumns": 1,\n      "updatedCells": 1\n    }\n    *\/\n    const response1Obj = JSON.parse( response1Body );\n    engine.log( " AutomatedTask ApiRequest1 updateRange: " + response1Obj.updatedRange );\n\n  }\n}\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\/*\nNOTES\n- If the sheet name is omitted, Value will be inserted into the first visible sheet.\n- Cell Coordinates in A1 notation (where "A" is the column and "1" is the row of the cell)\n- If set the value to an empty string, a cell will be an empty value (deleted).\n- It also supports overwriting with multi-line strings.\n- For merged cells, the value can only be stored in the upper left cell.\n\nNOTES-ja\n- \u30b7\u30fc\u30c8\u540d\u3092\u7701\u7565\u3057\u305f\u5834\u5408\u3001\u300c\u975e\u8868\u793a\u3067\u306a\u3044\u5148\u982d\u30b7\u30fc\u30c8\u300d\u306b\u633f\u5165\u3055\u308c\u307e\u3059\u3002\n- \u30bb\u30eb\u5ea7\u6a19\u306fA1\u8a18\u6cd5\u3067\u6307\u5b9a\u3057\u307e\u3059\uff08"A" \u306f\u30bb\u30eb\u306e\u5217\u756a\u53f7\u3001"1" \u306f\u30bb\u30eb\u306e\u884c\u756a\u53f7\u3067\u3059\uff09\n- \u4e0a\u66f8\u304d\u5024\u304c\u7a7a\u6587\u5b57\u5217\u306e\u5834\u5408\u3001\u30bb\u30eb\u30c7\u30fc\u30bf\u306f\u7a7a\u306b\u306a\u308a\u307e\u3059\u3002\uff08\u524a\u9664\u3055\u308c\u307e\u3059\uff09\n- \u8907\u6570\u884c\u6587\u5b57\u5217\u3067\u306e\u4e0a\u66f8\u304d\u306b\u3082\u5bfe\u5fdc\u3057\u3066\u3044\u307e\u3059\u3002\n- \u7d50\u5408\u30bb\u30eb\uff08Merged Cell\uff09\u306e\u5834\u5408\u3001\u5024\u306f\u300c\u5de6\u4e0a\u306e\u30bb\u30eb\u300d\u306b\u306e\u307f\u4fdd\u6301\u3055\u308c\u307e\u3059\u3002\n*\/\n\n\n\/*\nAPPENDIX\n- OAuth Setting sample [HTTP Authorization Setting]\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    - to get your "ClientId" and "Secret"\n        - Access to https:\/\/console.developers.google.com\/apis\/credentials\n- Authorization Scopes (one of the following)\n    - https:\/\/www.googleapis.com\/auth\/drive\n    - https:\/\/www.googleapis.com\/auth\/drive.file\n    - https:\/\/www.googleapis.com\/auth\/spreadsheets\n    - About the levels of access\n        - https:\/\/developers.google.com\/identity\/protocols\/oauth2\/scopes#sheets\n\nAPPENDIX-ja\n- OAuth \u8a2d\u5b9a\u4f8b\uff08\uff3bHTTP\u8a8d\u8a3c\u8a2d\u5b9a\uff3d\u306e\u4f8b\uff09\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    - \u306a\u304a "ClientId" \u3068 "Secret" \u306f\u958b\u767a\u8005\u30b3\u30f3\u30bd\u30fc\u30eb\u306b\u30a2\u30af\u30bb\u30b9\u3057\u3066\u53d6\u5f97\u3057\u307e\u3059\u3002\n        - Access to https:\/\/console.developers.google.com\/apis\/credentials\n- \u8a8d\u53ef\u30b9\u30b3\u30fc\u30d7\uff08\u4ee5\u4e0b\u306e\u3044\u305a\u308c\u304b\uff09\n    - https:\/\/www.googleapis.com\/auth\/drive\n    - https:\/\/www.googleapis.com\/auth\/drive.file\n    - https:\/\/www.googleapis.com\/auth\/spreadsheets\n    - \u305d\u308c\u305e\u308c\u306e\u30a2\u30af\u30bb\u30b9\u30ec\u30d9\u30eb\u306b\u3064\u3044\u3066\n        - https:\/\/developers.google.com\/identity\/protocols\/oauth2\/scopes#sheets\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