https://www.pbainbridge.co.uk/2023/09/autofill-google-sheet-formula-each-day.html

Autofill Google Sheet Formula each day.gs

/** 
 * Function to check if date in spreadsheet is less than todays date.
 * If so then autofill formulas down a row.
 * Run via daily Trigger.
 * 
 * @OnlyCurrentDoc 
*/

function autofill() {

  // get spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get 'Main' sheet
  var mainSheet = ss.getSheetByName('Main');

  // get last row
  var lastRow = mainSheet.getLastRow();
  console.log("lastRow is: " + lastRow);


  // get last date value and format
  var currentDate = mainSheet.getRange(lastRow, 1).getValue();
  var tidyCurrentDate = currentDate.valueOf();
  console.log("tidyCurrentDate is: " + tidyCurrentDate);


  // get todays date and format
  var todaysDate = new Date();
  var tidyTodaysDate = todaysDate.valueOf();
  console.log("tidyTodaysDate is: " + tidyTodaysDate);


  // check if date is less than (in the past) today
  if (tidyCurrentDate < tidyTodaysDate) {

    console.log("Spreadsheet date is in the past");

    // get last column
    var lastCol = mainSheet.getLastColumn();
    console.log("lastCol is: " + lastCol);


    // 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);


    // refresh the Google Sheet
    SpreadsheetApp.flush();


  } else {

    console.log("Spreadsheet date is not in the past");

  };


}