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

user_17056122 0
Viewed 577

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!

google-apps-script google-sheets
2 Answers

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:

user_115997890

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;
}

  • use Object.entries to retrieve the columns and values

  • rebuild sheets and ranges with ${sheet.getName()}!${columnToLetter(+col) + (+row)} to obtain addresses as sheet1!A1 for instance

  • column in number is transformed in letter with columnToLetter function

  • use batchUpdate to update

enter image description here

user_150193800
Related