Reputation: 5
I'm trying to put the name of certain sheets in a column (excluding the names of other sheets I don't want). I have created the array but, it does not appear. I want the information to be placed in A3:A105
.
I have tried to set the value of the column using indexOf
.
function sheetNames() {
var out = [];
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var exclude = ["About Me", "Contractors", "All Group Bids", "Bid Summary"];
var bidsheet = spreadsheet.getSheetByName("Bid Summary");
var column = bidsheet.getRange(3, 1, 105).getValue();
for (i = 0; i < sheets.length; i++) {
if (exclude.indexOf(sheets[i].getName()) === -1)
{column.setValue(out.push(sheets[i].getName()))};
return out;
}
}
//Nothing appears in the column.
Upvotes: 0
Views: 95
Reputation: 3246
At a glance, I think var column = bidsheet.getRange(3, 1, 105).getValue();
could be the problem.
Try taking out the getValue()
part, so column
is a Range
object. Then you can call setValue
or perhaps setValues
on that range a little later on.
Note:
SetValue
takes a single value, and should be used on a range made up of a single cell. Eg:
var cell = sheet.getRange("B2");
cell.setValue(100);
SetValues
takes a two-dimensional array of values, and should set used on a multi-cell array. Eg:
var values = [
[ "2.000", "1,000,000", "$2.99" ]
];
var range = sheet.getRange("B2:D2");
range.setValues(values);
Rather than calling setValue
each time you iterate through that loop, a better approach may be to use that loop to build up an array of sheet names and then make a single call to setValues
to write that data to your selected range.
Upvotes: 1