0

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 so that it will automatically fill up some columns there in my google sheet without doing it manually. So guys can anyone here help where if there is a row of data is added to column B, it should trigger the coding I have done in the appscript. I have attached the images and my code to explain myself better. Thank you.

enter image description here

Code.gs

var url = "https://docs.google.com/spreadsheets/d/1bM8l6JefFsPrlJnTWf56wOhnuSjdIwg3hMbY1tN1Zp8/edit#gid=531487589"; 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 username(){ var ss = SpreadsheetApp.openByUrl(url); 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); } function noOfPaid(){ var ss = SpreadsheetApp.openByUrl(url); var sheet = ss.getSheetByName("USERNAMES"); ss.getRange("F2").setFormula("=IF(ISBLANK(B2:B), ,(COUNTIF('Payment Configuration'!A:A,A2:A) + COUNTIF('Payment Configuration'!E:E,A2:A)))"); var range1 = sheet.getRange("B:B"); var lr = getLast(range1); // Retrieve last row of column 2. var fillDownRange = sheet.getRange(2, 6, lr-1); ss.getRange("F2").copyTo(fillDownRange); } function fullAddress(){ var ss = SpreadsheetApp.openByUrl(url); var sheet = ss.getSheetByName("USERNAMES"); ss.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 fillDownRange = sheet.getRange(2, 9, lr-1); ss.getRange("I2").copyTo(fillDownRange); }
Improve this question
3
  • 3
    Does this answer your question? Trigger script on google spreadsheet when a new row is inserted Commented May 22, 2021 at 10:17
  • @WillMetcher No bro because I only I only want to trigger the three functions when the column B in "USERNAMES" sheet is modified bro. Can you help me with it? Commented May 22, 2021 at 10:30
  • @WillMetcher Bro I tried the coding but somehow it shows an error for me bro. Can you help me to solve the problem. script.google.com/home/projects/… - Link to appscript Commented May 22, 2021 at 13:21

2 Answers 2

Reset to default
0 
function onEdit(e) { if(SpreadsheetApp.getActiveSheet().getSheetName() === "USERNAMES" && e.range.columnStart == 2) { // run functions } }
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@WaiHaLee Bro I tried the coding but somehow it shows an error for me bro. Can you help me to solve the problem. script.google.com/home/projects/… - Link to my appscript
What's the error?
My google sheet is keep on loading by itself bro. Suddenly it is not showing any fomula's there. It is like the sheet is fluctuating
docs.google.com/spreadsheets/d/… - Link to my google sheet
I open the links for editor bro would you mind have a look on the problem
0

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.

Improve this answer

Comments