Automatically Run The Script If A New Row Is Added To A Specific Column

I am developing a web app where the admin can add the residents name and few particulars about them there. So after adding the data there, I have few formulas written in appscript

Solution 1:

function onEdit(e) {
  if(SpreadsheetApp.getActiveSheet().getSheetName() === "USERNAMES" && e.range.columnStart == 2) {
    // run functions
  }
}

Solution 2:

It's all wrong, as far as I can tell.

For example: the function doPost() appends a new row on the sheet 'PaypalData'. But the columns of the new row (parameters of e) don't fit with columns on the sheet. Not to mention that the sheet 'PaypalData' has no real data at all. It gets the data from another spreadsheet via IMPORTRANGE(...) formula in the cell A3. And all the 'data' disappears as soon as you add something in the range of IMPORTRANGE That is the mess!

The task 'to add a new row on some sheets' is quite simply by itself. And you got the more or less correct example how it can be done already.

It looks like you just copy/paste too much someone's code without faintest idea how it works. I'm sorry, but you need to start from something less complicated.

Well, you can try to fix your code a bit...

The function onEdit():

function onEdit(e) {
  if (e.source.getActiveSheet().getName() == "USERNAMES" && e.range.getColumn() == 2) {
    username(e.source);
    noOfPaid(e.source);
    fullAddress(e.source);
  }
}

The function username():

function username(ss) {
  var sheet = ss.getSheetByName("USERNAMES");
  ss.getRange("A2").setFormula('=IF(ISBLANK(B2:B),"",(B2:B &"."&substitute(substitute(lower(C2:C),"jalan","jln")," ","")))');
  var range1 = sheet.getRange("B:B");
  var lr = getLast(range1); // Retrieve last row of column 2.
  var fillDownRange = sheet.getRange(2, 1, lr-1);
  ss.getRange("A2").copyTo(fillDownRange);
}

Etc... Instead of:

function noOfPaid(){
  var ss = SpreadsheetApp.openByUrl(url);
...

function fullAddress(){
  var ss = SpreadsheetApp.openByUrl(url);
  ...

You need to use:

function fullAddress(ss){
  ...  

function noOfPaid(ss){
  ...

Since you can't open the spreadsheet by URL if this spreadsheet already was open.

This makes your code to woks to some extent. It will copy the formulas in columns A, F and I when you will change column B. But it looks pretty awful anyway.