Google Apps Script - set formula of active cell depending on value of another cell

Question

I’m writing a custom function in Google Apps Script that, if a certain other cell contains a number, uses the value of that cell and several other cells to calculate a result. Otherwise, if that certain other cell does not contain a number, the function should just return an empty string so that the active cell appears blank.
I was able to come up with a working function to do this, but in order to protect sensitive information, I’m not going to copy it here. Instead, here’s an example function that accomplishes the same thing:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rowNum = sheet.getActiveCell().getRow();
  var rowVals = sheet.getRange(rowNum, 1, 1, 15).getValues();

  var fVal = rowVals[0][5];
  if (fVal == "" || isNaN(fVal)) {
    return "";    //leave cell blank if column F doesn't contain a number
  }

  var aVal = rowVals[0][0];
  var bVal = rowVals[0][1];
  var cVal = rowVals[0][2];
  var gVal = rowVals[0][6];

  return ((gVal * fVal) + aVal + bVal + cVal);
}

However, in an effort to speed it up (and also some other reasons that would be complicated to try to explain here, so you'll have to just trust me), I want to have the custom function set the value of the cell to be a formula instead of doing the calculating itself. It doesn’t work to just put the formula in the cell in the first place because then it still calculates/shows a result even if column F doesn’t contain a number. Here’s what I’ve tried so far:

function myFunction2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rowNum = sheet.getActiveCell().getRow();
  var fVal = sheet.getRange(rowNum, 6).getValue();

  if (fVal == "" || isNaN(fVal)) {
    return "";    //leave cell blank if column F doesn't contain a number
  }

  var formula = '=SUM((G2*F2)+A2+B2+C2)';

  return formula;
}

^This just makes the cell display the string “=SUM((G2*F2)+A2+B2+C2)”.

So I then tried using setFormula on the active cell:

function myFunction3() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  var rowNum = cell.getRow();
  var fVal = sheet.getRange(rowNum, 6).getValue();

  if (fVal == "" || isNaN(fVal)) {
    return "";    //leave cell blank if column F doesn't contain a number
  }

  cell.setFormula('=SUM((G2*F2)+A2+B2+C2)');
}

^which, when I called the function in a cell, returned an error saying “You do not have permission to call setFormula”. The same thing happened when I tried getting the a1 notation of the active cell and then using getRange(a1Notation).setFormula('=SUM((G2*F2)+A2+B2+C2)') instead of calling setFormula directly on the active cell.

Anybody know if there's a way around that permission error? or have any other ideas for how to accomplish this?

Ed Nelson Ed Nelson · Accepted Answer · 2017-08-11T01:40:02

The permission error is because of restrictions on what user defined functions can do. You can, however, do this with onEdit like this:

function onEdit(e) {
var ss=SpreadsheetApp.getActiveSpreadsheet()
var s=ss.getActiveSheet();
var col = e.range.getColumn();
var rowNum = e.range.getRow();
if(col==6){
  var fVal = s.getRange(rowNum,col,1, 1).getValue()
 }
     if (fVal == "" || isNaN(fVal)) {
       return 
     }
      else{
        s.getRange(rowNum,col,1, 1).setFormula('=SUM((G2*F2)+A2+B2+C2)');
   }}

Google Apps Script - set formula of active cell depending on value of another cell

2 Answers