Automate Report Generation with Google Apps Script — Add Data, Create a Report, and Email a PDF

📢 Introduction

Are you tired of manually generating monthly reports for your team or clients? Imagine clicking one button to automatically:

• Add Data to a Google Sheet
• Create a Google Doc Report with the live sheet data
• Embed a Dynamic Chart in the document
• Export the Document as a PDF
• Email the PDF to your inbox — all done automatically!

With Google Apps Script, you can achieve all this with a few lines of code. In this guide, we’ll walk you through how to set up this automation step-by-step.

---

🎉 What Will This Script Do?

This Google Apps Script does the following:

1. Add Sample Sales Data to a Google Sheet.
2. Create a New Google Doc for the report.
3. Insert a Data Table directly from the sheet into the Google Doc.
4. Generate a Dynamic Chart from the sheet’s data.
5. Embed the Chart as an Image in the Google Doc.
6. Export the Google Doc as a PDF.
7. Send the PDF Report via Email to the person who runs the script.

---

📘 Full Apps Script Code

Here is the complete script you need to get started. Follow the instructions below to add it to your Google Sheet.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('📊 Custom Report')
    .addItem('📄 Create Report as New Doc & Email PDF', 'createDocAndEmailPDF')
    .addToUi();
}

function AddData() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getActiveSheet();
  sheet.clear(); // Clear existing content
  const sampleData = [
    ['Month', 'Sales'],
    ['January', 1200],
    ['February', 1500],
    ['March', 1000],
    ['April', 1800],
    ['May', 2000],
    ['June', 1700],
    ['July', 2500],
    ['August', 2300],
    ['September', 1900],
    ['October', 2200],
    ['November', 2100],
    ['December', 2400]
  ];
  sheet.getRange(1, 1, sampleData.length, sampleData[0].length).setValues(sampleData);
}

function createDocAndEmailPDF() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getActiveSheet();
  const dataRange = sheet.getDataRange();
  const data = dataRange.getValues(); // Extract live data from the sheet
  
  const doc = DocumentApp.create('Monthly Sales Report');
  const docId = doc.getId();
  const body = doc.getBody();
  
  body.appendParagraph('📊 Monthly Sales Report').setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph('This document provides an overview of the monthly sales for the current year. Below you will find the detailed sales data along with a visual representation in the form of a chart.');
  
  body.appendParagraph('📋 Sales Data Table');
  body.appendTable(data); // Insert the live data directly from the spreadsheet
  
  // Step 5: Check if a chart exists, if not, create one
  let chartBlob;
  const charts = sheet.getCharts();
  
  if (charts.length > 0) {
    // Reuse the existing chart
    Logger.log('Chart already exists, reusing it.');
    chartBlob = charts[0].getAs('image/png');
  } else {
    // Create a new chart
    Logger.log('No chart found, creating a new one.');
    const chart = sheet.newChart()
      .setChartType(Charts.ChartType.COLUMN)
      .addRange(dataRange)
      .setPosition(5, 3, 0, 0)
      .setOption('title', 'Monthly Sales Report')
      .build();
    sheet.insertChart(chart);
    
    // Wait for the chart to render properly
    Utilities.sleep(2000);
    
    // Get the newly created chart
    const updatedCharts = sheet.getCharts();
    chartBlob = updatedCharts[0].getAs('image/png');
  }
  
  // Insert the chart image into the document
  body.appendParagraph('📈 Sales Chart');
  body.appendImage(chartBlob);
  
  // Save and close the document
  doc.saveAndClose();
  
  const pdfBlob = exportDocumentAsPDF(docId);
  
  const emailAddress = Session.getActiveUser().getEmail();
  const subject = 'Monthly Sales Report';
  const bodyMessage = 'Attached is your automatically generated Monthly Sales Report PDF.';
  
  MailApp.sendEmail({
    to: emailAddress,
    subject: subject,
    body: bodyMessage,
    attachments: [pdfBlob]
  });

  Logger.log('Email sent to ' + emailAddress);
}

function exportDocumentAsPDF(docId) {
  const url = `https://docs.google.com/document/d/${docId}/export?format=pdf`;

  const token = ScriptApp.getOAuthToken();
  const response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': `Bearer ${token}`
    }
  });

  const pdfBlob = response.getBlob().setName('Monthly_Sales_Report.pdf');
  return pdfBlob;
}

---

📘 Step-by-Step Instructions

1️⃣ Add the Script

1. Open Google Sheets.
2. Click Extensions → Apps Script.
3. Paste the code above into the script editor.
4. Click Save (💾) or press Ctrl + S.

2️⃣ Run the Script

1. Run the onOpen() function to create the custom menu.
2. Go back to your Google Sheet and refresh the page.

3️⃣ Use the Custom Menu

• Click 📊 Custom Report in the Google Sheet menu.
• Click 📄 Create Report as New Doc & Email PDF.
• The script will automatically generate and email the PDF report.

---

📘 Sample Data Table

Month	Sales
January	1200
February	1500
March	1000
April	1800
May	2000
June	1700
July	2500
August	2300
September	1900
October	2200
November	2100
December	2400

---

📘 Expected PDF Report

The PDF will have the following content:

• Title: 📊 Monthly Sales Report
• Introduction: Overview of the report’s purpose.
• Table: A live table with real-time data from the Google Sheet.
• Chart: A chart based on the live data from the sheet.

---

📘 Customization Options

1️⃣ Change Report Title

Change the title from Monthly Sales Report to something custom:

const doc = DocumentApp.create('Weekly Sales Report');

2️⃣ Customize Email

Change the email to be sent to someone else:

const emailAddress = 'manager@example.com';

3️⃣ Change Chart Type

Change the chart type from Charts.ChartType.COLUMN to LINE, PIE, BAR etc.:

.setChartType(Charts.ChartType.LINE)

---

📘 Key Features Recap

1. Dynamic Data: Uses live data from Google Sheets.
2. Automatic Charting: Inserts a chart based on the data.
3. Google Doc Export: Data and charts are embedded in a professional document.
4. Email PDF: Exports the document as a PDF and emails it.

---

📘 Use Cases

• Sales Reports: Automatically generate sales reports for clients or managers.
• Employee Reports: Email employee performance reports.
• Project Reports: Automatically generate a project status PDF.
• Data-Driven Marketing: Send performance reports to clients.

---

📘 Final Thoughts

This Google Apps Script provides a powerful, fully automated reporting system. It pulls live data, builds charts, and generates a professional document that’s emailed as a PDF. It’s perfect for generating weekly, monthly, or ad-hoc reports for managers, clients, or teams.