CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsindexof
A.E.
A.E.

Reputation: 5

How dynamically to put values in a column?

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!

Upvotes: 0

Views: 95

Answers (1)

Dustin Michels
Dustin Michels

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

Related Questions