Modifying OnEdit function

Question

I am a beginner to AppScript. I am developing a code for OnEdit where the function is something like this

function onEdit(e) { if(SpreadsheetApp.openByUrl(url).getActiveSheet == "USERNAMES" && e.range.columnStart == 2 ) { formulasheets(); } }

But it is not working I editted something in column B. I want the onedit function to work when there is modification done to column B. Can anyone help me on this?

Code.gs

var url = "https://docs.google.com/spreadsheets/d/1bM8l6JefFsPrlJnTWf56wOhnuSjdIwg3hMbY1tN1Zp8/edit#gid=531487589"; function onEdit(e) { if(SpreadsheetApp.openByUrl(url).getActiveSheet == "USERNAMES" && e.range.columnStart == 2 ) { formulasheets(); } } function doPost(e) { var rowData = []; var ss = SpreadsheetApp.openByUrl(url); var sheet = ss.getSheetByName("PaypalData"); rowData.push(new Date(e.parameter.payment_date)); rowData.push(e.parameter.item_number); rowData.push(e.parameter.option_selection1); rowData.push(e.parameter.payment_status); rowData.push(e.parameter.payment_gross); rowData.push(e.parameter.mc_currency); rowData.push(e.parameter.payment_fee); rowData.push(e.parameter.first_name); rowData.push(e.parameter.last_name); rowData.push(e.parameter.payer_email); rowData.push(e.parameter.residence_country); rowData.push(e.parameter.txn_id); sheet.appendRow(rowData); } function getLast(range) { var getResult = function(range) { if (!((range.getNumRows() > 1 && range.getNumColumns() == 1) || (range.getNumRows() == 1 && range.getNumColumns() > 1))) { throw new Error("Please input one row or one column."); } var v = Array.prototype.concat.apply([], range.getValues()); var f = Array.prototype.concat.apply([], range.getFormulas()); var i; for (i = v.length - 1; i >= 0; i--) { if (v[i] != "" || f[i] != "") break; } return i + 1; }; if (Array.isArray(range)) { return range.map(function(e) { return getResult(e); }); } else { try { range.getA1Notation(); } catch (e) { throw new Error("Inputted value is not a range."); } return getResult(range); } } function formulasheets(){ var ss = SpreadsheetApp.openByUrl(url); var sheet = ss.getSheetByName("USERNAMES"); sheet.getRange("A2").setFormula('=B2:B &"."&substitute(substitute(lower(C2:C),"jalan","jln")," ","")'); sheet.getRange("F2").setFormula("=COUNTIF('Payment Configuration'!A:A,A2:A) + COUNTIF('Payment Configuration'!E:E,A2:A)"); sheet.getRange("I2").setFormula('=join(", ",{B2,UPPER(C2),"BANDAR PUTERI KLANG"})'); var range1 = sheet.getRange("B:B"); var lr = getLast(range1); // Retrieve last row of column 2. var fillDownRangecolumnA = sheet.getRange(2, 1, lr-1); var fillDownRangecolumnF = sheet.getRange(2, 6, lr-1); var fillDownRangecolumnI = sheet.getRange(2, 9, lr-1); sheet.getRange("A2").copyTo(fillDownRangecolumnA); sheet.getRange("F2").copyTo(fillDownRangecolumnF); sheet.getRange("I2").copyTo(fillDownRangecolumnI); }

It should be working unless you are doing something that requires permission and it that case you will need an installable onedit trigger and you will be required to provide authorization. — Cooper
– Cooper, Commented May 23, 2021 at 3:11
@Cooper I have gave the permission ready bro. I give you the link to my google sheet would you mind check it — Mathavan Krishnan
– Mathavan Krishnan, Commented May 23, 2021 at 3:18
@Cooper docs.google.com/spreadsheets/d/… - Link to google sheets — Mathavan Krishnan
– Mathavan Krishnan, Commented May 23, 2021 at 3:18
And you already asked this question yesterday, and it looks like you don't care the answers: stackoverflow.com/questions/67648131/… What's wrong with you, bro? — Yuri Khristich
– Yuri Khristich, Commented May 23, 2021 at 14:58

Yuri Khristich · Accepted Answer · 2021-05-23 16:17:18Z

Why do you need openByUrl()? You can get access to current sheet via e.source:

function onEdit(e) { var sheet = e.source.getActiveSheet(); if (sheet.getName() == "USERNAMES" && e.range.getColumn() == 2) { formulasheets(sheet) } }

function formulasheets(sheet){ sheet.getRange("A2").setFormula('=B2:B &"."&substitute(substitute(lower(C2:C),"jalan","jln")," ","")'); sheet.getRange("F2").setFormula("=COUNTIF('Payment Configuration'!A:A,A2:A) + COUNTIF('Payment Configuration'!E:E,A2:A)"); sheet.getRange("I2").setFormula('=join(", ",{B2,UPPER(C2),"BANDAR PUTERI KLANG"})'); var range1 = sheet.getRange("B:B"); var lr = getLast(range1); // Retrieve last row of column 2. var fillDownRangecolumnA = sheet.getRange(2, 1, lr-1); var fillDownRangecolumnF = sheet.getRange(2, 6, lr-1); var fillDownRangecolumnI = sheet.getRange(2, 9, lr-1); sheet.getRange("A2").copyTo(fillDownRangecolumnA); sheet.getRange("F2").copyTo(fillDownRangecolumnF); sheet.getRange("I2").copyTo(fillDownRangecolumnI); }

You don't even need the function formulasheets(). You can add ist lines into onEdit() function.

And it's not the best practice to refill all the columns (A, F, I) every time as something is changing in column B. It would be enough to update just three cells on the current row. The current row you can get from e object:

var row = e.range.getRow();

And probably you won't even need the function getLast() in this case.

So, all the code from OP can be boiled down to this:

function onEdit(e) { if (e.source.getActiveSheet().getName() != "USERNAMES") return; if (e.range.getColumn() != 2) return; var sheet = e.source var row = e.range.getRow(); var A = '=B2:B &"."&substitute(substitute(lower(C2:C),"jalan","jln")," ","")'; var F = "=COUNTIF('Payment Configuration'!A:A,A2:A) + COUNTIF('Payment Configuration'!E:E,A2:A)"; var I = '=join(", ",{B2,UPPER(C2),"BANDAR PUTERI KLANG"})'; sheet.getRange("A"+row).setFormula(A); sheet.getRange("F"+row).setFormula(F); sheet.getRange("I"+row).setFormula(I); }

Collectives™ on Stack Overflow

Modifying OnEdit function

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related