Sending an email with spreadsheet data

Question

I'm currently trying to write an Apps Script function that sends an email when its Google Sheet is edited. My current problem is that the function only sends one column of data and I need it send both the date and amount of inventory. This is what I currently have, I'm thinking that maybe I need a nested loop in order for the function to print both columns. Any help would be great!

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(2, 1, 4, 2);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  //var data = dataRange.getSheetValues();
  for (i in data) {
    var row = data[i];
    var message = row[1];       // Second column
    var subject = "Inventory update";
    MailApp.sendEmail("test@test.com", subject, message);
  }
}

That's a lot of repeated mail sent to the same address. Why not concatenate the message and send it at the end? You'll avoid a lot of quota and rate limit issues. If this function is called with an `onEdit` installed trigger, you should pass in the event object (`sendEmails`->`sendEmails(e)`) and check that the modified range (`e.range`) is within a corresponding section, to avoid unnecessarily spamming someone just because some unrelated text was modified. FTR, iterating an `Array` with `for ... in ...` is not recommended. — tehhowch, Mar 09 '18 at 18:09

Jack Brown · Accepted Answer · 2018-03-09T18:26:40.793

In the above code values of your cells from range "A2:B5" are stored in an array of array format in variable data i.e

var data = dataRange.getValues(); //This code results in the below
data = [["A2","B2"],
        ["A3","B3"],
        ["A4","B4"],
        ["A5","B5"]]

Where A2 corresponds to the value of cell A2, B2 to cell B2 and so on. To access the first row you would the following:

data[0] => ["A2","B2"]

Note: data[0] returns another array containing all the elements on row 1. Also, note the numbering starts from 0 not form 1
Now to access the first element of the first row, you would do the following:

data[0][0] => "A2"

So similarly in your above, code when you do this

var row = data[i]; where i = 0
row => ["A2","B2"]

Hence to get first column you would do

row[0]  => "A2"

to get the second column you would do

row[1]  => "B2"

Modify your message variable to look like this

message = message + row[0] +","+row[1]"\n"

Where in your appended the new row to the previous message with a comma delimiter and \n new line feed.

As of now, your mail app sends 1 email per each row. However, I don't believe that is the intended behavior. Below is your modified code

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(2, 1, 4, 2);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  //var data = dataRange.getSheetValues();
  var message =""  //empty string
  //Loop through each row and form CSV data
  for (var i=0;i<data.length;i++) {  //edited because:https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea
    var row = data[i];
    message = message + row[0] +","+row[1]"\n"   
  }
  // Send one email with all the rows
  var subject = "Inventory update"; 
  MailApp.sendEmail("test@test.com", subject, message);
}

Do not iterate Javascript arrays with the `.. in ..` pattern. — tehhowch, Mar 09 '18 at 18:11
https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea — tehhowch, Mar 09 '18 at 18:23

score 0 · Answer 2 · answered Aug 03 '20 at 10:04

0

The initial question did not insist on a coded solution. Therefore, your exact usecase can be done without code, using this Gsheet addon:

https://reactor.isvery.ninja/

see the tutorial video in the bottom (send email from spreadsheet)

answered Aug 03 '20 at 10:04

coderofsalvation

1,764
16
13

Sending an email with spreadsheet data

2 Answers2