Insert formula in specific column of certain sheet

Question

Here is the scenario: When ever there is value "1" in column A of Sheet 1 i want to insert formula "=UNIQUE(Sheet2!B2:B1000)" in column B of Sheet 1.

I have only this formula which is used to delete contents:

function onEdit(e) {
if(e.range.columnStart === 1) { 
e.range.offset(0,1).clearContent();
}
}

but I am not able to insert formula based on a cell value

By column B of Sheet 1, you mean `'Sheet 1'!B1`, right? – Rubén May 04 '19 at 00:52 — Rubén, May 04 '19 at 00:52

score 0 · Answer 1 · answered May 03 '19 at 17:34

0

Try this:

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()!='Sheet1'){ return; }
  if(e.range.columnStart==1) {
    e.range.offset(0,1).setFormula("=UNIQUE(Sheet2!B2:B1000)");
  }
}

answered May 03 '19 at 17:34

Cooper

59,616
6
23
54

Thanks, but the error is coming "Cannot read property "range" from undefined. (line 2, file "insert form"" – Rony Joseph May 03 '19 at 17:40
Did you add the 'e' parameter? And perhaps you need to change your sheet name. – Cooper May 03 '19 at 17:45
1

@RonyJoseph It's not possible to run `onEdit(e)` from the Apps Script editor, you should use another function that pass the `e` argument with the required properties. – Rubén May 03 '19 at 19:18
@rubén, is there any other script for my case? – Rony Joseph May 04 '19 at 00:46
If you mean how to test `onEdit(e)` please checkout [How can I test a trigger function in GAS?](https://stackoverflow.com/q/16089041/1595451) – Rubén May 04 '19 at 00:49

score 0 · Answer 2 · answered May 04 '19 at 00:51

To insert a formula that uses A1 notation use setFormula. This is a method of Class Range, meaning that you first should get the range where you want to put your formula. There are several ways to get a range. Please checkout https://developers.google.com/apps-script/reference/spreadsheet/

Insert formula in specific column of certain sheet

2 Answers2