CODEWITHSUNDEEP
web-applicationsgoogle-apps-scriptgoogle-sheets
Sami Chouchane
Sami Chouchane

Reputation: 219

Reading spreadsheet, send value by e-mail

Im a beginner for Google Scripts and im looking for a possability to read values from my spreadsheet and write an automatic email with these values in it. I tried to find some information on Google and in Forums but I dont understand it very well.

For example: in my spreadsheet C2 is the cell that the user will fill in (it is a price per square meter calculator). When C2 is filled in with for example 6m2, D2 will calculate the price. For example €240.

Now I want to add a Script for this Sheet that automaticly creats and send an email to me with the text: 'You want carpeting for a 'C2' location. This will cost 'D2'.

I know this is possible but I dont know how... Can somebody please help me?

Upvotes: 0

Views: 127

Answers (1)

cmdr_keen
cmdr_keen

Reputation: 179

Normally you could use a function like the following which automatically fires each time a cell is edited:

function onEdit(e){
  var range = e.range; //Get edited range
  if (range.getA1Notation() == 'C2'){
    var calculatedValue = range.offset(0,1); //Assumes that the calculated value is in the cell to the right of the cell where the area is entered
    var body = 'You want carpeting for a '+ range.getValue() + ' location. This will cost ' + calculatedValue.getValue();
    try{
      GmailApp.sendEmail('[email protected]', 'new carpet request', body);
    }
    catch(e)
    {
      Logger.log(e)
    }
  }
}

Unfortunately for you, the onEdit() trigger fires when a spreadsheet is edited does not allow a script to send am email. See https://developers.google.com/apps-script/guides/sheets/functions#advanced

Your next best option would be to couple it with a menu item by adding an onOpen function like the following:

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Scripts')
      .addItem('Send Email', 'sendEmail')
      .addToUi();
}

which calls a sendEmail function like this:

function sendEmail(){
  var range = SpreadsheetApp.getActiveSpreadsheet().getRange('C2');
  var calculatedValue = range.offset(0,1);
  var body = 'You want carpeting for a '+ range.getValue() + ' location. This will cost ' + calculatedValue.getValue();
  try{
    GmailApp.sendEmail('[email protected]', 'new carpet request', body);
  }
  catch(e)
  {
    Logger.log(e)
  }
}

You'll obviously also need to add some error checking and pretty syntax to get your email to come out the way you like it with units and everything, unless you plan to do that in the spreadsheet.

Upvotes: 1

Related Questions