Send Google Sheet data in an email

Here’s a step-by-step approach on how to create a Google Sheet and use Apps Script to send it as an email, including steps to create a graph in the spreadsheet:

Step 1: Setup Google Sheet to export

• Create a new Google Sheet by going to Google Drive and selecting “New” > “Google Sheets”. (or go to https://sheets.new)
• Rename the sheet to something descriptive like “Sales Data”.
• Add some sample data to the sheet, including column headings for “Date”, “Product”, “Sales”, and “Expenses”.
• Highlight the data that you want to include in the graph.
• Select “Insert” > “Chart” from the Google Sheets menu.
• Choose the type of chart you want to create, such as a bar or line chart.
• Customize the chart as desired using the options in the Chart Editor.
• Once the chart is created, you can move it to a separate sheet or embed it in a dashboard if desired.

Step 2: Write Apps Script to export the Sheet

• Open the Apps Script editor by selecting “Tools” > “Script editor” from the Google Sheets menu.
• In the Apps Script editor, create a new script file by selecting “File” > “New” > “Script file”.
• Name the script file something descriptive like “SendSalesReport”.
• Copy and paste the following code into the script file:

function sendSalesReport() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sales Data'); // Replace 'Sales Data' with the name of your sheet
  var chart = sheet.getCharts()[0]; // Assumes chart is the first chart in the sheet
  var chartBlob = chart.getAs('image/png');

  // Send email with chart as attachment
  var email = Session.getActiveUser().getEmail();
  var subject = 'Sales Report';
  var message = 'Attached is the latest sales report.';
  var attachments = [{fileName: 'sales_report.png', contentBytes: chartBlob.getBytes(), mimeType: chartBlob.getContentType()}];
  GmailApp.sendEmail(email, subject, message, {attachments: attachments});
}

• Save the script file.
• Test the sendSalesReport() function by selecting “Run” > “sendSalesReport” from the menu in the script editor.
• Grant the necessary permissions when prompted.
• Check your email to ensure the sales report was sent as an attachment with the chart included.
• You can now set up a time-driven trigger to run the sendSalesReport() function regularly, such as weekly or monthly.

That’s it! You can now use this script to send a sales report with a chart included as an attachment via email.