Ufdjrw

This is probably an ignorant mistake on my part, but I am trying to assign different values from different column to an array to put in a master sheet. I would like the last column to be equal the the sheet name where the data came from to track it back.

I feel I am close, but when I get to the last column which I want to be the sheet name I get the following error: Cannot convert Array to Object[][]. (line 19, file "combineTOmaster")

This is my code so far. I know the issue is the other column are array[[][]] and the last one is array [][] but I don't know how to fix it. Any ideas?

function getColValues(label,sheetName) 

 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
 var colIndex = getColumnIndex(label, sheetName);
 var numRows = ss.getLastRow() -1;
 if (colIndex > 0 ) 
 var colValues = ss.getRange(2, colIndex, numRows, 1).getValues();
 
 else if (colIndex = "orginalsheet") 
 var colValues = [];
 var array = [];
 for (var i = 0; i < numRows-1;i++) 
 array[i] = sheetName;
 
 colValues = array;
 
 else 
 var colValues = [];
 
 Logger.log(colValues);
 return colValues;
 

Thank you

Lets break your function down -

// you're passing column header and sheet name, OK
function getColValues(label, sheetName) 
 // getting sheet, OK
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

 // getting column index, OK
 var colIndex = getColumnIndex(label, sheetName);

 // getting number of rows to collect, OK
 var numRows = ss.getLastRow() - 1;

 // at this point, i assume, variable colIndex is a number, because it should be
 // if label column exists, colIndex will be greater than 0, OK
 if (colIndex > 0) 
 // get values of that column, OK
 var colValues = ss.getRange(2, colIndex, numRows, 1).getValues();
 // colValues will be like this, at this point-
 // [ [value], [value], [value] ... ]

 else if ((colIndex = 'orginalsheet')) 
 // NOT OK, = sign assigns 'orginalsheet' to colIndex
 // do you wanna assign or compare?
 // to compare, use == sign
 // besides why are you assigning a string value to colIndex ?
 // colIndex should be a number, in my understanding

 // now, what do you wanna do here ?
 var colValues = [];
 var array = [];
 for (var i = 0; i < numRows - 1; i++) 
 array[i] = sheetName;
 
 colValues = array;
 // at his point, colValues is -
 // [sheetName, sheetName, sheetName...]

 // is this correct colValues ?
 // i guess not
 // i think you're trying to use sheetName as a column value for every row
 // if that is the case, do this
 var colValues = [];
 for (var i = 0; i < numRows; i++) colValues.push([sheetName]);
 // now colValues is -
 // [ [sheetName], [sheetName], [sheetName].... ]
 // which you can insert in a sheet as values of a column
 else 
 var colValues = [];
 
 Logger.log(colValues);
 return colValues;

@ra89fi Thank you so much. You lead me to the answer. So mess up 1 was using = and not ==. Mess up two was using colIndex instead of label. Mess up three was not using push. Is there a good resource on push? Thank you again!!

Below is the final code that worked for me.

function getColValues(label,sheetName) 

 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
 var colIndex = getColumnIndex(label, sheetName);
 var numRows = ss.getLastRow() -1;
 if (colIndex > 0 ) 
 var colValues = ss.getRange(2, colIndex, numRows, 1).getValues();
 
 else if (label == "orginalsheet") 
 var colValues = [];
 for (var i = 0; i < numRows;i++) 
 colValues.push([sheetName]);
 
 
 else 
 var colValues = [];
 
 return colValues;