title: Set a cell value after sending an email according to the volunteer application status
body: <p>I'm in creating a volunteer registration system for the organization  I work with. I want to write a script so that any time I run the script from the dropdown menu, it scans the spreadsheet row by row, and determines what the application status is. If the status is open, I want it to then determine if the status open email has been sent, if it hasn't, send the email, and change the value of the open_email value for that row to "SENT". If the app status is closed, I want it to do the same thing for the closed email.</p>
  
  <p><a href="https://i.stack.imgur.com/kfyDn.png" rel="nofollow noreferrer">Volunteer Spreadsheet</a></p>
  
  <p>Basically, I want to run this function whenever we get new volunteer applications so that they receive emails based on their application status.</p>
  
  <p>I've been successful in writing the function to scan the rows and send the emails based on the application status, but I can't seem to figure out how to get it to change the value of the cell after it has sent the corresponding email.</p>
  
  <p>Here is the code for the mail merge function:</p>
  
  <pre><code>//function for menu item 1
  function volunteerMailMerge() {
    SpreadsheetApp.getUi();
  
    //references
    var ss = SpreadsheetApp.openById("1Ei86oBsafBc6GdFeKFFGdbljyUzbWRFRF8eWvnoYdIU");
    var sheet = ss.getSheetByName("Application Status");
    var range = sheet.getDataRange(); //sets range to all data in the spreadsheet
    var values = sheet.getDataRange().getValues(); //translates the range into values in an array
    var headers = values.shift(); //removes headers from values array
  
    //loop through rows
    for (var i = 0; i &lt; values.length; i++) {
      var firstName = values[i][0];
      var lastName = values[i][1];
      var email = values[i][2];
      var position = values[i][3];
      var status = values[i][4];
  
      //mailmerge
      if (status == "OPEN" &amp;&amp; openEmail == "") {
            //if app status is open, and openEmail hasn't been sent, do this
            GmailApp.sendEmail(email, "SAFE Volunteer Application",{
                         htmlBody: "email body",
                         });
                         //change value of column 5 on the current row
      } else {};
  
    };
  
  }
  </code></pre>