How to Automatically Send Google Sheets Data as an HTML Email using Apps Script

Google Sheets is excellent for collecting and organizing data, but sharing that data effectively often requires converting it into a readable format. While you can send a link to the spreadsheet, using Google Apps Script to extract the data and send it as a beautifully formatted HTML email provides a much better experience for the recipient.

Why Use HTML Emails?

Standard plain-text emails can make tabular data difficult to read. By writing a custom Google Apps Script, you can pull rows directly from your Google Sheet, inject them into an HTML table structure, and send the email automatically via Gmail.

How to Create the Apps Script

Follow these steps to extract data from your active sheet and email it:

1. Open your Google Sheet containing the data you want to send.
2. In the top menu, click Extensions > Apps Script.
3. Delete any existing code in the editor and paste the following script:

function sendDataAsHtmlEmail() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  
  if (data.length < 2) return; // Exit if there's no data beyond the header
  
  let htmlBody = '<h2>Weekly Data Report</h2>';
  htmlBody += '<table border="1" cellpadding="5" style="border-collapse: collapse;">';
  
  // Build the HTML table
  for (let i = 0; i < data.length; i++) {
    htmlBody += '<tr>';
    for (let j = 0; j < data[i].length; j++) {
      if (i === 0) {
        htmlBody += '<th style="background-color: #f2f2f2;">' + data[i][j] + '</th>';
      } else {
        htmlBody += '<td>' + data[i][j] + '</td>';
      }
    }
    htmlBody += '</tr>';
  }
  
  htmlBody += '</table>';
  htmlBody += '<p>This is an automated message generated from Google Sheets.</p>';
  
  // Send the email
  MailApp.sendEmail({
    to: "[email protected]",
    subject: "Automated Google Sheets Report",
    htmlBody: htmlBody
  });
}

4. Replace [email protected] with the actual destination email address.
5. Click the Save icon (floppy disk) and then click Run. You will be prompted to authorize the script to send emails on your behalf.

Automating the Email

To send this report on a schedule (e.g., every Monday morning):

1. In the Apps Script editor, click the Triggers icon (the clock symbol) on the left sidebar.
2. Click Add Trigger in the bottom right.
3. Set the function to sendDataAsHtmlEmail, select Time-driven as the event source, and configure your desired frequency.
4. Click Save.

Your Google Sheet will now independently format and distribute its data as a professional HTML email according to your schedule.