I am getting data in the script in JSON format (through url parameters), which looks something like this - e.g. {"row":3,"data":{"2":"apple","3":"strawberry","5":"pineapple"}}. (formatted json) -

{
  "row": 3,
  "data": {
    "2": "nyc",
    "3": "strawberry",
    "5": "18"
    ...
  }
}

Now, to update cells we need it's row number and column number - row in the json is the row number, and 2 3 5 are the column numbers of the cells that are supposed to be updated with the value given.

How should my code look like if I want to update the given cells with their respective values? Here's a representation of what I want to do -

(As you can see, for row number 3, cell values for columns 2, 3 & 5 have been updated)

I am unable to figure out how to parse this JSON and get a list of targeted columns and their values, and how to update cells in using apps script... Any help is appreciated :) Thanks!

CodePudding user response：

You can use the method Object.entries() to iterate through the keys and values of your nested object

• Then use the Apps Script methods getRange(row, col) and setValue() to set the values into the sheet

Sample:

function myFunction() {
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var json = {
    "row": 3,
    "data": {
      "2": "nyc",
      "3": "strawberry",
      "5": "18"
    }
  }  
  var row = json.row;
  var data = json.data;
  for (const [col, value] of Object.entries(data)) {
    sheet.getRange(row, col).setValue(value);
  } 
}

• Keep in mind that the method setValue() will be slow if your JSON is big and you set many values. In this case it is best to store the values in an array first and then set them into the sheet with a single call of setValues(). This requires an adjacent value range though.

References: