Email Google Sheets data based on a schedule

Here’s a step-by-step approach on how to create a Google Sheet and use Apps Script to send it as an email automatically every day two times, 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 schedule the email

• 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});
}

function scheduleSalesReport() {
  // Schedule the sendSalesReport function to run at 9am and 5pm every day
  ScriptApp.newTrigger('sendSalesReport')
      .timeBased()
      .onWeekDay(ScriptApp.WeekDay.MONDAY)
      .atHour(9)
      .create();
  ScriptApp.newTrigger('sendSalesReport')
      .timeBased()
      .onWeekDay(ScriptApp.WeekDay.MONDAY)
      .atHour(17)
      .create();
}

• Save the script file.
• Run the scheduleSalesReport() function by selecting “Run” > “scheduleSalesReport” 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 at the scheduled times.
• You can now update the data in the sheet, and the sales report will automatically be sent every day at 9am and 5pm with the updated data.

That’s it! You can now use this script to send a sales report with a chart as a PDF attachment via email at scheduled times every day.