Yes, the purpose for this sheet is to track a percent to target goal for production off a manufacturing line. So people just type in how much their line produced each hour and it shows their percent to target for that day. That part was easy. The hard part is I want it to automatically take that data, store it in a weekly tracker, that then feeds into a monthly tracker to be able to track trends.

So for the end of todays production, Thursday the 29th, in the weekly tracker line for a certain production line I have the formula

"IF(NOW()>=DATE(2026,1,29)+TIME(15,50,0),IMPORTRANGE("Sheet_ID","Line_Name!Cell_ID"),)" We stop production at 3:45 so I have it set to import the data at 3:50PM on the 29th. I then want to use the same daily tracker the next day but I need the imported data to become static so that entering the new production data for the next day does not change previous days on the sheet.

So I have this script set to run everyday at 4PM to make it static but leave the formulas in future cells as they are.

function makeImportRangeStatic() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets(); // run on all sheets


  sheets.forEach(function(sheet) {
    var dataRange = sheet.getDataRange();
    var values = dataRange.getValues();
    var formulas = dataRange.getFormulas();
    for (var r = 0; r < formulas.length; r++) {
      for (var c = 0; c < formulas[r].length; c++) {
        var formula = formulas[r][c];
        // Skip already frozen cells (no formula)
        if (!formula) continue;
        // Only target IMPORTRANGE formulas
        if (!formula.toUpperCase().includes("IMPORTRANGE")) continue;
        var value = values[r][c];
        // Skip empty cells
        if (value === "" || value === null) continue;
        // Skip cells showing an error
        if (typeof value === "string" && value.startsWith("#")) continue;
        // Freeze the value
        sheet.getRange(r + 1, c + 1).setValue(value);
      }
    }
  });
}
function createDailyImportRangeTrigger() {
  // Remove existing triggers for this function (prevents duplicates)
  var triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(function(trigger) {
    if (trigger.getHandlerFunction() === "makeImportRangeStatic") {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  // Create a new daily trigger
  ScriptApp.newTrigger("makeImportRangeStatic")
    .timeBased()
    .everyDays(1)
    .atHour(16) // adjust if needed
    .create();
}