Export CSV and email to user

Ask for Help
9

All - I am close on this and need idea / help!

Glide - Trying to Email a Filtered Sheet CSV — Watch Video

Here is the workflow:

  1. User clicks a button which adds a row the export queue table.
  2. My App script checks for new rows that have not been sent.
  3. If an new export request is found, it sends the user an export of a Google Sheet.

What I can’t figure out how to do is filter the Google Sheet by email address before sending. Does anyone have experience with this?

Here is my script 
function sendDataExports() {  
  
  /*
   * Get and Prepare Sheet Variables
   */
  var googleSheetID = SpreadsheetApp.getActiveSpreadsheet().getId();
  
  var trackingSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("User Data Export");
  var trackingSheetID = trackingSheet.getSheetId();
  
  var exportSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Log Entries");
  var exportSheetID = exportSheet.getSheetId();
  
  /*
   * Get Tracking Sheet Rows
   */
  var firstRow = 2;
  var lastRow = trackingSheet.getLastRow();
  var lastColumn = trackingSheet.getLastColumn();
  var range = trackingSheet.getRange(firstRow, 1, lastRow - 1, lastColumn);  
  var rows = range.getValues();
  
  /*
   * For Each Row in Tracking Sheet
   */
  for (var i = 0; i < rows.length; ++i) {
    var row = rows[i];
    
    /*
     * If Email is Not Sent, Send Email
     */
    var emailSent = row[3];
    var EMAIL_SENT = 'EMAIL_SENT';
    if (emailSent !== EMAIL_SENT) {
      
      var date = new Date();
      var prettyDate = Utilities.formatDate(date, "GMT-08", "MMM dd, yyyy' 'HH:mm");
      var systemDate = Utilities.formatDate(date, "GMT-08", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
      
      /*
       * Create CSV
       */
      var requestData = {"method": "GET", "headers":{"Authorization":"Bearer " + ScriptApp.getOAuthToken()}};
	  var url = "https://docs.google.com/spreadsheets/d/" + googleSheetID + "/export?format=csv&gid=" + exportSheetID;
	  var result = UrlFetchApp.fetch(url, requestData);
	  var contents = result.getContent();
      
      /*
       * Create Email
       */
      var emailAddress = row[0];
      var emailSubject = "Your Data has arrived! " + prettyDate;
      var emailBody = "Thanks for using the app! Your export is attached.";
      var emailParameters = {
        name: "Glide Exports", 
        noReply: true,
        attachments:[
          {
            fileName: "export" + systemDate + ".csv",
            content: contents,
            mimeType: "application//.csv"
          }
        ]
      };
      
      /*
       * Send Email
       */
      MailApp.sendEmail(emailAddress, emailSubject, emailBody, emailParameters);
      
      /*
       * Mark Export as Sent in Tracking Sheet
       */
      trackingSheet.getRange(firstRow + i, 3).setValue(systemDate);
      trackingSheet.getRange(firstRow + i, 4).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
    }
  }
}
2 Likes