Insert formula in a Google Sheets programmatically

Question

I need to put a formula into a cell in each new row added to a Google Sheets. I have this working in VBA but not been able to build it correctly in Script. I loop through i rows until lastrow. In cell J, I want this formula inserted:

var Discount = '=IF(ISBLANK("F"+i,,IF(ISNUMBER(FIND("CM","B"+i)),IF("C"+i>"F"+i,150,0),0))';

I use this method to add the row:

var dtaCollect = ["","",StartDate,CustomerName,Monthly,"",Discount,LateFee,TotalPaid,Commission,Note,Referral];
    target_sheet.appendRow(dtaCollect);
    i++;
    } else {
      i++;
      }
    }

However, the formula is written exactly as above, without i substituted with the iteration value. As a result I get #ERROR! in the cell. I've tried INDIRECT and concat.

How can I fix this?

Mogsdad Mogsdad · Accepted Answer · 2015-09-09T14:49:04

The value i isn't being substituted in your string because it's just text. You need to break it out of the string, and be more careful with your use of quotes to ensure you end up with a viable formula. This would work:

var Discount = '=IF(ISBLANK(F'+i+',,IF(ISNUMBER(FIND("CM",B'+i+')),IF(C'+i+'>F'+i+',150,0),0))';

Since you're using A1Notation, a simple JavaScript String.replace() should be all you need to provide a more readable solution:

var Discount = '=IF(ISBLANK(F%row%,,IF(ISNUMBER(FIND("CM",B%row%)),IF(C%row%>F%row%,150,0),0))'
               .replace(/%row%/g, i.toString());

Explanation:

replace() will find regexp or substring matches, and replace them with a new substring.
in this case, we're looking for a regexp; the g flag means we'll look for all occurrences of "%row%" and replace them with the value of i.
We've used the % as bookends, to make the replaceable text stand out clearly - just a convention, not a requirement.

Note: You didn't show how you used INDIRECT, only mentioned that you tried it. It is an alternative here, and might be preferred as you could simply copy a formula from an existing cell without worrying about adjusting the references.

Insert formula in a Google Sheets programmatically

1 Answers