Spreadsheet Email Trigger

Question

I have Written Script on Google Spreadsheet to send Email when spreadsheet is modified or any Data is added. Email Trigger is working but whenever any data is entered in next Row it send Email to previous email address also.

Please suggest solution

The below is written script :

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 1;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1 , numRows,3) // Fetch the range of cells A2:B3

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
for (i in data) {
    var row = data[i];
    var emailAddress = row[2];  // First column
    var message = row[0] + "requested" + row [1];       // Second column
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

Answer 1

Your question is unclear... nowhere in the script I see something that reads which cell is actually modified... your target range is hardcoded on row 2 so the only row that can be processed is row 2 (and the mail can only be sent once)...

So can you :

• explain how it should work
• explain how it works now , especially what do you mean by 'previous email'
• remove typos in your code (row[2] is not First column)
• explain how you trigger this function : the name onEdit(e) suggest an onEdit trigger but simple triggers cannot send mail so I suppose you have set some other trigger.
• explain why (e) in your function parameter and not using it ?

---

EDIT : thanks for the complement of information. The script you suggest is not sufficient to achieve what you want. The idea here is to check if something in the sheet has been modified either by adding (or inserting) a row of data or (if I understood well) by editing any row in the sheet with a new value.

This is not really as simple as it looks at the first glance ;-)

What I would do it to take a 'snapshot' of the sheet and -based on a timer or onEdit - compare that snapshot to the sheet's current state.

There is more than one way to get that result, you could have a second sheet in your spreadsheet that no one could modify and that is a copy of the main sheet that you update after each modification/mail send. So before updating the script should look for any difference between the sheets and send a report to the corresponding email when a difference is found.

Another way to do that is to store the sheet data converted to a string in the script properties, the principle is the same but it's more 'invisible' for normal users accessing the spreadsheet.

You could also use scriptDb or your userproperties but the script properties is probably better suited (simpler) for this use case.

Tell us what you think/prefer and I (or someone else) could probably give you some code to start with.

Answer 2

It appears that you're using a shared spreadsheet to collect the add-user-requests, and trusting the requesters to fill in the information. In the detail document you shared, it further appears that requests are ADDED, but not EDITED. (That's an important simplifying distinction.)

I suggest that what you really need is to use a form for receiving that input. Using a form will create a "data table" within your spreadsheet, a set of columns that you must not mess with. (You can edit the contents, add and delete rows, but must not add or remove columns.) However, you CAN add columns to the spreadsheet outside of this table, which gives you a handy place to store state information about the status of individual requests.

Further, you can trigger your processing to run on form submit, rather than a simple "onEdit" - this gets away from the problem that ScampMichael pointed out. Alternatively, you can use an installable edit trigger, as described in this answer.

Try this sheet, and this form. Save yourself a copy, go into the script and remove the comments that are stopping emails from being sent, and try it out. There's a menu item in the spreadsheet that can kick off processing; just clear the "Request State" column to re-run it. You can open the form (and find its URL), and add more entries to experiment.

It's the core of a similar system that I've written, and contains a discreet state machine for processing the requests. My system has large amounts of very complex data in multiple spreadsheets, so it often gets pre-empted, then needs to run again. (I use a timed trigger for that.) That's why requests are handled through states. If you find that too complex, pull out only the parts you need.