CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Mathavan Krishnan
Mathavan Krishnan

Reputation: 149

Modifying OnEdit function

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);
}

Upvotes: 2

Views: 252

Answers (1)

Yuri Khristich
Yuri Khristich

Reputation: 14502

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);
}

Upvotes: 3

Related Questions