SetValues cannot convert array to object [] []
I have coded this in google apps script for 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 cannot write the array to a range. I have checked if the ranges are correct and I am pretty sure this array is two dimensional. Any instructions?
Thanks in advance!
+3
source to share
2 answers
It looks like a problem with creating the newdata array. Here's an example that works (I put bad code in the 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);
}
0
source to share
Array 1: [A, B, C] <---- Compatibility for 1 row, 3 columns
Array 2: [[A], [B], [C]] <--- Compatibility for 3 rows, 1 column
Array 3: [[A, B, C], [A, B, C], [A, B, C]] <--- Compatibility for 3 rows, 3 columns **
** It's important to note that the width of each smaller array in the larger array (3 in this case) must be the same for each of the smaller arrays.
+3
source to share