Copy formula and paste to specific range and then paste again as values

Question

A total newbie here but eager to learn.

We have a Google Sheet file that contains a formula that we want to copy in specific cells. The formula we are copying varies from 3 or more columns (meaning the script should be dynamic).

Please see test file - https://docs.google.com/spreadsheets/d/1ayMoMFPllDk5XbIc8cYrnj9K0eFz461JrvdtzW3F5BI/edit?usp=sharing

In column R to W (there are some sheets with only 3 up to 10 columns with this formula), we need to copy the formula to today's date + 1 (so today is March 31, we need to copy to formula up to April 01).

After we copy the formula, we then want to paste them as values but excluding April 01 so once we run the script again, we will have the formula - reason we want to do this is that sheet is very slow to response when the formula we used is calculating the result (formula is array index / match).

We imagine the logic as if todays date is the same as in column B, then insert the formula into that row but only columns R-V (but this part needs to by dynamic to accommodate other number of columns)

recorded macro we tried but is not near to what we want.

function CopyandPasteasValues() {

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getCurrentCell().offset(1, 0, 7, 5).activate();
  spreadsheet.getCurrentCell().offset(-1, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getCurrentCell().offset(0, 0, 6, 5).activate();
  spreadsheet.getActiveRange().copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

So you want to copy the formulas in the last row of R and all the columns to the right of R down to the last row of data based on column B? Once that's done replace all the newly created with values apart from the last row? — norie

norie norie · Accepted Answer · 2021-04-01T01:36:45

Try this, is a bit cumbersome but the lack of resize/offset makes things a bit harder.

function fillFormulasDown() {
  const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const lastCol = ws.getLastColumn();
  const lastRow = ws.getLastRow();
  const lastRowInR = ws.getRange("R1:R").getValues().filter(String).length;
  const rngFormulas = ws.getRange(lastRowInR,18, 1, lastCol-18+1);
  const rngDst = ws.getRange(lastRowInR,18, lastRow-lastRowInR+1, lastCol-18+1);
  const rngValues = ws.getRange(lastRowInR,18, lastRow-lastRowInR, lastCol-18+1);

  rngFormulas.copyTo(rngDst);

  rngValues.setValues(rngValues.getValues());

}

Copy formula and paste to specific range and then paste again as values

1 Answers