Sending an email whenever Google Sheets specific range is edited

[isanka rangana]

I have a simple app script that will send an email whenever my G-sheet is edited. This works fine but it will send an email for every edit in any cell. So, I need to define a specific range as B3:E7 in my Gsheet and the email should trigger only after B3:E7 range is edited. No emails should send for other edits. Please help me on this. I took this code from this tutorial https://spreadsheet.dev/send-email-when-google-sheet-is-edited#:~:text=Step%201%3A%20Create%20your%20spreadsheet,whenever%20your%20spreadsheet%20is%20edited.

//@OnlyCurrentDoc function processEdit(e) { var sheet = SpreadsheetApp.getActive(); var rows = sheet.getRangeByName("signups").getValues(); var headerRow = rows.shift(); var editedRow = e.range.getRow(); var template = HtmlService.createTemplateFromFile("Template"); template.headerRow = headerRow; template.editedRow = editedRow; template.rows = rows; var html = template.evaluate().getContent(); MailApp.sendEmail({ to: "mye...@gmail.com ", subject: "This is test mail", htmlBody: html }); }

[cbmserv...@gmail.com]

Hi Isanka,

 

You just need to add one if statement in your script to quit if the cell being edited is not in your chosen range.

 

B3 is row 3, column 2

E7 is row 7, column 5

 

So get column number and then add the if statement in the position below:

 

//@OnlyCurrentDoc 4

function processEdit(e)

{

var sheet = SpreadsheetApp.getActive();

var rows = sheet.getRangeByName("signups").getValues();

var headerRow = rows.shift();

var editedRow = e.range.getRow();

 

var editedCol = e.range.getColumn();

if (  (  ( row < 3 ) || (row >7) ) || ( (editedCol <2)  || (editCol > 5) ) ) {return;}  // do nothing if outside the range

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3666c088-3d08-4420-a13b-5b217c749691n%40googlegroups.com.

[Isanka Rangana]

Hey. Thank you very much for helping me with this. I tried this but no luck. Could you kindly have a look at my Gsheet below

https://docs.google.com/spreadsheets/d/17ngxuIZ37Bveuyli92Gj1w2NzSIi7y-aypbQrE4Dfw0/edit#gid=1486321515

[Laurie J. Nason]

I think that the if statement needs to have && (AND) instead of || (OR) and the <> signs need to be reversed - 

if (  (  ( row < 3 ) || (row >7) ) || ( (editedCol <2)  || (editCol > 5) ) ) {return;}

Should Read

if (((row < 3) || (row >7)) && ( (editedCol <2)||(editCol > 5))) {return;}

Also - I would say (personal preference here) that it’s bad form to have a “return” in the middle of a function - a function should only have one exit - but I know that can be a divisive issue :-)

Laurie

------ Original Message ------

From "Isanka Rangana" <isa...@gssintl.biz>

To "Google Apps Script Community" <google-apps-sc...@googlegroups.com>

Date 11/09/2022 09:07:59

Subject Re: [Apps-Script] Sending an email whenever Google Sheets specific range is edited

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5f5a726c-b763-49be-87ca-7cb70ebeb5f8n%40googlegroups.com.

[CBMServices Web]

Laurie's change is correct (had a logic error).

But there is also another error with variable name.

 Try this:

//@OnlyCurrentDoc 4

function processEdit(e)

{

var sheet = SpreadsheetApp.getActive();

var rows = sheet.getRangeByName("signups").getValues();

var headerRow = rows.shift();

var editedRow = e.range.getRow();

var editedCol = e.range.getColumn();

if (  (  ( editedRow < 3 ) || (editedRow >7) ) && ( (editedCol <2)  || (editCol > 5) ) ) {return;}  // do nothing if outside the range

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/emd37831fb-8ad1-4f02-80a2-ffcd40bf91ad%40156d0e53.com.

[CBMServices Web]

Actually thinking about this more, I think initial logic of OR is correct. If either row or col is out of range, it should just return.

if (  (  ( editedRow < 3 ) || (editedRow >7) ) || ( (editedCol <2)  || (editCol > 5) ) ) {return;}  // do nothing if outside the range

So please change it back to the || instead of &&.

[Isanka Rangana]

Hi All,

First of all let me thank you all for your wonderful support. However still no luck. I keep getting e-mails for every single edit. Could you kindly have a look in the below G sheet?

https://docs.google.com/spreadsheets/d/17ngxuIZ37Bveuyli92Gj1w2NzSIi7y-aypbQrE4Dfw0/edit#gid=1486321515

[CBMServices Web]

I will take a peak in a bit and see what is going on.

Please make sure you provide me with edit access so that I can look at the script.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6cf1051e-d2f5-4828-981f-5f23131799ean%40googlegroups.com.

[isanka rangana]

Thank you very much. You are awesome. I have granted edit access to all.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/6LNrS4E4URg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3UTLVWFtioY6DwF5jo2hZC0YbuDmF30Gm%3DLwSUdGvk68g%40mail.gmail.com.

[cbmserv...@gmail.com]

Hi,

 

I checked the script and it is working correctly.

 

I highlighted the area in red where any changes will cause the email to go out. Any changes outside the red area will not trigger the email. I think this is what you asked for..

 

I added an alert when the trigger occurs in the right zone. You will not see the alert if any cell is edited outside the zone.

 

Once you are happy testing, just delete the alert (which is these two commands):

 

var ui = SpreadsheetApp.getUi();

ui.alert("Triggered at cell: row: " + editedRow + " Column: " + editedCol);

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKKL0BWLY-5GAKwqsoeu0FOphY%3DWyznp%3DVA1_%3DdFAGeeauCe0A%40mail.gmail.com.

[isanka rangana]

Hey

Great, it worked. It is out of verbal definitions to say thank you. You are awesome   😍😍😍😍😍😍😍😍

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/000c01d8c64d%243faeee80%24bf0ccb80%24%40gmail.com.

[CBMServices Web]

Your are welcome.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKKL0BVge56rFPZ2StjvgGPni68e_htYL5VwCx4A79gcmXFv2w%40mail.gmail.com.

[game over]

how to find google sheets id