Googlesheets
No Responses June 3, 2025

How I Automated a Daily PDF Report From Google Sheets (And Emailed It Out Automatically)

This used to be my routine:

Open Google Sheet → filter today’s data → format → download as PDF → open Gmail → attach → send.

Not anymore.

I got tired of doing the same thing every morning. So I wrote a small Google Apps Script that pulls today’s rows from the sheet, formats it into a clean table, generates a PDF, and emails it out — automatically.

Here’s how I did it.

📊 The Sheet

My sheet is called "Item Issue" and looks like this:

Item Issue Id | Date | Item Id | Item | Item Category | Quantity | Remarks | Item Category Id

Every row is a record of something issued on a date.

For the report, I only need:

  • Item Issue Id
  • Item
  • Item Category
  • Quantity

And I only want rows where Date = today.

🎯 Workflow I automated?

  • Filter only today’s rows
  • Format into a clean table
  • Add serial numbers
  • Generate PDF
  • Send it as an email attachment

All of it automated. No clicking.

🧠 The Script

Go to your sheet → Extensions → Apps Script → delete whatever is there, and paste this:

function generateTodayItemIssuePDF() {
  const SHEET_NAME = "Item Issue";
  const RECIPIENT_EMAIL = "you@example.com"; // Replace with your email
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");
  const displayDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yyyy");
  const dateCol = headers.indexOf("Date");
  const itemIdCol = headers.indexOf("Item Id");
  const todayRows = data.slice(1).filter(row => {
    const d = row[dateCol];
    return d instanceof Date &&
           Utilities.formatDate(d, Session.getScriptTimeZone(), "yyyy-MM-dd") === today;
  });
  if (todayRows.length === 0) {
    Logger.log("No entries for today.");
    return;
  }
  todayRows.sort((a, b) => String(a[itemIdCol]).localeCompare(String(b[itemIdCol])));
  const cols = {
    issueId: headers.indexOf("Item Issue Id"),
    item: headers.indexOf("Item"),
    category: headers.indexOf("Item Category"),
    qty: headers.indexOf("Quantity")
  };
  const tableRows = todayRows.map((row, i) => `
    <tr>
      <td>${i + 1}</td>
      <td>${row[cols.issueId]}</td>
      <td>${row[cols.item]}</td>
      <td>${row[cols.category]}</td>
      <td>${row[cols.qty]}</td>
    </tr>
  `).join("");
  const html = `
    <html>
    <head>
      <style>
        body { font-family: Arial, sans-serif; font-size: 12px; padding: 20px; }
        h2 { text-align: center; }
        table { width: 100%; border-collapse: collapse; margin-top: 20px; }
        th, td { border: 1px solid #888; padding: 8px; text-align: center; }
        th { background-color: #f6b26b; }
      </style>
    </head>
    <body>
      <h2>Item Issue Report - ${displayDate}</h2>
      <table>
        <tr>
          <th>S.No</th>
          <th>Item Issue Id</th>
          <th>Item</th>
          <th>Item Category</th>
          <th>Quantity</th>
        </tr>
        ${tableRows}
      </table>
    </body>
    </html>
  `;
  const blob = Utilities.newBlob(html, 'text/html').getAs(MimeType.PDF);
  blob.setName(`Item_Issue_Report_${today}.pdf`);
  MailApp.sendEmail({
    to: RECIPIENT_EMAIL,
    subject: `Item Issue Report - ${displayDate}`,
    body: `Hi,\n\nPlease find attached the item issue report for ${displayDate}.\n\nRegards,\nYour Automation Bot`,
    attachments: [blob]
  });
  Logger.log("Email sent to " + RECIPIENT_EMAIL);
}

⚙️ Automate It

If you want this to run automatically every morning:

  1. Go to the Triggers tab (⏰ icon in Apps Script)
  2. Click + Add Trigger
  3. Choose:
  • Function: generateTodayItemIssuePDF
  • Event source: Time-driven
  • Type: Day timer → 8am (or whenever you want)

Now it runs daily. No reminders. No formatting. No attachments to drag manually.

✅ Conclusion

This script now runs every day and sends me a fresh PDF — clean, dated, and ready to file or forward.

No more opening sheets, no more manual exports.

If you’re doing any kind of repetitive reporting in Google Sheets, this will save you hours every month. Build it once, forget about it.