Export CSV and email to user

Hi all - I am building a variation of a CRM and would love my users to be able to export a subset of data as a CSV and have it emailed to them. I’ve checked Zapier and can’t find anything that would achieve this. If anyone has ideas, would love to hear!

Hi Amanda,

What if you create a Template Column and put there all your variables/data using the syntax you will need. Later, use Zapier and send that Template Column as Value #1 as the attachment of your email .

This should work sure!

Saludos y feliz día!

2 Likes

Thank you! I am pretty new to Glide so will look into how templates work! What Zap/App connections would you use to create/send an attachment?

Thanks for your help!

Hi Amanda,

After signing up for Zapier, navigate to: https://zapier.com/apps/integrations

In your case, I think this Zap would suit:

image

Follow the instructions, basically when the designated row in Google Sheets is updated, an email will be sent to emails you want.

The subjects and content of email can be linked to values in the Google Sheets as well.

Try it out!

Thank you!! What I am trying to achieve is… allowing users to add potential contacts to a project. The contacts will be a list of say 10-30 names, emails, addresses, etc. I would like to have a button that allows the user to say “Export CSV of contacts”, and have an email sent to them with all the contacts, and their relevant info in a CSV attachment. Do you think what you are mentioning above could work for this usecase?

1 Like

Hi, just my thoughts, actually I have a use case for this in one of my apps as well but I haven’t implemented it so if it works for you, hopefully I can do it as well.

My case is a sports news app, for Vietnamese newspaper editors. My idea is that they can access my app, check for the latest English news, click “check” for specific news they want to translate, then click a button to have all those links send to them via email.

I think this can be achieved by:

  • Setting up a user-specific check column for each contact in your app. If a signed-in user checks a column, the checkbox becomes TRUE for that specific user.
  • Use a QUERY formula to take all information from the rows that were checked into a new sheet, then CONCATENATE them like what Jeff proposed into a single cell.
  • Set up a Zap to send that cell’s value through the mail to the user.

If you want to send a CSV instead of a cell value, I’m not sure Zap can handle that natively. I searched and found that Integromat can do that: https://www.integromat.com/en/integrations/csv/zapier

This is a complex problem and I hope other people can chime in with their ideas.

1 Like

thank you! I’ll let you know how far I get on this. This sounds like a great approach and will keep you posted on the results!

Thanks for finding this CSV zap - will look into this

1 Like

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();
    }
  }
}
1 Like

I would include the users email address in your tracking sheet, and then use that to filter.
So instead of mailing the whole sheet, create another temporary sheet with just that users data, and mail that.

In terms of doing the filtering in a script, you can just iterate over each row in your data sheet, skipping any rows where the email address doesn’t match. For those that do match, you copy the data in that row to your temporary sheet.

Make sense?

I got it! :tada::tada::tada:

Thanks for the suggestions @Darren_Murphy!

User’s can get an email with a CSV export of their data by clicking a button in the app.

I have a tracker table in my Google sheet

Users can request an export via a button click in the app

The button click triggers an action that adds their email and the date requested to this sheet.

I created a Google App Script to do the rest

  • Look for rows without ‘EMAIL_SENT’
  • Create a new Google Sheet in Google Drive
  • Copy the data from the table I want to export into the New Google Sheet
  • Filter the Data by the Requesting User’s Email
  • Send the User an Email with a .csv export attached
  • Puts the New/Temporary Google Sheet into Google Drive trash
  • Mark the row as ‘EMAIL_SENT’

Warning: Real code incoming… :grin:

Here is the big beautiful script
function sendFilteredDataExports() {  
  
  /*
   * Get and Prepare My Google SpreadSheet Variables
   */
  var googleSS = SpreadsheetApp.getActive();
  var googleSSID = 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) {
      
      /*
       * Set Current Date
       */
      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'");
      
      /*
       * Get email address of user who requested export
       */
      var emailAddress = row[0];
      
      /*
       * Create temporary Google Sheet, Copy over data to export, and Remove any existing Filters
       */
      var tempGoogleSS = SpreadsheetApp.create(systemDate + " Export for " + emailAddress);
      var tempGoogleSSID = tempGoogleSS.getId();
      var exportSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Log Entries");
      exportSheet.copyTo(tempGoogleSS);
      exportSheet = tempGoogleSS.getSheetByName("Copy of Log Entries");
      exportSheet.getFilter().remove();
      
      /*
       * Create a new sheet in the temporary Google Sheet to write the filtered data to
       */
      var tempFilteredSheet = tempGoogleSS.insertSheet("Sheet to Export");
      var tempFilteredSheetID = tempFilteredSheet.getSheetId();
      var tempRange = tempFilteredSheet.getDataRange();
      var tempCurrentRow = 0;
      
      /*
       * For each row in the unfiltered sheet
       */
      var exportRange = exportSheet.getDataRange();
      var exportRows = exportRange.getValues();
      var lastColumn = exportSheet.getLastColumn();
      for (var j = 1; j < exportRows.length; j++) {
        var row = exportRows[j - 1];
        var exportRow = exportSheet.getRange(j, 3, 1, lastColumn);
        
        /*
         * Copy the row to the filtered sheet if it is the header row or the row's email matches the user's email
         */
        var rowEmail = row[2];
        if(rowEmail === emailAddress || j === 1) {
          var targetRowNum = tempFilteredSheet.getLastRow();
          tempFilteredSheet.insertRowAfter(targetRowNum + 1);
          var targetRow = tempFilteredSheet.getRange(targetRowNum + 1, 1, 1, lastColumn - 2);
          exportRow.copyTo(targetRow);
        }
      }
      
      /*
       * Create CSV
       */
      var requestData = {"method": "GET", "headers":{"Authorization":"Bearer " + ScriptApp.getOAuthToken()}};
	  var url = "https://docs.google.com/spreadsheets/d/" + tempGoogleSSID + "/export?format=csv&gid=" + tempFilteredSheetID;
	  var result = UrlFetchApp.fetch(url, requestData);
	  var contents = result.getContent();
      
      /*
       * Create Email
       */
      var emailSubject = "Your Data has arrived! " + prettyDate;
      var emailBody = "Thanks for using the app! Your export is attached.";
      var emailParameters = {
        name: "App", 
        noReply: true,
        attachments:[
          {
            fileName: "export" + systemDate + ".csv",
            content: contents,
            mimeType: "application//.csv"
          }
        ]
      };
      
      /*
       * Send Email
       */
      MailApp.sendEmail(emailAddress, emailSubject, emailBody, emailParameters);
      
      /*
       * Delete the temporary sheet
       */
      DriveApp.getFileById(tempGoogleSSID).setTrashed(true);
      
      /*
       * Mark Export as Sent in Tracking Sheet
       */
      trackingSheet.getRange(firstRow + i, 3).setValue(systemDate);
      trackingSheet.getRange(firstRow + i, 4).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
    }
  }
}

The script is triggered by a Google App Script Trigger

6 Likes

Nice job!
Glad you got it working.

My try for exporting a CSV of signed-in user’s data, using Query & Zapier.

1 Like

This is awesome stuff! I’m going to try to use this in one of my apps — I currently have multiple query sheets configured and give certain people access to the sheet in Google… this would make things a lot easier as it’s all handled in the app and I don’t need to worry about who has access to the query sheets.

1 Like

Amazing!

1 Like