title: Google App Script | Email Send on New Sheet Line
body: <p>I'm trying to create an app script sends an email when a new row is added into my Google sheet.</p>
  <p>I have everything working for when I manually add in the data. However, when the data is auto added based on a form fill-- the email doesn't send.</p>
  <p>I'm thinking that if I add a script that copies &amp; pastes the email address after the new line is created, it will trigger the script to send an email. As of right now, auto-added rows do not send emails.</p>
  <p>Thanks for the help!</p>
  <p><a href="https://docs.google.com/spreadsheets/d/1QxhW-fs2M-YxcfbdKIMDi9GVBuS88YFbhAGd79XTxXI/edit?usp=sharing" rel="nofollow noreferrer">Here's a link to my sheet</a>.</p>
  <p>Here's the App Script I'm using:</p>
  <pre><code>function sendEmailNotificationAndInsertTimestamp_(e) {
    try {
      if (!e) {
        throw new Error('Please do not run the script in the script editor window but set up a trigger. See the instructions in the script.');
      }
      var range = e.range ? e.range : SpreadsheetApp.getActive().getActiveSheet().getActiveRange();
      if (!range || JSON.stringify(e.range) === '{}') {
        return;
      }
      var sheet = range.getSheet();
      if (range.getA1Notation() === 'A1' &amp;&amp; sheet.getName() === SpreadsheetApp.getActive().getSheets()[0].getName()) {
        return;
      }
      var notificationSettings = [
        {
          sheetsToWatch: /^(EmailSender)$/i,
          columnLabelsToWatch: ['Email'],
          columnValuesToWatch: [undefined],
          columnLabelsOfEmailAddresses: ['Send to'],
          columnLabelsOfEmailSubjects: ['Subject'],
          columnLabelsOfEmailContents: ['Body'],
          columnLabelsToTimestamp: ['Completed Timestamp'],
          insertTimestamps: [true],
          overwriteTimestamps: [true],
          eraseTimestamps: [false],
          timestampFormat: 'm/d/yyyy h:mm am/pm',
          columnLabelRow: sheet.getFrozenRows() || 1,
        }
      ];
      var rowStart = range.getRow();
      var columnStart = range.getColumn();
      var settings = getSettings_(sheet, notificationSettings);
      if (!settings || rowStart &lt;= settings.columnLabelRow) {
        return;
      }
      var keysToRead = ['columnLabelsToWatch', 'columnLabelsOfEmailAddresses', 'columnLabelsOfEmailSubjects', 'columnLabelsOfEmailContents', 'columnLabelsToTimestamp'];
      var columnLabelArrayLengths = ['columnValuesToWatch'].concat(keysToRead).map(getArrayLengths_(settings));
      if (!isStrictlyEqual_.apply(null, columnLabelArrayLengths)) {
        throw new Error('Mismatch between number of items in ' + keysToRead.join(', ') + '.');
      }
      var keysToWrite = ['columnsToWatch', 'columnsOfEmailAddresses', 'columnsOfEmailSubjects', 'columnsOfEmailContents', 'columnsToTimestamp'];
      if (keysToRead.length !== keysToWrite.length) {
        throw new Error('The number of items in keysToRead differs from the number of items in keysToWrite.');
      }
      var columnNumbers = getColumnNumbers_(sheet, settings, keysToRead, keysToWrite);
      if (!columnNumbers) {
        return;
      }
      var columnNumberArrayLengths = keysToWrite.map(getArrayLengths_(columnNumbers));
      if (!isStrictlyEqual_.apply(null, columnNumberArrayLengths)) {
        throw new Error('Could not find all the columns in ' + keysToRead.join(', ') + '.');
      }
      // iterate modified values
      var now = new Date();
      var emailAddressCheck = /^(([^&lt;&gt;()\[\]\\.,;:\s@&quot;]+(\.[^&lt;&gt;()\[\]\\.,;:\s@&quot;]+)*)|(&quot;.+&quot;))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/;
      var values = range.getDisplayValues();
      var emailRecipients = [];
      for (var column = 0, numColumns = values[0].length; column &lt; numColumns; column++) {
        var columnIndex = columnNumbers.columnsToWatch.indexOf(columnStart + column);
        if (columnIndex === -1) {
          continue;
        }
        var toWatch = settings.columnValuesToWatch[columnIndex];
        var regex
          = toWatch === undefined
          ? /./i
          : typeof toWatch === 'string'
          ? new RegExp('^' + escapeRegExCharacters_(toWatch) + '$', 'i')
          : toWatch instanceof RegExp
          ? toWatch
          : null;
        if (!regex) {
          throw new Error('All columnValuesToWatch must be regular expressions or text strings, but &quot;' + toWatch + '&quot; is a ' + (typeof toWatch) + '.');
        }
        for (var row = 0, numRows = values.length; row &lt; numRows; row++) {
          var value = values[row][column];
          if (value === '' &amp;&amp; settings.columnValuesToWatch[columnIndex] === undefined) {
            continue;
          }
          if (!value.match(regex)) {
            continue;
          }
          var emailAddress = sheet.getRange(rowStart + row, columnNumbers.columnsOfEmailAddresses[columnIndex]).getDisplayValue().trim();
          var emailSubject = sheet.getRange(rowStart + row, columnNumbers.columnsOfEmailSubjects[columnIndex]).getDisplayValue().trim();
          var emailContents = sheet.getRange(rowStart + row, columnNumbers.columnsOfEmailContents[columnIndex]).getDisplayValue().trim();
          if (emailAddress.split(',').some(address =&gt; !address.trim().replace(/^(.+&lt;)|(&gt;$)/g, '').match(emailAddressCheck))
            || [emailAddress, emailSubject, emailContents].some(string =&gt; string === '')) {
            throw new Error('Cannot send email with these parameters: To: &quot;' + emailAddress + '&quot;, Subject: &quot;' + emailSubject + '&quot;, Content: &quot;' + emailContents + '&quot;');
          }
          // send email
          MailApp.sendEmail(emailAddress, emailSubject, emailContents);
          emailRecipients.push(emailAddress);
          // insert timestamp
          if (settings.insertTimestamps[columnIndex]) {
            var timestampCell = sheet.getRange(rowStart + row, columnNumbers.columnsToTimestamp[columnIndex]);
            var timestamp
            = value.length
            ? now
            : settings.eraseTimestamps[columnIndex]
            ? null
            : now;
            if (timestamp &amp;&amp; !settings.overwriteTimestamps[columnIndex] &amp;&amp; timestampCell.getValue()) {
              continue;
            }
            timestampCell.setValue(timestamp).setNumberFormat(settings.timestampFormat);
          }
        } // row
      } // column
      if (emailRecipients.length) {
        showMessage_('Sent email of the status change to ' + emailRecipients.filter(filterUniques_).join(', ') + '.');
      }
    } catch (error) {
      showAndThrow_(error);
    }
  }    
  
  function getSettings_(sheet, settingsArray) {
    // version 1.0, written by --Hyde, 26 February 2020
    //  - initial version
    var settings = null;
    var sheetName = sheet.getName();
    for (var s = 0, numSettings = settingsArray.length; s &lt; numSettings; s++) {
      if (settingsArray[s].sheetsToWatch.test(sheetName)) {
        settings = settingsArray[s];
        break;
      }
    }
    return settings;
  }
  
  function getColumnNumbers_(sheet, settings, keysToRead, keysToWrite) {
    // version 1.0, written by --Hyde, 26 February 2020
    //  - initial version
    var columnLabelRowValues = sheet.getRange(settings.columnLabelRow, /*column*/ 1, /*numRows*/ 1, sheet.getLastColumn()).getValues()[0];
    var columnLabels = columnLabelRowValues.map(function stringTrim(value) {
      return String(value).trim();
    });
    var arrays = {};
    for (var k = 0, numKeys = keysToRead.length; k &lt; numKeys; k++) {
      var keyToRead = keysToRead[k];
      var keyToWrite = keysToWrite[k];
      arrays[keyToWrite] = [];
      for (var i = 0, col, numColumns = settings[keyToRead].length; i &lt; numColumns; i++) {
        if ((col = columnLabels.indexOf(settings[keyToRead][i])) !== -1) {
          arrays[keyToWrite].push(col + 1);
        }
      }
    }
    return arrays[keysToWrite[0]].length ? arrays : null;
  }
  
  function getArrayLengths_(obj) {
    // version 1.0, written by --Hyde 28 February 2020
    //  - initial version
    var closure = function(key) {
      return obj[key].length;
    };
    return closure;
  }
  
  function isStrictlyEqual_(value1, value2) {
    // version 1.0, written by --Hyde 28 February 2020
    //  - initial version
    var args = Array.prototype.slice.call(arguments);
    var numArgs = args.length;
    if (!numArgs) {
      return null;
    }
    var checkAgainst = args[0];
    for (var a = (1); a &lt; numArgs; a++) {
      if (args[a] !== checkAgainst) {
        return false;
      }
    }
    return true;
  }
  
  function filterUniques_(element, index, array) {
    // version 1.0, written by --Hyde, 30 May 2019
    //  - initial version
    return array.indexOf(element) === index;
  }
  
  function escapeRegExCharacters_(string) {
    // version 1.1, written by --Hyde, 9 November 2018
    //  - use String
    // version 1.0, written by --Hyde, 30 November 2018
    //  - initial version
    //  - adapted from https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions
    return String(string).replace(/[.*+?^${}()|[\]\\]/g, '\\$&amp;'); // $&amp; means the whole matched string
  }
  
  function showAndThrow_(error) {
    // version 1.0, written by --Hyde, 16 April 2020
    //  - initial version
    var stackCodeLines = String(error.stack).match(/\d+:/);
    if (stackCodeLines) {
      var codeLine = stackCodeLines.join(', ').slice(0, -1);
    } else {
      codeLine = error.stack;
    }
    showMessage_(error.message + ' Code line: ' + codeLine, 30);
    throw error;
  }
  
  function showMessage_(message, timeoutSeconds) {
    // version 1.0, written by --Hyde, 16 April 2020
    //  - initial version
    SpreadsheetApp.getActive().toast(message, 'Send email notification script', timeoutSeconds || 5);
  }
  
  function installOnEditTrigger() {
    // version 1.0, written by --Hyde, 9 January 2020
    //  - initial version
    deleteTriggers_(ScriptApp.EventType.ON_EDIT);
    deleteTriggers_(ScriptApp.EventType.ON_CHANGE);
    ScriptApp.newTrigger('sendEmailNotificationAndInsertTimestamp_')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onEdit()
    .create();  
  }
  
  function installOnFormSubmitTrigger() {
    // version 1.0, written by --Hyde, 16 April 2020
    //  - initial version
    deleteTriggers_(ScriptApp.EventType.ON_FORM_SUBMIT);
    ScriptApp.newTrigger('sendEmailNotificationAndInsertTimestamp_')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onFormSubmit()
    .create();
  }
  
  function installOnChangeTrigger() {
    // version 1.0, written by --Hyde, 7 May 2020
    //  - initial version
    deleteTriggers_(ScriptApp.EventType.ON_EDIT);
    deleteTriggers_(ScriptApp.EventType.ON_CHANGE);
    ScriptApp.newTrigger('sendEmailNotificationAndInsertTimestamp_')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onChange()
    .create();
  }
  
  function deleteTriggers_(triggerType) {
    // version 1.0, written by --Hyde, 7 May 2020
    //  - initial version
    var triggers = ScriptApp.getProjectTriggers();
    for (var i = 0, numTriggers = triggers.length; i &lt; numTriggers; i++) {
      if (triggers[i].getEventType() === triggerType) {
        ScriptApp.deleteTrigger(triggers[i]);
      }
    }
  }
  </code></pre>