Filling ranges in Google Sheets

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

rangeFill (theRange  , thePropertyName , theValue or a function to generate the value , optional range for the header row);

Examples

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

rangeFill (headerRange , 'backgrounds' , 'gray');

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

rangeFill (dataRange, 'backgrounds', function (item) {
    return item.value < 500  ? 'red' : item.propertyValue; 
});

set odd numbered rows to bold

rangeFill (dataRange, 'fontWeights', function (item) {
    return item.rowIndex % 2  ? 'bold' : 'normal'; 
	});

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

rangeFill (dataRange, 'fontColors', function (item) {
    var max = Math.max.apply (null,item.rowValues.filter(function(d) {
      return !isNaN(new Number(d));
    }));
    return item.value === max ? 'green' : 'gray'; 
	});

replace values with error message if 5000 or more

rangeFill (dataRange, 'values', function (item) {
    return item.value < 5000  ? item.value : 'too big'; 
	});

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

rangeFill ( flightRange , 'values' , function () {
    return Math.floor(Math.random() * 10000);
	});

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.

rangeFill ( flightRange , 'values' , function (item) {
    return item.values[0][item.columnIndex] === 'double it' && item.rowIndex ? 2 * item.value : item.value;
	});

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

rangeFill ( flightRange , 'values' , function (item) {
    return item.columnName === 'double it' && item.rowIndex ? 2 * item.value : item.value;
	});

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.

var dataOnly = dataRange.offset(1,0,dataRange.getNumRows() -1);
var headersOnly = dataRange.offset (0,0,1);

rangeFill ( dataOnly , 'values' , function (item) {
    return item.columnName === 'double it' ? 2 * item.value : item.value;
	} , headersOnly);

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

rangeFill ( dataOnly , 'values' , function (item) {
    return item.is('double it') ? 2 * item.value : item.value;
	} , headersOnly);

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.

The code
rangeFill is in the cUseful library under the SheetUtils namespace. You can get to it like this

cUseful.SheetUtils.rangeFill (
  theRange  , thePropertyName , theValue or a function to generate the value , optional range for the header row
  );

Here’s the key for the cUseful library, and it’s also on github, or below.

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j

Here’s the function code.

/**
* given a range and a property name, fill it with a value
* @param {Range} range the range
* @param {string} propertyName the property name
* @param {*|function} fillValue the value to fill with, or function to create a value
* @param {Range} [headerRange=] an optional range for the headers, default is first data row
* @return {range} for chaining
*/
function rangeFill (range , propertyName, fillValue, headerRange) {
  
  // camel case up property name
  var name = propertyName.slice(0,1).toUpperCase() + propertyName.slice(1);
  if (typeof range['get'+name] !== typeof range['set'+name] || 
      typeof range['set'+name] !== 'function') {
    throw new Error (name + ' should be a property of a range with a getter and setter');
  }
                     
  // we'll always need the values to pass to a function, and also get the current properties
  var values = range.getValues();
  
  // set up default headers
  columnNames = headerRange ? headerRange.getValues()[0] : values[0]; 
  if (columnNames.length != values[0].length) {
    throw new Error ('headers are length ' + columnNames.length + 
      ' but should be ' + values[0].length);
  }
  // these are the properties that will be set                 
  var properties =  name === 'Values' ? values : range['get'+name]();
  
  // iterate
  return range['set'+name](
    values.map(function(row,rowIndex) {
      return row.map(function(cell,colIndex) {
        return typeof fillValue === 'function' ? 
          fillValue ({
            value:cell,
            propertyValue:properties[rowIndex][colIndex],
            columnIndex:colIndex, 
            rowValues:row,
            rowIndex:rowIndex,
            propertyValues:properties,
            values:values,
            range:range,
            propertyName:propertyName,
            columnNames:columnNames,
            columnName:columnNames[colIndex],
            is:function(n) { return columnNames[colIndex] === n; }
          }) : fillValue;
      });
    })
  );
  }

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.