How to mold the data to my desired format?-CodePudding

I am a beginner, need help in coding

function userClicked(arrData){
  var url = " ";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("sheet1");
  ws.appendRow(arrData);
};

Result appended in one cell

{DESCRIPTION=Mobile, UNITCOST=10000, QTYHRRATE=2, AMOUNT=2000}

How I wanted is

Column1     Column2  Column3   Column4
DESCRIPTION UNITCOST QTYHRRATE AMOUNT
Mobile      10000      2       2000

CodePudding user response：

Modification points:

From your showing script and {DESCRIPTION=Mobile, UNITCOST=10000, QTYHRRATE=2, AMOUNT=2000} of result appended in one cell, I guessed that your value of arrData might be [{ "DESCRIPTION": "Mobile", "UNITCOST": "10000", "QTYHRRATE": "2", "AMOUNT": "2000" }]. If my understanding is correct, in your showing script, by ws.appendRow(arrData);, the value of [{ "DESCRIPTION": "Mobile", "UNITCOST": "10000", "QTYHRRATE": "2", "AMOUNT": "2000" }] is put in a cell as {DESCRIPTION=Mobile, UNITCOST=10000, QTYHRRATE=2, AMOUNT=2000}. I thought that this might be the reason for your issue.

When these points are reflected in your script for achieving your goal, how about the following modification?

Modified script:

function userClicked(arrData) {
  var url = "###"; Please set your Spreadsheet URL.

  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("sheet1");
  var header = Object.keys(arrData[0]); // Or ["DESCRIPTION","UNITCOST","QTYHRRATE","AMOUNT"]
  var values = [header, ...arrData.map(o => header.map(h => o[h]))];
  ws.getRange(ws.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
}

In this modification, if the value of arrData is [{ "DESCRIPTION": "Mobile", "UNITCOST": "10000", "QTYHRRATE": "2", "AMOUNT": "2000" }], the values are appended to the sheet by splitting the key and value from JSON object.
At JSON, the order of properties in a JSON object is not preserved. By this, if the result is not your expected result, please use ["DESCRIPTION","UNITCOST","QTYHRRATE","AMOUNT"] instead of Object.keys(arrData[0]) as header value.

Note:

If an error occurs and the result is not your expected result, can you provide a sample value of arrData? By this, I would like to modify the script.

References:

CodePudding user response：

Try this:

function userClicked(arrData){
  var url = " ";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("sheet1");
  ws.getRange(ws.getLastRow()   1, 1,arrData.length,arrData[0].length).setValues(arrData);
}

arrData must be 2 dimensional array