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.
 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
 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 forumfollow the blog or follow me on twitter to ensure you get updates when they are available. 

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.






Comments