Sometimes you need to fill a range in a sheet, either with a single value, or with some calculated value. It’s a pretty straightforward pattern, but if you are an Excel user, you’ll be used to using range.fill or simply setting an entire range to a value in a single statement.

This doesn’t really exist in Apps Script, but this small function should make it easier. More importantly though, since you can pass functions to other functions in JavaScript this approach provides a really powerful conditional properties capability.

How to use

Examples

set the background color of each cell in a range to gray

set the background color of each cell in a range to red if a cell value < 500, otherwise leave as current.

set odd numbered rows to bold

set the font color of the largest numeric value in each row to green, and the rest to gray

replace values with error message if 5000 or more

fill a range with random integers between 0 and 10,000

I could go on, but hopefully you get the picture. The simple case of just filling a value or a property is useful, but using a function to play around with properties or values is even better.

Using column Names
Abstracting the data using column names rather than specific column indexes is usually better as it will still work if the positions of columns in sheets change.

You could access the current column name like this, where a the column name is retrieved from the first column of data (assuming your range includes a header) and no change is made if its the header row.

but rangeFill also provides an item.columnName property to give the current column name, as well as item.columnNames to give all the column names, so the above can be written as

but it’s still an unnecessary pass through the header row, and in any case the data range may not even contain the header range. rangeFill takes an extra optional argument – headerRange – to separate out the data from the header, as in the example below, so now this can be written as.

finally there is a convenience function .is() so it can be written as

If you don’t pass a header range, then these properties are still available since rangeFill by default uses the first row as the header row. If you do use a single range that contains headers, remember to give special treatment to the first row by testing item.rowIndex === 0.

If your call passes a function, then the object that is passed to it for each cell (item in the examples) has these properties.

 property  contains
 value  the value in the cell
 propertyValue  the requested property value for this cell
 columnIndex  the column index for this cell starting at 0
 rowIndex  the row index for this row starting at 0
 propertyValues  a two dimensional array with all the property values of the range
 values  a two dimensional array with all the values of the range
 range  the range that was passed
 propertyName  the property name that was requested
 columnNames  an array with all the column names – if headerRange not passed, then it uses the first data row
 columnName  the name of the current column
 is  a function that returns true or false to check whether the current column name matches eg item.is(‘total’)
The code
rangeFill is in the cUseful library under the SheetUtils namespace. You can get to it like this
Here’s the key for the cUseful library, and it’s also on github, or below.

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j

Here’s the function code.

For more like this see Google Apps Scripts Snippets
For more like this, see Google Apps Scripts snippets. Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.