Paste formula as value on a certain date

Question

I've been digging around on line for a couple of hours now to no avail. I'm trying to write a formula(which I assume is only possible through AppScript), that based on a certain date, will re-paste contents in a cell as values only.

The issue I've had with other scripts is that in my situation I have several different values that need to be checked. I've set up a continuously updating calendar that pulls from an array with listed dates for each item. However, in that array, the row gets moved into an archive once it hits todays date. Thus, they get deleted from the calendar too.

If there is another solution to this, like pasting based off conditional formatting(this would then keep the formulas that haven't found a value yet, and paste ones that have been found as values only), that would be great too.

Any help or advice would be greatly appreciated. If you need any other info, please ask. EDIT: The spreadsheet link is in comments. Attached are a before and after picture to help visualize whats going on here. Since the calendar is pulling from the active sheet, and rows on the active sheet go to "archive" when J2-Today() is -1(yesterday), those then disappear from the calendar. I've tried to make a mixed sheet with both archive and active, but I get duplicates as a frequent issue.

Answer 1

See code below:

function archiveRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mainSheet = ss.getSheetByName("Sheet1");
  var archiveSheet = ss.getSheetByName("Sheet2");

  // last row should be on the data itself, same with column
  var lastRow = mainSheet.getLastRow(); 
  var lastCol = mainSheet.getLastColumn(); 
  // set time to 0 so we can only compare dates
  var dateToday = new Date().setHours(0, 0, 0, 0);

  var range = mainSheet.getRange(1, 1, lastRow, lastCol);
  var values = range.getValues();
  // remove header to only process data
  var header = values.shift();
  var indices = [], difference = [];

  // remove dates that are earlier than today
  // collect rows and indices data when condition is not met
  values.forEach(function (row, index) {
    if (row[9] >= dateToday)
      return true;
    difference.push(row);
    indices.push(index);
  });

  // remove the row with earlier date
  // offset with 2 due to 0-indexing and header
  indices.forEach(index => mainSheet.deleteRow(index + 2));

  // all rows that were deleted are copied to archive sheet
  difference.forEach(row => archiveSheet.appendRow(row));
}

Sample Data:

Output:

Note:

• If you need to have this run on a daily basis, Time Driven trigger is your friend. Just have it set on a daily basis triggering archiveRows
• Make sure that the main sheet only contains the data. (I see your sheet has rows containing 9AM values below. Make sure to clear those as the script will mistakenly include it on the processing)
• Adjust date condition if needed.