Send Your Data As a CSV Attachment in an Email

1. Configure Datasources

   Connect Data (Ignore if already done)

   • Navigate to Datasources in Appsmith.
   • Integrate your API or database, start querying your data, and connect it to your widgets.

   SMTP Datasource

   • From the Datasources integration page, click on SMTP.
   • Configure your SMTP server. Refer to the SMTP configuration documentation for detailed instructions.

2. Convert Data to CSV

   Create a JSObject function to convert your JSON array (queried from your API or database) to a CSV format.

   /* Function to convert JSON Data to CSV using native Javascript */
   	arrayToCSV(data) {
   		const headers = Object.keys(data[0]);
   		let csv = headers.join(',') + `\n`;
   		csv += data.map(obj => {
   			return headers.map(key => {
   				const value = obj[key];
   				if (typeof value === 'string' && value.includes(',')) {
   					return `"${value}"`;
   				}
   				return value;
   			}).join(',');
   		}).join(`\n`);
   		return csv;
   	}

   Alternatively, you can use the PapaParse library for this conversion:

   1. Add PapaParse to your external libraries. Follow the external libraries documentation for installation.
   2. Install PapaParse using the jsDelivr link: PapaParse jsDelivr
   3. Use Papa.unparse(data) to convert your data to CSV.

   At this point, you will have a variable containing your CSV data returned by using either of these above functions.

3. Encode CSV to Base64 Format

   To send your CSV as a file attachment, your Email Query accepts it only in base64 format.
   You can use the code btoa(csvData), to convert your CSV data to a base64 encoded value.

   Handling Non-Latin-1 Characters

   If your data includes characters outside the Latin-1 character set, base64 encoding may fail. To handle this, create a JSObject called Utils and add a function to escape these characters.

   escapeOtherUnicodeCharacters(str) {
   		// Convert the string to an array of characters
   		return Array.from(str)
   		// Map each character to its Unicode code point
   			.map(char => {
   			const codePoint = char.codePointAt(0);
   			// If the character is outside the Latin-1 range, escape it
   			if (codePoint > 255) {
   				return `\\u${codePoint.toString(16).padStart(4, '0')}`;
   			} else {
   				return char;
   			}
   		}).join('');
   	}

   You should identify which all columns in your data can have this type of characters and run the function for them.
   In my App, I had identified the parameter name in my dataset to contain such characters, and wrote a .map function to loop through the data records and escape the string by calling Utils.escapeOtherUnicodeCharacters on it.

   let data = getAllUsers.data.map(function(item) {
   	return  {
   		...item,
   		name: Utils.escapeOtherUnicodeCharacters(item.name || "")
   	}
   });

4. Function to trigger the Send Email Query

   Create a JSObject called Actions to combine all the above steps and call the Send Email Query.

   export default {
   	/* Convert the Data to CSV file */
   	exportAllData() {
   		let data = getAllUsers.data.map(function(item) {
   			return  {
   				...item,
   				name: Utils.escapeOtherUnicodeCharacters(item.name || "")
   			}
   		});
   		let csvData = Utils.arrayToCSV(data); // You can either use a native Javascript function
   		csvData = Papa.unparse(data); // Or use PapaParse library
   		return csvData;
   	},
   	/* Run the Send Email Query */
   	async sendEmail() {
   		let csvData = this.exportAllData();
   		let base64Data = btoa(csvData);
   		await sendEmailData.run({ base64Data })
   			.then(function() {
   			showAlert("User Data CSV file successfully sent to your email: " + appsmith.user.email, "success")
   		})
   			.catch(function(error)  {
   			showAlert("Error occurred while sending email: sendEmailData.data" + , "error")
   		})
   	}
   }

   Here, sendEmailData is the query created on your SMTP datasource, and base64Data is passed as an input.

5. SMTP Email Query

   Configure your email query as below to send the email body in HTML format for a customized and themed email.

   

   HTML Template
   Below you can see the HTML I am using included with styling from top CSS libraries like bootstrap in addition to the style sheet I have added. The HTML can also have dynamic content, like data bindings embedded inside from the widgets or JSObject functions. I am using one of my JS functions Actions.getDashboardData() to dynamically retrieve certain data sets from my User Data and set them as <td> (or  Table Data cell elements).

   <!DOCTYPE html>
   <html>
   <head>
       <meta charset="UTF-8">
       <title>User Data Dashboard</title>
       <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
       <style>
           body {
               font-family: Arial, sans-serif;
               line-height: 1.6;
               background-color: #f8f9fa;
               padding: 20px;
           }
           .container {
               max-width: 800px;
               margin: auto;
               background-color: #ffffff;
               padding: 30px;
               border: 1px solid #dddddd;
               border-radius: 5px;
               box-shadow: 0px 0px 10px 0px rgba(0,0,0,0.1);
           }
           h1, h2 {
               color: #007bff;
               margin-bottom: 20px;
           }
           p {
               margin-bottom: 20px;
           }
           table {
               width: 100%;
               border-collapse: collapse;
               margin-bottom: 20px;
           }
           th, td {
               border: 1px solid #ccc;
               padding: 8px;
               text-align: left;
           }
           th {
               background-color: #f2f2f2;
           }
       </style>
   </head>
   <body>
       <div class="container">
           <h1 class="mb-4">User Data Dashboard</h1>
           <p class="mb-4">Summary of user records:</p>
   
           <h2>Country Distribution</h2>
           <table class="table table-striped">
               <thead class="thead-light">
                   <tr>
                       <th>Country</th>
                       <th>Number of Users</th>
                   </tr>
               </thead>
               <tbody>
                   {{Actions.getDashboardData().countryChartData.map(item => `<tr><td>${item.name}</td><td>${item.value}</td></tr>`).join("")}}
               </tbody>
           </table>
   
           <h2>Age Distribution</h2>
           <table class="table table-striped">
               <thead class="thead-light">
                   <tr>
                       <th>Age Range</th>
                       <th>Number of Users</th>
                   </tr>
               </thead>
               <tbody>
                   {{Actions.getDashboardData().ageChartData.map(item => `<tr><td>${item.name}</td><td>${item.value}</td></tr>`).join("")}}
               </tbody>
           </table>
       </div>
   </body>
   </html>

   Attachments

   In the Attachments field, send an array where each object is configured with the necessary parameters. Here’s an example configuration:

   {{[{
     "type": "text/csv",
     "data": `data:text/csv;base64,${this.params.base64Data}`,
     "name": `User Data.csv`,
     "size": this.params.base64Data.length,
     "dataFormat": "Base64"
   }]}}

   In this example, this.params.base64Data references the base64 data passed from the sendEmailData query.

6. Run the function to Send Email

   Finally, add a button to your Appsmith interface. Drag and drop a button widget, then configure its onClick event to trigger the sendEmail function we set up earlier.

    

   Now, deploy your app. By clicking this button, you will initiate the process of sending an email with the CSV attachment to your users.

Conclusion

Additional Resources

• Using SMTP
• Add Attachments