How to Set Values with Array and match length

0
votes

I'm getting a list of data from an external API and when I'm doing the setValues() it deliver an error:

The number of rows in the data does not match the number of rows in the range. The data has 1 but the range has 365. (line 59, file "Extra_queries")

I've tried deleting the length of the columns on set values and also changing the order.

I think the array is not well built. I think this array is being set for columns and not for rows? Range expected is on Row 3, column 16 until the end of data.

function getComments(doc){
  var basicMetadata = getDeliveryBasicMetadata()
  var ordersId = doc.getRange("C3:C").getValues();
  var last_row = ordersId.filter(String).length;
  var range = doc.getRange(3,16,last_row,1);
  var comments = [];
  for (var i = 3; i <= last_row; ++i){
    var orderIndex = i-3
    var comment = lookForComments(ordersId[orderIndex],basicMetadata);
    comments.push(comment);
  }
  var setValuesLenght = last_row - 2 //as we start on row 3 to align Last_row with lenght of the comments Array. Now both 365
  doc.getRange(3,16,setValuesLenght,1).setValues([comments]);
}

Here you can see the array that is built with comments.(lots of "" because this is a test)

enter image description here

2
google-apps-script
Which line is throwing this error? - ross

2 Answers

2
votes

Issue:

Your array is 1-dimensional, not 2-dimensional like you're expecting. Since this is the case, it will only ever be 1 row long.

Solution:

doc.getRange(3,16,1,comments.length).setValues([comments]);
0
votes

I've found the solution doing the loop explained on: 1D Array to fill a spreadsheet column

Thank you ross for your help!

See working code:

  function getComments(doc){
  var basicMetadata = getDeliveryBasicMetadata()
  var ordersId = doc.getRange("C3:C").getValues();
  var last_row = ordersId.filter(String).length;
  var comments =[]
  for each (order in ordersId){
    comments.push(lookForComments(order,basicMetadata));
  } 
  writeArrayToColumn(comments, doc)
}


function writeArrayToColumn(array, mainSheet) {
  var newArray = array.map(function (el) {
    return [el];
  });
  mainSheet.getRange(3,16,newArray.length,1).setValues(newArray);
}