3

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.

3
  • Looks complicated... I would try entering data in another sheet and then paste a whole block in.
    – user79865
    Aug 14, 2015 at 5:34
  • I have done it that way, @Normal Human, and you are right, that works well. I do hope to find a way to accomplish it via script, because my application is used by some not-tech-savvy folks. Aug 15, 2015 at 6:42
  • It finally occurred to me, that the solution is to use the offline mode for entering new data. Response time is much faster in offline mode. Aug 15, 2015 at 23:32

1 Answer 1

2

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);
}
2
  • The freezeFormulas script posted by Normal Human also works well when linked to a button. Aug 17, 2015 at 3:32
  • Another possible solution. When "ok" is selected from dropdown, all the formulas of the corresponding row are converted to values. Example (make a copy to test it): docs.google.com/spreadsheets/d/… Aug 18, 2015 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.