Right now, If i run for loop

{“FirstName” : “Kim”}
{“LastName” : “hana”}
{“Phone” : “010-1234-5648”}
{“Email” : “[email protected]”}

It comes out like this. But the result I want is

{“FirstName” : “Kim”, “LastName” : “hana”, “Phone” : “010-1234-5648”, “Email” : “[email protected]”}

I want to make it come out like this. What should I do?

This is my code that I just wrote.

function testRun(){
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var LastRow = ss.getLastRow();
    var LastCol = ss.getLastColumn();

    var arr = [];
    for(var i = 1; i<=LastCol; i   ){
        var fieldName = ss.getRange(1,i).getValue();
        arr.push(fieldName);
    }

    //arr = [FirstName, LastName, Phone, Email]

    for(var i =2;i<=LastRow;i  ){
        for(var j=1;j<=LastCol;j  ){
            var payload =  Utilities.jsonStringify({
              [arr[j-1]] : ss.getRange(i,j).getValue()}
            );
        }
    }
 }

CodePudding user response：

Your code creates separate objects in each iteration. Create an empty object for every Person, then add the properties you need.

This should look something like this:

for(var i=2; i<=LastRow; i  ){
    var payload = {}
    for(var j=1; j<=LastCol; j  ){
        payload[arr[j-1]] = ss.getRange(i,j).getValue()};
    }
}

CodePudding user response：

Modification points:

• In your showing script, it seems that payload is not used.
• When getValue() is used in a loop, the process cost becomes high. Ref

When these points are reflected in a sample script for achieving your goal, it becomes as follows.

Sample script:

When your showing script is modified, how about the following modification?

function testRun() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var LastRow = ss.getLastRow();
  var LastCol = ss.getLastColumn();
  var [header, ...values] = ss.getRange(1, 1, LastRow, LastCol).getValues();
  var arr = [];
  for (var i = 0; i < LastRow - 1; i  ) {
    var temp = {};
    for (var j = 0; j < LastCol; j  ) {
      temp[header[j]] = values[i][j];
    }
    arr.push(temp);
  }
  console.log(JSON.stringify(arr))
}

• When this script is run, arr is [{"FirstName":"Kim","LastName":"hana","Phone":"010-1234-5648","Email":"[email protected]"}].

• As another apporach, I thought that you might be able to also use the following sample script.

    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var [header, ...values] = ss.getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).getValues();
    var arr = values.map(r => header.reduce((o, h, j) => (o[h] = r[j], o), {}));
    console.log(JSON.stringify(arr))