Adding some AutoFill magic to your Google Sheets Apps Script projects

Google Sheets users can already use the magic of AutoFill to expand data automatically detecting a series of numbers, letters or dates. This feature is particularly powerful even when your series includes text or repeating dates. Another feature of AutoFill is being able to expand multiple columns with different series data:

AutoFill Example
Google Sheets AutoFill Examples

In a recent Google Sheets project I encountered a problem where my script was inconsistently adding formulas to new rows or data … the solution was to programmatically use AutoFill in my script project. In this post I’ll highlight how you can also use AutoFill in your projects.

Background

If you are a long time Apps Script user or just coming to this product you may have missed the announcement in October 2017 for the additional methods:

The autoFill() method essentially allows you to replicate at screen user behaviour or selecting and expanding a data series. Copied below is the code example from the documentation:

var sheet = SpreadsheetApp.getActiveSheet();

// Has values [1, 2, 3, 4].
var sourceRange = sheet.getRange("A1:A4");
// The range to fill with values.
var destination = sheet.getRange("A1:A20");

// Inserts new values in A5:A20, continuing the pattern expressed in A1:A4
sourceRange.autoFill(destination, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

Below are some example use cases illustrating the benefits of using autoFill().

Use Case 1: No code AutoFill with the Macro Recorder

A quick way of generating some AutoFill code you can reuse in your projects is with the Macro Recorder. The animation below shows how you can record a macro which will generate some AutoFill code you can use in other projects, extend or daisy chain with other recorded macros:

Recording an AutoFill with the Macro Recorder
Recording an AutoFill with the Macro Recorder

Use Case 2: Expanding formulas to inserted to new rows of data

In a recent project I’ve been tracking Covid data for Scotland in a Google Sheet (see source Google Sheet). The dataset I’m using contains the daily number of cumulative cases per Scottish region. As this is a personal project rather than selectively extracting new rows from the source .csv file I’ve just been reading and writing the entire published .csv file into an existing sheet. As I need a national daily case total, which is not included in the data, each time the script runs I was adding a SUM formula to the last row:

/**
 * First method of getting all data and writing to sheet and adding a row SUM formula to the last row
 */
function getCurrentCovid19CasesOld(){
  const url = "https://raw.githubusercontent.com/DataScienceScotland/COVID-19-Management-Information/master/COVID19%20-%20Daily%20Management%20Information%20-%20Scottish%20Health%20Boards%20-%20Cumulative%20cases.csv";
  const csv = UrlFetchApp.fetch(url).getContentText();
  const data = Utilities.parseCsv(csv);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DashboardCases");
  // Write data starting in row 2 col 2
  sheet.getRange(`B2:P${data.length+1}`).setValues(data);
 
  // Add SUM function to column Q
  const lastRow = sheet.getLastRow(); // get last row number  
  sheet.getRange(`Q${lastRow}`).setValue(`=SUM(C${lastRow}:P${lastRow})`);
}

Using a daily trigger to run this function was initially working well, then in July I noticed the SUM formula was missing from some rows. Looking at the repository commit frequency it became apparent that new case data was now only being published on weekdays. This meant that when the trigger ran on Monday it would have 3 rows of new data but would only add one row sum. The solution I’ve used is using the AutoFill to select the the first row SUM and copy all the other rows:

/**
 * AutoFill solution for getting all data and writing to sheet and adding a row SUM 
 */
function getCurrentCovid19CasesAutofill(){
  const url = "https://raw.githubusercontent.com/DataScienceScotland/COVID-19-Management-Information/master/COVID19%20-%20Daily%20Management%20Information%20-%20Scottish%20Health%20Boards%20-%20Cumulative%20cases.csv";
  const csv = UrlFetchApp.fetch(url).getContentText();
  const data = Utilities.parseCsv(csv);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DashboardCases");
  // Write data starting in row 2 col 2
  sheet.getRange(`B2:P${data.length+1}`).setValues(data);
 
  // Add SUM function to column Q
  const source = sheet.getRange("Q3"); // Cell with existing SUM row
  const dest = sheet.getRange("Q3:Q"); // The range to fill with values
  // Inserts new values in Q3:Q, continuing the pattern in Q3
  source.autoFill(dest, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
}

Summary

Hopefully this post has shown how you can benefit from using autoFill() in your script projects. If you are already using autoFill() in your script projects and have some interesting use cases please feel free to share in the post comments.