Export Google Sheets to pdf and send over Google chat


A beautiful report has been created in Google Sheets.

The spreadsheet is exported to a pdf format and circulated with the relevant people via a Google Chat space.


The export to pdf and distribution to the Google Chat is a manual process. This takes up time which is better spent elsewhere.

There is no value in the distribution of this report requiring a human in the loop.


Automate the entire process of exporting the sheet as a pdf and sending on Google chat.

The automation can be achieved using Google Apps script. (See resources here if Google Apps Script is new to you)

Services used:

  1. Google Sheets
  2. Gmail
  3. Google Drive
  4. Google Apps Script


  1. Export sheet in PDF format
  2. Save the PDF to Google drive
  3. Move the pdf file into specified folder
  4. Send pdf file as attachment on email [Optional]
  5. Send link to pdf file in a message on Google Chat

Below is the code which executes these steps using Google Apps Script. This code will work when it is in the Google Apps Script project linked to the spreadsheet that is being exported to pdf.

// set global variables for use throughout script

// dates
var my_now = new Date();
var my_month = my_now.getMonth() + 1;
var my_date = my_now.getFullYear() + "-" + my_month + "-" + my_now.getDate();

// The Google Drive file ID of the pdf file that will be created in this script
var pdfFileId = ""; 
// the name of the pdf to be created
var pdfName = "LookerStudio Dashboard Engagement " + my_date + ".pdf";
// the folder id where the pdf will be saved on Google drive

// this is the webhook for the Google chat space to send a message into
// change this line to send a message into another Google chat space
var WebWhooklink = "https://chat.googleapis.com/v1/spaces/AAAAnX1yKek/messages?key=AIzaSyDdI0hCZtE6vySjMm-WEfRq3CPzqKqqsHI&token=hUnvSmKAiZySJD8QR6z029Q7E_ktWgPtakh87Ph";

// # Email Settings
var emailRecipient = "marquin@marquinsmith.com";
var emailSubject = "PDF Export ::: " + my_date;
var emailMessage = "Here's the PDF export of the sheet.";

function mainDriver() {
  var my_pdf = exportSheetToPDF();

  // send email

  // send chat message

function exportSheetToPDF() {
    // google apps script to send the contents of the "main"
    // tab into a google chat space
    // https://docs.google.com/spreadsheets/d/1d1NGC9ZLC-Z7IfKFECi8HYdjbWPCqvRw6BORUqQupoi
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    // set the tab to be the "main" tab
    // this is the only tab not hidden on the spreadsheet
    // and the only one needing to be in pdf format
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName("main"), true);
    var sheet = spreadsheet.getActiveSheet();
    var blob = spreadsheet.getBlob();
    // Define the PDF export options
    var pdfOptions = {
      "size": "A3",
      "portrait": true,
      "format": "PDF",
      "exportUrlOptions": {
        "format": "pdf",
        "size": "A3",
        "portrait": true
    // Export the sheet to a PDF file
    var pdfFile = DriveApp.getFileById(spreadsheet.getId()).getAs("application/pdf").setName(pdfName);
    Logger.log("Created the following pdf file: " + pdfName);

    // Save the PDF to Google Drive
    var my_file = DriveApp.createFile(pdfFile);
    // get file id
    pdfFileId = my_file.getId();

    // Move the file to the destination folder

    return pdfFile

function sendPDFEmail(pdfFile) {

    // Send an email with the PDF attachment (optional)
    MailApp.sendEmail(emailRecipient, emailSubject, emailMessage, {
       attachments: [pdfFile]
function sendPDFGoogleChat() { 
    // sends a message into the "Marquin Test" google chat space

    var message = {
    text: "Dashboard engagement report for the last 7 days: https://drive.google.com/file/d/" + pdfFileId + "/view"
    var payload = JSON.stringify(message);
    var options = {
            method: "POST",
            contentType: "application/json",
            payload: payload

    var response =  UrlFetchApp.fetch(WebWhooklink, options ).getContentText();

Send a Comment

Your email address will not be published.