setValues cannot convert array to object[][]

Question

i've been coding this on the google apps script for a sheet:

  function basePesa(){
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("aux");
  var unique = sheet.getRange("C1").setFormula("=unique(A:A)");
 var Avals = sheet.getRange("C1:C").getValues();
 var Alast = Avals.filter(String).length;
 var transp = sheet.getDataRange().getValues();

 var ss = SpreadsheetApp.openById("14Y3xiAa9kdoK_YO_tAVN-YWC9RE1EANV5wm8Ez1sa1o");
 var base =ss.getSheetByName("Base PESA");
 var values = base.getDataRange().getValues();
 var newdata = new Array(values.length);
 var y = 0;

//  Browser.msgBox(transp.length);

 for(var i=0;i<Alast;i++){


 var tra = Avals[i][0];

 for(var x =1; x<values.length;x++){

 if(values[x][18] == tra){
  newdata[y] = new Array(values[0].length);

 for(var p=0; p<values[0].length;p++)
 newdata[y][p] = values[x][p];   

 y++;
 }

 }   

  }
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Bco de Dados").getRange("A2:AZ"+(y+1)+"").setValues(newdata);
// Browser.msgBox(newdata);


}

It seems that I can't get to write the array into a range. I've checked if the ranges match as well as i'm certain that this array is a 2D one. Any instructions?

Thanks in advance!

Can you give us some access to a copy of this sheet, for testing purpose ? — Pierre-Marie Richard, Apr 11 '17 at 13:17
I tried that before, I guess I can't since i'm in a work group and only those in it can access via sharing... :( I'll try to recreate it on another account, hang on — Lucas Duarte Almeida, Apr 11 '17 at 13:45
Btw, give it a try: https://drive.google.com/open?id=1DOSPQS_4ZbEvQrFuNHeccicOzj2DDdz9Xxgv1OLB0WE — Lucas Duarte Almeida, Apr 11 '17 at 13:46

score 7 · Answer 1 · answered Sep 19 '17 at 23:54

7

Array 1: [ A , B , C ] <---- Compatible for 1 Row, 3 Columns

Array 2: [ [A] , [B] , [C] ] <--- Compatible for 3 Rows, 1 Column

Array 3: [ [ A , B , C ] , [ A , B , C ] , [ A , B , C ] ] <--- Compatible for 3 Rows , 3 Columns**

**Important to note that the width of each smaller array within the larger array (in this case 3), must be the same for each of the smaller arrays.

Click Here for a Visual Example (Image)

answered Sep 19 '17 at 23:54

user8637437

71
1
2

Now I finally get my old issues with setValues, the 3 array pic examples are fantastic! Many thanks – user3257693 Oct 01 '17 at 12:44
@user8637437: in the image the examples of Array 1 and Array 2 are inverted. – Michele Pisani Sep 04 '19 at 08:40

score 0 · Accepted Answer · answered Apr 11 '17 at 14:27

It appear that the creation of the newdata array was the issue. Here is a sample which is working (I put the bad code in comment):

function basePesa(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("aux");
  var unique = sheet.getRange("C1").setFormula("=unique(A:A)");
  var Avals = sheet.getRange("C1:C").getValues();
  var Alast = Avals.filter(String).length;
  var transp = sheet.getDataRange().getValues();

  var ss = SpreadsheetApp.openById("14Y3xiAa9kdoK_YO_tAVN-YWC9RE1EANV5wm8Ez1sa1o");
  var base =ss.getSheetByName("Base PESA");
  var values = base.getDataRange().getValues();
  var newdata = [];//new Array(values.length);
  var y = 0;

  //  Browser.msgBox(transp.length);

  for(var i=0;i<Alast;i++){


    var tra = Avals[i][0];

    for(var x =1; x<values.length;x++){

      if(values[x][18] == tra){
        //newdata[y] = new Array(values[0].length);

        var test = [];

        for(var p=0; p<values[0].length;p++)
        {
          test.push(values[x][p]);
          //newdata[y][p] = values[x][p].toString(); 
        }

        newdata.push(test);

        y++;
      }

    }   

  }

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Bco de Dados").getRange("A2:AZ"+(y+1)+"").setValues(newdata);

  // Browser.msgBox(newdata);

}

Man, thanks so much, you rock! Even though i don't understand why that old code didn't work, yours is certainly welcome! — Lucas Duarte Almeida, Apr 11 '17 at 14:36
Take a look at this [SO question](http://stackoverflow.com/questions/931872/what-s-the-difference-between-array-and-while-declaring-a-javascript-ar), it may have a explaination. — Pierre-Marie Richard, Apr 11 '17 at 14:40

score 0 · Answer 3 · answered Oct 22 '19 at 13:31

Based on similar problem I had I guess that in the last row where you have

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Bco de Dados").getRange("A2:AZ"+(y+1)+"").setValues(newdata);

instead of

.setValues(newdata);

you should use

.setValues([newdata]);

Note the square brackets! This solution is basically the same (but shorter) as

...
var newdata = []
var test = []
...
test.push(values[x][p]
...
newdata.push(test)
...
....setValues(newdata)

setValues cannot convert array to object[][]

3 Answers3