Moving Average Custom Function for Google Spreadsheets

Moving Averages can be calculated in Google Spreadsheets using standard functions, however the formulas can get pretty complex when dealing with different durations, and it also requires the formula to be included in each field. The first time I tried to solve this by writing a custom function that would calculate the moving average per cell, however I quickly ran into an error stating that I was running too many scripts per second for Google’s liking.

I am working with rows of time series data, so I really want to add a new column with a moving average in it. It would also be nice to show the average of the available data if the full set isn’t available for convenience. To accomplish both of these I created a function that can be placed in the header and returns an array of results, including the header itself, of a moving average of the value from another column.

Custom function via Google Script

Open Google Scripts by selecting Tools  > Script editor... to open the browser based IDE. A sample file will open by default, you can look it over or just remove all of the contents. Once the file is cleared, enter the following JavaScript to define a custom function named MOVING_AVERAGE.

function MOVING_AVERAGE(column, days, dateColumn){
	// defaults
	days = ( typeof days == 'undefined' )? 30 : days;
	dateColumn = ( typeof dateColumn == 'undefined' )? 'A' : dateColumn;
	var now = (new Date()).getTime();
	
	var sheet = SpreadsheetApp.getActiveSpreadsheet();
	var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows()-1;
	var dates = sheet.getRange(dateColumn+"2:"+dateColumn).getValues();
	var values = sheet.getRange(column+"2:"+column).getValues();
	for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
	var title = sheet.getRange(column+"1").getValue();
	var results = [ days + " days " + title ];
	for ( var rowIndex = 0; rowIndex < lastRow; rowIndex++ ){
		// we are after "today" 
		var inTheFuture = (now+3600 <= (new Date(dates[rowIndex])).getTime());
		
		if (inTheFuture){
			results.push(0);
		} else {			
			var lookback = ( rowIndex < days )? rowIndex : days-1;
			var startRow = rowIndex - lookback;
			
			var total = 0;
			for ( var i=startRow; i<=rowIndex; i++ ){
				total += parseFloat( values[i] );
			}
			
			results.push(parseFloat(total/((rowIndex-startRow)+1)));
		}
	}
	
	return results;
}

Use the custom function in your spreadsheet

You can now use this function by adding the following into the formula field of a column header. Keep in mind that this won’t update on demand so you will need to remove/re-add the formula to recalculate.

=MOVING_AVERAGE( "COLUMN", DAYS_INT )

Or if your date column isn’t “A”, then you can specify it like so.

=MOVING_AVERAGE( "COLUMN_DATA", DAYS_INT, "COLUMN_DATE" )