How to update cells of a particular row in Google Sheets (based on JSON data) using google apps script?

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 -

enter image description here

(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!

2 answers

  • answered 2022-05-04 11:40 ziganotschka

    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:

  • answered 2022-05-04 11:50 Mike Steelson

    enable google sheets API service and try with batchupdate

    const json = { "row": 3, "data": { "2": "apple", "3": "strawberry", "5": "pineapple" } }
    
    function myFunction() {
      var ss = SpreadsheetApp.getActive()
      var sheet = ss.getActiveSheet();
      var row = json.row;
      var data = [];
      for (const [col, value] of Object.entries(json.data)) {
        data.push({
          range: `${sheet.getName()}!${columnToLetter(+col) + (+row)}`,
          values: [[`${value}`]],
        })
      }
      var resource = {
        valueInputOption: 'USER_ENTERED',
        data: data,
      };
      try { Sheets.Spreadsheets.Values.batchUpdate(resource, ss.getId()); } catch (e) { console.log(JSON.stringify(e)) }
    }
    function columnToLetter(column) {
      var temp, letter = '';
      while (column > 0) {
        temp = (column - 1) % 26;
        letter = String.fromCharCode(temp + 65) + letter;
        column = (column - temp - 1) / 26;
      }
      return letter;
    }
    

    enter image description here

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum