How to Automatically Email a Formatted Table from Google Sheets

Other Questions Google Sheets
1

I’m trying to set up an automated email that sends a formatted table from my Google Sheets every 5 days. The table has 25 rows and 2 columns. I know how to send emails using MailApp.sendEmail, but I’m stuck on getting the whole table into the email body correctly.

My current script only sends the first row instead of all 25 rows. Here’s what I want the email to look like:

A - 1
B - 2
C - 3
...
X - 24
Y - 25

I’ve tried using getRange and getValue, but it’s not working as expected. Can anyone help me figure out how to get all the data and format it nicely in the email? I’d really appreciate some guidance on this!

Also, if there’s a way to make it look more like a table in the email, that would be awesome. Thanks in advance for any help!

2

hey dancingbird, i think i got a solution for ya. try using getRange().getValues() to grab all the data, then loop through it to build an html table. something like:

var data = sheet.getRange('A1:B25').getValues();
var html = '<table>';
for (var i = 0; i < data.length; i++) {
  html += '<tr><td>' + data[i][0] + '</td><td>' + data[i][1] + '</td></tr>';
}
html += '</table>';

then use that html in your mailapp.sendemail. hope this helps!

3

Here’s a solution that should work for you:

Use getRange(‘A1:B25’).getValues() to grab all your data at once. Then, you can loop through this 2D array to build your email content. Something like:

var data = sheet.getRange(‘A1:B25’).getValues();
var emailBody = ‘’;
for (var i = 0; i < data.length; i++) {
emailBody += data[i][0] + ’ - ’ + data[i][1] + ‘\n’;
}

MailApp.sendEmail(recipient, subject, emailBody);

This will format your data as you requested. For a more table-like appearance, you could use HTML formatting in your email. Just set the htmlBody parameter in sendEmail instead of body, and construct your content as an HTML table.

To automate every 5 days, set up a time-driven trigger in the Script Editor. Hope this helps!

4

I’ve been using a similar setup for my business reports, and I can share what worked well for me. Instead of sending plain text, I found that using HTML to create a proper table looks much more professional in the email. Here’s a snippet of code that might help:

function sendTableEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange('A1:B25');
  var data = range.getValues();
  
  var htmlBody = '<table style=\"border-collapse: collapse; width: 100%;\">';
  for (var i = 0; i < data.length; i++) {
    htmlBody += '<tr><td style=\"border: 1px solid #ddd; padding: 8px;\">' + 
                data[i][0] + '</td><td style=\"border: 1px solid #ddd; padding: 8px;\">' + 
                data[i][1] + '</td></tr>';
  }
  htmlBody += '</table>';

  MailApp.sendEmail({
    to: '[email protected]',
    subject: 'Automated Table Report',
    htmlBody: htmlBody
  });
}

This creates a nice-looking table in the email. For the 5-day automation, set up a time-driven trigger in the script editor. It’s been reliable for me so far. Hope this helps!