Autofill Google Sheet Formula each day

The following Google Apps Script is designed to check a Google Sheet once per day and if the date is in the past it Autofills another row with the existing formulas used across the columns. This post is a variation of the Autofill Google Sheet Formula one.

Autofill Google Sheet formulas each day

The Code

You will need to create a 'Time-driven' trigger once you have the code sorted in order to run this on a schedule. For my purpose I used a 'Day timer' of '11pm to midnight' so that it ran at the very end of the day when collecting metrics.

Part of the code involves getting the date (last row) from the Google Sheet and the date when the code is running. We then use 'valueOf()' to get the number of milliseconds since midnight January 1, 1970 UTC so they can be compared:

var currentDate = mainSheet.getRange(lastRow, 1).getValue();
var tidyCurrentDate = currentDate.valueOf();
var tidyTodaysDate = todaysDate.valueOf();

Once we check if the date in the Sheet is in the past (less than the current date) we can get our ranges with the formulas that need Autofilling down:

// get range with formulas to replicate
var sourceRange = mainSheet.getRange(lastRow, 1, 1, lastCol);

// get range to autofill, including the row below
var destinationRange = mainSheet.getRange(lastRow, 1, 2, lastCol);

// run autofill method to copy down formulas
sourceRange.autoFill(destinationRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

Download

Autofill Google Sheet Formula each day download (please use 'File' > 'Make a copy' for your own version).