Append multiple row instead of single row from json

Question

I have script to copy json data to google sheet, bus Is there any way to append multiple rows instead of single row

Can anybody explain how i change this script ? Thanks

function doPost(request = {}) {
  const { parameter, postData: { contents, type } = {} } = request; //request data
  const { dataReq = {} } = JSON.parse(contents); //content
  const { fname = {} } = JSON.parse(contents); //function name

  const response = {
    status: "function not found: " + fname, // prepare response in function not found
    data2: dataReq
  }
  switch (fname) { //function selection
    case 'pasteData':
      var output = JSON.stringify(pasteDAta(dataReq)) //call function with data from request
      break
    default:
      var output = JSON.stringify(response)
      break
  }
  return ContentService.createTextOutput(output).setMimeType(ContentService.MimeType.JSON); //response to frontend
}
function pasteDAta(dataReq) {
  const id = '1_27rjNQmlXrwVKpLWUbGrJYPJufGRa7Dk-XEKcNAHr0'; //id of Google Sheet
  var sheet = SpreadsheetApp.openById(id).getSheetByName('Sheet1'); //sheet
  var headings = sheet.getDataRange().getValues()[0]; //Headers
  var i = 0 //to test the times that efectively adds rows the forEach function
  
  dataReq.forEach((a) => { //go trought every item on dataReq as 'a'
    let holder = []; //to steore temp the elements
    for (x in headings) { //to add in order of Headers on sheet
      let output = (headings[x] in a) ? a[headings[x]] : ''; //if exist add, if not empty
      holder.push(output); //add to holder
    }
    sheet.appendRow(holder); //put holder(order data) on sheet
    i += 1 //to test the times
  });
  return "Numbers of sheets added: "+i;
}

user_7108653 · Answer

In your script, how about the following modification?

From:

var i = 0 //to test the times that efectively adds rows the forEach function

dataReq.forEach((a) => { //go trought every item on dataReq as 'a'
  let holder = []; //to steore temp the elements
  for (x in headings) { //to add in order of Headers on sheet
    let output = (headings[x] in a) ? a[headings[x]] : ''; //if exist add, if not empty
    holder.push(output); //add to holder
  }
  sheet.appendRow(holder); //put holder(order data) on sheet
  i += 1 //to test the times
});
return "Numbers of sheets added: "+i;

To:

var values = dataReq.map((a) => {
  let holder = [];
  for (x in headings) {
    let output = (headings[x] in a) ? a[headings[x]] : '';
    holder.push(output);
  }
  return holder;
});
var len = values.length;
sheet.getRange(sheet.getLastRow() + 1, 1, len, values[0].length).setValues(values);
return "Numbers of sheets added: " + len;

In this modification, map is used instead of forEach. And, a 2-dimensional array is returned. This array is appended to the sheet using setValues.

Note:

When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".
If this modification was not the direct solution to your issue, when you provide the sample value of dataReq, I think that it will help to think of the modification points.

Append multiple row instead of single row from json

1 Answers

From:

To:

Note:

References: