Google Apps Script: how to copy paste ranges based on formulas?

Question

I have a model in Google Sheets that is set up with one column per day. It contains both actuals and forecasts, and every day I need to roll forward formulas to replace forecasts with actuals. I can't roll forward the whole column, only a segment of it (there are reference numbers above and below that shouldn't be changed).

I have tried to write a script to do this for me every day, but I don't know how to make getRange reference a dynamic range. This is my attempt:

function rollColumn() {
  var ss2 = SpreadsheetApp.openById('<ID redacted>');
  ss2.getRange("=index(Model!$7:$7,,match(today()-2,Model!$4:$4,0)):index(Model!$168:$168,,match(today()-2,Model!$4:$4,0))").copyTo(ss2.getRange("=index(Model!$7:$7,,match(today()-1,Model!$4:$4,0)):index(Model!$168:$168,,match(today()-1,Model!$4:$4,0))"))
};

The INDEX formulas work insofar as they reference the relevant ranges (I have tested them in the spreadsheet). But clearly getRange doesn't accept formulas as an input. It also seems that Google Sheets doesn't allow for a named range to be created with formulas (which is how I would solve this in Excel).

Can someone help me recreate this functionality with GAS?

This is the closest existing question I've found on Stack Overflow, but I haven't been able to make it work: Google Apps Script performing Index & Match function between two separate Google Sheets

Thank you!

Answer 1

You should add {contentsOnly:false} parameter to your code. something like this:

TemplateSheet.getRange("S2:T2").copyTo(DestSheet.getRange("S2:T"+LRow2+""), {contentsOnly:false});

Answer 2

Getting a date from column's title, then pasting formulas to the row to the right:

// note: we assume that sheet is disposed as in the following document: https://docs.google.com/spreadsheets/d/1BU2rhAZGOLYgzgSAdEz4fJkxEcPRpwl_TZ1SR5F0y08/edit?ts=5a32fcc5#gid=0
function find_3formulas() {

  var sheet = SpreadsheetApp.getActiveSheet(),
      leftTitle, // this variable will stay unused because we do not need a vertical index
      topTitle = todayMinus_xDays(2),
      topTitlesRange = sheet.getRange("G3:T3"),
      leftTitlesRange = sheet.getRange("A4:A8"); // this range will stay unused.

  var coor = findCoordinates(leftTitlesRange, leftTitle, topTitlesRange, topTitle);

  if (coor.row == null || coor.column == null) {
    sheet.getRange("M12:M14").setFormula('="NULL: please check logs"'); 
    return;
  }

  var rowAxis = 4 + coor.row;
  var colAxis = 8 + coor.column;
  var fromRange = sheet.getRange(rowAxis, colAxis, 3, 1);
  var toRange = sheet.getRange(rowAxis, colAxis + 1, 3, 1);

  Logger.log(fromRange.getA1Notation())
  Logger.log(toRange.getA1Notation());

  var threeFormulas = fromRange.getFormulas();

  toRange.setFormulas(threeFormulas)
}

// unused in current script!
function findCoordinates(leftTitlesRange, leftTitle, topTitlesRange, topTitle) {

  var formattedDate, 
      row = 0, 
      column = 0;

  if (leftTitle) {
    row = findRow(leftTitlesRange, leftTitle);
  }

  if (topTitle) {
    column = findColumn(topTitlesRange, topTitle);
  }

  var array = {row:row, column:column}

  return array;
}

// unused in current script!
function findRow(range, valueToSearch) {

  var colRows = range.getValues();
  for (i = 0; i < colRows.length; i++) {
    if (valueToSearch == colRows[i][0]) {return i;}
  } 
  // however, if found nothing: 
  Logger.log("the value " + valueToSearch + " could not be found in row titles");
  return null;
}

// assumes that column titles are dates, therefore of type object.
function findColumn(range, valueToSearch) {

  var colTitles = range.getValues();
  for (i = 0; i < colTitles[0].length; i++) { 
    if (typeof colTitles[0][i] == "object") {
      formattedDate = Utilities.formatDate(colTitles[0][i], "GMT", "yyyy-MM-dd")
    };
    if (valueToSearch === formattedDate) {return i;}
  }
  // however, if found nothing:
  Logger.log("today's date, " + valueToSearch + ", could not be found in column titles");
  return null;
}

// substracts 2 days from today, then returns the result in string format.
function todayMinus_xDays(x) {
  var d = new Date();
  d = new Date(d - x * 24 * 60 * 60 * 1000);
  d = Utilities.formatDate(d, "GMT", "yyyy-MM-dd");
  return d;
}