如何通过Apps Script的MailApp发送整张表格或指定单元格区域并保留复制粘贴样式？

Great question! Sending a specified range (like A1:J30) as a properly formatted table in an email via Apps Script is totally doable—here's how you can make it look just like you copied and pasted directly from Google Sheets:

The core trick here is converting your spreadsheet range into an HTML table. HTML emails let you render structured content with borders, padding, and styling that matches the sheet's look. Here's a step-by-step implementation:

Step 1: Fetch Your Target Range Data #

First, grab the values and optional formatting (like cell backgrounds) from the range you want to send:

function sendFormattedTableEmail() {
  // Replace with your sheet name and target range
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Your Sheet Name");
  const targetRange = sheet.getRange("A1:J30");
  
  // Get cell values and background colors (backgrounds are optional)
  const rangeValues = targetRange.getValues();
  const cellBackgrounds = targetRange.getBackgrounds();
}

Step 2: Build the HTML Table #

Next, construct an HTML table using the fetched data. Add basic styling to replicate the sheet's clean, structured appearance:

function sendFormattedTableEmail() {
  // ... [previous code] ...

  // Start building the HTML table with base styling
  let htmlTable = '<table style="border-collapse: collapse; width: 100%; font-family: Arial, sans-serif;">';
  
  // Loop through each row in the range
  rangeValues.forEach((row, rowIndex) => {
    htmlTable += '<tr>';
    // Loop through each cell in the row
    row.forEach((cell, colIndex) => {
      // Add cell with border, padding, and background color
      htmlTable += `<td style="border: 1px solid #ccc; padding: 8px; background-color: ${cellBackgrounds[rowIndex][colIndex]};">${cell || ''}</td>`;
    });
    htmlTable += '</tr>';
  });
  
  htmlTable += '</table>';
}

Step 3: Send the Email with HTML Content #

Finally, use MailApp.sendEmail() with the htmlBody parameter to deliver the formatted table. Include a plain-text fallback for email clients that don't support HTML:

function sendFormattedTableEmail() {
  // ... [previous code] ...

  // Email configuration
  const recipient = "your-recipient@example.com";
  const emailSubject = "Your Formatted Table from Google Sheets";
  // Plain-text fallback for non-HTML clients
  const plainTextBody = "Here's your table content:\n" + rangeValues.map(row => row.join('\t')).join('\n');

  // Send the email
  MailApp.sendEmail({
    to: recipient,
    subject: emailSubject,
    body: plainTextBody,
    htmlBody: `<p>Hi there,</p><p>Here's the table you requested:</p>${htmlTable}`
  });
}

Extra Tips for Polished Formatting #

• Highlight Headers: If your first row is a header, add special styling to make it stand out:

  rangeValues.forEach((row, rowIndex) => {
    htmlTable += '<tr>';
    row.forEach((cell, colIndex) => {
      const isHeaderRow = rowIndex === 0;
      const cellStyle = isHeaderRow 
        ? 'border: 1px solid #ccc; padding: 8px; background-color: #f0f0f0; font-weight: bold;' 
        : `border: 1px solid #ccc; padding: 8px; background-color: ${cellBackgrounds[rowIndex][colIndex]};`;
      htmlTable += `<td style="${cellStyle}">${cell || ''}</td>`;
    });
    htmlTable += '</tr>';
  });
  

• Handle Empty Cells: The cell || '' ensures empty cells don't show undefined in the email.
• Tweak Styling: Adjust the CSS in the style attributes to match your sheet's font, border thickness, or padding.

This method will make the email content look exactly like a direct copy-paste from your spreadsheet—clean, organized, and easy to read.

内容的提问来源于stack exchange，提问作者Mysteryquy