Google sheet to send email based on text in two different cells

Question

Background: I work in a school district and I'm currently trying to use Google Forms along with Google Sheets to track attendance for staff members. I have the form figured out, but what I'm trying to do with the sheet is beyond my abilities at the moment, though I'm willing to learn.

The sheet has two cells at the end for supervisors to "approve" and "sign" requests. I would like for the sheet to email the person requesting the time off once their supervisor has responded in the sheet.

IF "approved" = "yes" && there is text in the "signature" cell, send an email (pulled from the sheet) to the requester saying "Your absence request has been approved for the following dates: (dates from spreadsheet). IF "not approved" && there is text in the "signature" cell, send an email saying "Your absence request has been denied for the following dates: (dates from sheet).

Here's a link (updated 3/20/17) to the spreadsheet.

FYI: I've dabbled in JavaScript a little bit, but not really in sheets very much, so bear with me!

UPDATE: I solved my second question with the following code:

function onEdit() {
  // moves a row from "Form Responses 1" to "Signed" when the value in column 29 !==""
  // After row is moved it is deleted from "Form Responses 1"

  var sheetNameToWatch = "Form Responses 1";

  var columnNumberToWatch = 29; // column A = 1, B = 2, etc.
  var sheetNameToMoveTheRowTo = "Signed";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();


  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() !== "") {

    var targetSheet = ss.getSheetByName("Signed");
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).copyTo(targetRange);
    sheet.deleteRow(range.getRow());
  }
}

Now I would like to use the time-driven trigger to send emails once per day to every submitter whose submission has been approved, is in the "Signed" sheet, and has not had an email sent already for that submission. So, now the question I have is how do I get both triggers to work on the same sheet?

Cooper Cooper · Accepted Answer · 2017-03-16T03:46:31

You could use a time driven to check for these approval and the end of day each day. Make sure you put some sort of a timestamp so that you'll know that past approvals are complete so your not sending out multiple emails. Check the link You will also find the trigger resources in the Edit Menu now (down at the bottom). If you have any trouble with this let me know. I'll give you a hand.

Google sheet to send email based on text in two different cells

1 Answers