CODEWITHSUNDEEP
javascriptarraysloopsgoogle-apps-scriptgoogle-sheets
Схирисх Шреста
Схирисх Шреста

Reputation: 265

setValues doesn't iterate through the arrays

Google-apps script for Spreadsheet

I have an Object array of variable length. [{}, {}] I use for loop(i=0; i<Obj.length; i++) to iterate through each Object item and get the values off the keys with another for loop(var p in Obj[i]) for Object. This then gets pushed to an empty array - row =[] - which gets populated with iterated values Obj[i][p].

When it jumps over to next Obj[i], the row array should be pushed to the google sheet and then empty itself with - row = [] initialization - then iterate over next Obj[i].

Logger.log fantastically logs as expected:

[16-01-18 07:39:56:418 AEDT] [4 secs, , Mailbox, 7:05:47 AM, finished,     7:05:51 AM, 2016-01-17T20:05:47.142Z, 2016-01-17T20:05:51.891Z, MAILBOX]
[16-01-18 07:39:56:419 AEDT] []
[16-01-18 07:39:56:420 AEDT] pushed
[16-01-18 07:39:56:420 AEDT] [5 secs, , Adhoc, 7:05:51 AM, finished, 7:05:56 AM, 2016-01-17T20:05:51.892Z, 2016-01-17T20:05:56.896Z, ADHOC TASKS]
[16-01-18 07:39:56:421 AEDT] []
[16-01-18 07:39:56:422 AEDT] pushed
[16-01-18 07:39:56:422 AEDT] [3 secs, , Huddle, 7:05:56 AM, finished, 7:06:00 AM, 2016-01-17T20:05:56.897Z, 2016-01-17T20:06:00.187Z, HUDDLE]
[16-01-18 07:39:56:423 AEDT] []
[16-01-18 07:39:56:424 AEDT] pushed
[16-01-18 07:39:56:424 AEDT] [13 mins 3 secs, , Audits, 7:06:00 AM, finished, 7:19:03 AM, 2016-01-17T20:06:00.189Z, 2016-01-17T20:19:03.384Z, AUDITS]
[16-01-18 07:39:56:426 AEDT] []
[16-01-18 07:39:56:426 AEDT] pushed

However in the sheet, it logs only the last pushed row array and that too twice all the time, except if the main Obj array only has one Object item in it.

What am I doing wrong? Here's the code:

function getThisToTrix(Obj) {
  //  Logger.log(Obj);
  var doc = SpreadsheetApp.openById("_id"),
  sheetName="abc",
  sheet = doc.getSheetByName(sheetName),
  col = [], row = [],
  headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0], 
  nextRow = sheet.getLastRow()+1
  ;

  for(var i = 0; i<Obj.length; i++){
    for(var p in Obj[i]){
      col.push(p);
      row.push(Obj[i][p]);
    }

   Logger.log(row);
   sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

   row=[];
   Logger.log(row);
   pushed();
  }
}

function pushed(){Logger.log("pushed");}

UPDATE: Execution Transcript:

[16-01-23 00:20:46:140 HKT] Sheet.getLastColumn() [0.02 seconds]
[16-01-23 00:20:46:141 HKT] Sheet.getRange([1, 1, 1, 9]) [0 seconds]
[16-01-23 00:20:46:165 HKT] Range.getValues() [0.023 seconds]
[16-01-23 00:20:46:263 HKT] Sheet.getLastRow() [0.097 seconds]
[16-01-23 00:20:46:264 HKT] Sheet.getRange([88, 1, 3, 9]) [0 seconds]
[16-01-23 00:20:46:265 HKT] Range.setValues([[[987 ms, , Mailbox, 12:20:38 AM, finished, 12:20:39 AM, 2016-01-22T16:20:38.351Z, 2016-01-22T16:20:39.338Z, MAILBOX], [862 ms, , Audits, 12:20:39 AM, finished, 12:20:40 AM, 2016-01-22T16:20:39.352...) [0 seconds]
[16-01-23 00:20:46:458 HKT] Execution succeeded [0.243 seconds total  runtime]

Upvotes: 0

Views: 469

Answers (3)

User
User

Reputation: 66101

It seems like your question is really how to convert the object array to the proper 2D array to pass to setValues.

I would structure it more like this:

function convertToArray(objectArray) {

  var result = [];

  for(var o in objectArray){
    var row = [];
    for(var property in objectArray[o]){
      row.push(objectArray[o][property]);
    }
    result.push(row);
  }

  return result;

}

And then:

var data = convertToArray(Obj);
sheet.getRange(1,1,data.length, numberOfColumns).setValues(data);

Upvotes: 1

Схирисх Шреста
Схирисх Шреста

Reputation: 265

Checked the Execution Transcript to find out getRange was calling same row. Bit silly. This ended up looking like some panic callout for help. *smh

function getThisToTrix(Obj) {
  //  Logger.log(Obj);
  var doc = SpreadsheetApp.openById("_id"),
  sheetName="abc",
  sheet = doc.getSheetByName(sheetName),
  col = [], row = [],
  headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0], 
  nextRow = sheet.getLastRow()+1
  ;

  for(var i = 0; i<Obj.length; i++){
    for(var p in Obj[i]){
      col.push(p);
      row.push(Obj[i][p]);
    }

   Logger.log(row);
   sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
   nextRow = sheet.getLastRow()+1;   //Answer to this.

   row=[];
   Logger.log(row);
   pushed();
  }
}

function pushed(){Logger.log("pushed");}

But still not sure why it would log twice all the time though instead of just once since it was calling in same Row number each time. Anyways, not my concern anymore.

Upvotes: 0

Justin L.
Justin L.

Reputation: 387

nextRow never gets updated in your for loop, does it? So you keep altering the same row in the spreadsheet.

Upvotes: 1

Related Questions