Skip to main content

Set cell values

Sets sheet cell values

Function SetCellValues(Val Token, Val Spreadsheet, Val ValueMapping, Val Sheet = "", Val MajorDimension = "COLUMNS") Export

ParameterCLI optionTypeRequiredDescription
Token--tokenStringToken
Spreadsheet--spreadsheetStringSpreadsheetID
ValueMapping--dataMap Of KeyAndValueFill data where the key is the cell name like A1
Sheet--sheetnameStringSheet name (first sheet by default)
MajorDimension--dimStringMain dimension when filling the array range

Returns: Map Of KeyAndValue - serialized JSON response from Google


1C:Enterprise/OneScript code example
    Token       = "ya29.a0AQQ_BDTgQbn9c5rXhPK3FCakms_7oKkdIWE1CCQFnSPftb2RxvloVqnYOcnsAKhXrD0Gq8Tz2Kgn5IBXG5E25uksZfvvTtXob-jFcuj_9LZYs9La4HrcFk766...";
Spreadsheet = "16uPAewX6jwjTdyVlE0O8adQngnoCMbUHQ5Ek36-IPaQ";
Sheet = "Sheet2";

ValueMapping = New Map;
ValueMapping.Insert("A1", "ThisIsA1");
ValueMapping.Insert("A2", "ThisIsA2");
ValueMapping.Insert("B2", "ThisIsB2");
ValueMapping.Insert("B3", "ThisIsB3");
ValueMapping.Insert("A3", "ThisIsA3");
ValueMapping.Insert("A4", "ThisIsA4");
ValueMapping.Insert("B1", "ThisIsB1");
ValueMapping.Insert("B4", "ThisIsB4");

Result = OPI_GoogleSheets.SetCellValues(Token, Spreadsheet, ValueMapping, Sheet);
    oint gsheets SetCellValues \
--token "***" \
--spreadsheet "1NYD5B4YQnXCfmGMzXGh7GzA9Uk1tifgDK4A8682lufM" \
--data "{'A1':'ThisIsA1','A2':'ThisIsA2','B2':'ThisIsB2','B3':'ThisIsB3','A3':'ThisIsA3','A4':'ThisIsA4','B1':'ThisIsB1','B4':'ThisIsB4'}" \
--sheetname "Sheet2"
Result
{
"spreadsheetId": "1NYD5B4YQnXCfmGMzXGh7GzA9Uk1tifgDK4A8682lufM",
"totalUpdatedRows": 4,
"totalUpdatedColumns": 2,
"totalUpdatedCells": 8,
"totalUpdatedSheets": 1,
"responses": [
{
"spreadsheetId": "1NYD5B4YQnXCfmGMzXGh7GzA9Uk1tifgDK4A8682lufM",
"updatedRange": "Sheet2!A1",
"updatedRows": 1,
"updatedColumns": 1,
"updatedCells": 1
},
{
"spreadsheetId": "1NYD5B4YQnXCfmGMzXGh7GzA9Uk1tifgDK4A8682lufM",
"updatedRange": "Sheet2!A2",
"updatedRows": 1,
"updatedColumns": 1,
"updatedCells": 1
},
{
"spreadsheetId": "1NYD5B4YQnXCfmGMzXGh7GzA9Uk1tifgDK4A8682lufM",
"updatedRange": "Sheet2!B2",
"updatedRows": 1,
"updatedColumns": 1,
"updatedCells": 1
},
{
"spreadsheetId": "1NYD5B4YQnXCfmGMzXGh7GzA9Uk1tifgDK4A8682lufM",
"updatedRange": "Sheet2!B3",
"updatedRows": 1,
"updatedColumns": 1,
"updatedCells": 1
},
{
"spreadsheetId": "1NYD5B4YQnXCfmGMzXGh7GzA9Uk1tifgDK4A8682lufM",
"updatedRange": "Sheet2!A3",
"updatedRows": 1,
"updatedColumns": 1,
"updatedCells": 1
},
{
"spreadsheetId": "1NYD5B4YQnXCfmGMzXGh7GzA9Uk1tifgDK4A8682lufM",
"updatedRange": "Sheet2!A4",
"updatedRows": 1,
"updatedColumns": 1,
"updatedCells": 1
},
{
"spreadsheetId": "1NYD5B4YQnXCfmGMzXGh7GzA9Uk1tifgDK4A8682lufM",
"updatedRange": "Sheet2!B1",
"updatedRows": 1,
"updatedColumns": 1,
"updatedCells": 1
},
{
"spreadsheetId": "1NYD5B4YQnXCfmGMzXGh7GzA9Uk1tifgDK4A8682lufM",
"updatedRange": "Sheet2!B4",
"updatedRows": 1,
"updatedColumns": 1,
"updatedCells": 1
}
]
}