How do I select a range of cells in without giving the range a name?

Question

I would like to select cells C29:Z50 as a variable using apps script. I'd prefer not to have to create a named range (but I can if needed).

My goal is to create some custom formatting beyond colors that can use built in functionality. So, if cell B27 = "Transactions" then format C29:Z50 as a number, else format Z29:Z50 as dollar currency.

So I don't need the values of the cells, just the group of cells as a variable that I can apply if logic to.

I was reading the documentation here.

getDataRange()

Returns a Range corresponding to the dimensions in which data is present. This is functionally equivalent to creating a Range bounded by A1 and (Range.getLastColumn(), Range.getLastRow()).

The example in the documentation grabs the entire tab but I just want a subset of the sheet in question.

I tried:

var currency_range = ss.getDataRange(C29, Z50); //"Reference Error. "C29" is not defined."

var currency_range = ss.getDataRange(C29:Z50); // "Missing ) argument list"

How can I "get" the range of cells so that I can later edit their formatting based on if conditional?

AJPerez AJPerez · Accepted Answer · 2015-05-20T14:23:21

You can use Spreadsheet.getRange(). For example, this should work:

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("...");
var currencyRange = ss.getRange("C29:Z250");

Well, actually I'm using this Sheet.getRange() method instead, but they should be the same in your case. It offers more ways to declare the desired range, such as getRange(row, column, numRows, numColumns), which could be useful for you too.

How do I select a range of cells in without giving the range a name?

1 Answers