Google Sheets – Tie Cell Updating Formula to Script Running on Demand

google sheetsgoogle-apps-script

I have a Google Sheet with about 300 rows in it. A couple of the columns have formulas like this:

=IF(ISBLANK(E210),C10,
QUERY(importrange(SheetKey,"SheetName!A:B"),"select Col2 where Col1="&E10,0))

What the formulas do:
When an ID number is entered in one cell, the other cell retrieves information from another cell of the row where the ID number is found. You may preview a copy at https://drive.google.com/previewtemplate?id=14yB8lqTdFvszLs7eaJcWQXVPtQcqBUjp4dJqIY_Yfx4&mode=public — see the "Rabbits_List" tab.

While entering new rows, there seems to be quite a bit of lag, I am guessing because of those formulas.

Is there a way to tie those cells updates to a script, so it can be linked to a button and run on demand?

I am using Google Sheets with the Chrome browser.

Best Answer

Here is a script that adds two custom menu commands: "Freeze Formulas" and "Unfreeze Formulas". The logic is very simple: freezeFormulas replaces every formula in the spreadsheet with text value of the formula, preceded by triple backtick (randomly chosen string that doesn't normally appear in spreadsheets; you can use something else like exotic Unicode characters or private use Unicode codepoints).

The formula freezeFormulas finds all cells where the value begins with triple backtick, and restores them to formulas.

function freezeFormulas() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var formulas = range.getFormulas();
  for (var i=0; i<formulas.length; i++) {
    for (var j=0; j<formulas[i].length; j++) {
      if (formulas[i][j]) {
        range.getCell(i+1, j+1).setValue('```'+formulas[i][j]);
      }
    }
  }
}

function unFreezeFormulas() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var values = range.getValues();
  for (var i=0; i<values.length; i++) {
    for (var j=0; j<values[i].length; j++) {
      if (/^```/.test(values[i][j])) {
        range.getCell(i+1, j+1).setFormula(values[i][j].slice(3));
      }
    }
  }
}

function onOpen() {
  var menu = [{name: "Freeze Formulas", functionName: "freezeFormulas"}, {name: "Unfreeze Formulas", functionName: "unFreezeFormulas"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}
Related Topic