Copy and Pasting a Moving Range in Google Sheets

I'm very much still learning the basics of script writing, but I have a rather specific question that I've not been able to find a solution for - perhaps because I might be phrasing it wrong.

Essentially, In sheet MS, I have a range of data (A2:O23) with formulas that I want to stay fixed (as in, always reference the same range in a different sheet).

My aim is, at the run of a script, that this block of formulas will copy and paste from the next empty row (in the same columns [A:O]) and that the previous block (A2:O23) will copy and paste over itself as values.

Then, each time the script runs, it does the same thing, copying the latest block of 22 rows beneath itself and pasting itself as values. Essentially having the effect of building a long list of data with the latest (read lowest) range being the formulas and all above being values.

For example, the results of the first time it runs should be that, A24:O45now show the same formula and that A2:O23 are showing as values only. The next time, A24:O45 would be frozen as values and A46:O67 are now the formulas.

I'm not sure if this requires the use of loops or there is a different way to achieve the effect, but the former is something I've not used and the latter is still a mystery to me.

I realise that this might not be especially clear, but I'm happy to field any questions and greatly appreciate any efforts made to help.

Thanks

Solution

If I understand you correctly, you want to:

Copy the last 22 rows of your sheet (columns A to O) to the next 22 rows (formulas included).
Copy/paste to the original range as values only.

If the above is correct, then you can copy and run the following code to the script bound to your spreadsheet (see inline comments for detail on what the code is doing, step by step):

function copyPasteValues() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("MS"); // Name of your sheet (change if that's necessary)
  var lastRow = sheet.getLastRow();
  var firstCol = 1; // First column to copy
  var numCols = 15; // Number of columns to copy
  var numRows = 22; // Number of rows to copy
  if (lastRow > numRows) { // Check if the sheet has at least 23 rows with content (otherwise it would give error when copying the range)
    var originRange = sheet.getRange(lastRow - numRows + 1, firstCol, numRows, numCols);
    var destRange = sheet.getRange(lastRow + 1, firstCol, numRows, numCols);
    originRange.copyTo(destRange); // Copying with formulas to next 22 rows
    originRange.copyTo(originRange, {contentsOnly:true}); // Copying only values to same range
  }
}

The main method to notice here is copyTo, which can be used to copy the data as it is, with formulas included, or values only, by setting the parameter {contentsOnly:true} (copyValuesToRange could be used too).

I hope this is of any help.