How dynamically to put values in a column?

Question

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.

ENTER IMAGE DESCRIPTION HERE!

Thanks but, I still couldn't get it to work for me. I'm extremely new to coding. I tried to determine what I needed from that. I still get only one sheet name repeating all the way down. I created a new function referencing the old one. see a pic above. — A.E., Jul 19 '19 at 17:34

Dustin Michels · Answer 1 · 2019-07-19T18:54:45.777

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.

How dynamically to put values in a column?

1 Answers1