Send Google Sheet data as a pdf 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 as a PDF attachment, 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”.
• 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 that you want to create, such as a bar chart 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 url = sheet.getUrl();
  var sheetId = sheet.getSheetId();
  var pdf = UrlFetchApp.fetch('https://docs.google.com/spreadsheets/d/' + url.substring(url.indexOf('/d/')+3, url.indexOf('/edit')) + '/export?exportFormat=pdf&gid=' + sheetId, {
    headers: {
      Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
    }
  }).getBlob();

  // Send email with PDF attachment
  var email = Session.getActiveUser().getEmail();
  var subject = 'Sales Report';
  var message = 'Attached is the latest sales report.';
  var attachments = [{fileName: 'sales_report.pdf', content: pdf}];
  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 a PDF 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 a PDF attachment via email.