Export Google Sheets to pdf and send over Google chat

Situation

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.

Complication

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.

Solution

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:

Google Sheets
Gmail
Google Drive
Google Apps Script

Stages:

Export sheet in PDF format
Save the PDF to Google drive
Move the pdf file into specified folder
Send pdf file as attachment on email [Optional]
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
var DESTINATION_FOLDER_ID = "1V6Na3IgQEV0u1Y7Br26PmAMXTJCM9876"

// 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
  sendPDFEmail(my_pdf);

  // send chat message
  sendPDFGoogleChat();
}


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
    my_file.moveTo(DriveApp.getFolderById(DESTINATION_FOLDER_ID));

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

MarquinSmith