set static values for functions in Google Sheets automatically

I need an Apps Script function that automatically converts function output in cells in Google sheets, into a static value (function -> text).

I am specifically looking for a function that monitors/acts upon an array of cells H2:AV250 or more….

It doesn’t matter if the function sets static values of 1 cell, 3 cells or a row of cells at a time – but it must only maximally convert 1 row at a time.

---

Conditions

Range:

The set.value function has to be executed by the completion of a function ((function creating an output) – automatically

Speed

The execution should be immediate (1-3 sec.)
(If converting a row or nearly a row 1-10 sec. is good)

---

All the functions in the range H2:AV250 are linked together using IF-functions, so they execute sequencially = h2-i2-j2-k2-l2…av2-h3-i3-j3-k3-l3…av3-h4… etc.

• the functions will only execute if the cell its IF-function is referring to, has completed…

Functions waiting to be executed display “wait”, and should not be converted to static values:

Fix is = const skipStrings = [“wait”];

---

If you know of an app, google extension, program or any alternative that fixes this, that will be enough (I have searched for hours without finding anything…)

• If not, any help with my Apps Script would be greatly appreciated

I cannot paste a spreadsheet link here since it contains OpenAI functions that can run me up a bill – but if you want a test-sheet from me – I will make one

——- ATTEMPTS

Here are a few examples of my approaches

OnEdit: These require edits by the ‘user’ of the sheet to fire and don’t work

OnChange: These work in principle but I run into the following problems;

Function 1)

• I made a standard OnChange function that surveys the cell range H2:AV250, for any function completion

  • It fired, but only after the first 25 functions were completed, and when it fired, it would

convert the completed functions into static values, but for some reason the last 5-8 functions,

would display “#ERROR!”, even though, they had completed their execution and had a different output

before the convertion to static values.

---

Function 2)

• I then tried making the OnChange function survey the column AV for the output “Ready”

  • The functions in column AV outputs “Ready” when the function in, for example, AU2, has completed

    • Given the sequencial execution of the functions, a completed function in column AU2, means that the whole array of functions in cells H2:AU2 has completed.

• Upon the output of “Ready” in the column AV, the OnChange function should convert the corresponding row of the cell, of where the output “Ready” was made, into static values

=

This approach worked but stalled the OnChange function for up to 4 minutes before executing.

---

Functions:

Function 1)
(https://i.stack.imgur.com/fQAbV.png)

function onChange(e) {
  if (e.changeType !== 'EDIT') return;

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange("H2:AV300");
  const formulas = range.getFormulas();
  const values = range.getValues();

  for (let row = 0; row < formulas.length; row++) {
    for (let col = 0; col < formulas[row].length; col++) {
      // Add a condition to exclude cells containing "wait" and "Processing"
      if (values[row][col] !== "wait" && values[row][col] !== "Processing") {
        if (formulas[row][col]) {
          
          range.getCell(row + 1, col + 1).setValue(values[row][col]);
        }
      }
    }
  }
}

---

Function 2)

function setupProperties() {
  const sheet = SpreadsheetApp.getActive().getActiveSheet();
  const lastRow = sheet.getLastRow();
  let properties = {};

  for (let row = 1; row <= lastRow; row++) {
    let cellValue = sheet.getRange('AV' + row).getValue();
    properties['AV' + row] = cellValue || '';
  }

  PropertiesService.getScriptProperties().setProperties(properties);
}

function onChange(e) {
  if (e.changeType !== 'EDIT') return;
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const scriptProps = PropertiesService.getScriptProperties();
  
  for (let row = 1; row <= lastRow; row++) {
    let cellAddress = 'AV' + row;
    let currentValue = sheet.getRange(cellAddress).getValue();
    let storedValue = scriptProps.getProperty(cellAddress);

    if (currentValue !== storedValue) {
      scriptProps.setProperty(cellAddress, currentValue || '');

      if (currentValue === 'Ready') {
        convertRangeToStatic(sheet, row);
        sheet.getRange(row, 49).setValue('Ready'); // Column 49 is AW
      }
    }
  }
}

function convertRangeToStatic(sheet, row) {
  const range = sheet.getRange(row, 8, 1, 41); // Range from H:AV
  range.setValues(range.getDisplayValues());
}