CODEWITHSUNDEEP
pdfgoogle-apps-scriptgoogle-sheets
RustyTones
RustyTones

Reputation: 69

setName when exporting a PDF from Google Sheets with Google Apps Script

I have this script to export three separate sheets as PDF's using Google Apps Script which works fine except for the fact that the PDF's are exported with the file name of export.pdf in each case. I would like to rename them to 'Yesterday', 'Last 7 Days' and 'Last 30 Days' respectively, can anyone help me to achieve this, please?

//Menu in Google Sheet

function onOpen() {

 var ui = SpreadsheetApp.getUi();

 ui.createMenu('Dashboard')

   .addItem('Email Dashboard','emailDashboard')

   .addToUi();

}

//Convert dashboard to PDF and email a copy to user
function emailDashboard() {

 // get sheet id

 var ss = SpreadsheetApp.getActiveSpreadsheet();

 var id = ss.getId();

 // setup sheets
 var yesterdaySheet = ss.getSheetByName('Yesterday');
 var last7DaysSheet = ss.getSheetByName('Last 7 Days');
 var last30DaysSheet = ss.getSheetByName('Last 30 Days');
 var settingsSheet = ss.getSheetByName('Settings');
 var dashboardURL = ss.getUrl() + "?usp=sharing";

 // Send the PDF of the spreadsheet to this email address
 // get this from the settings sheet
 var email = settingsSheet.getRange(8,2).getValue();
 var cc_email = settingsSheet.getRange(9,2).getValue();
 var bcc_email = settingsSheet.getRange(10,2).getValue();

 // Subject of email message
 var subject = "Dashboard PDF generated from " + ss.getName() + " - " + new Date().toLocaleString(); 

 // Email Body
 var body = `A pdf copy of your dashboard is attached.<br><br>

  To access this Google Sheet,; 
   <a href="` + dashboardURL + `" >click here</a>`;


 // Base URL
 var url = "https://docs.google.com/spreadsheets/d/" + id + "/export?";
 var url_ext = 'exportFormat=pdf&format=pdf&size=A4&portrait=false&fitw=true&gid=';
  
 // Auth value
 var token = ScriptApp.getOAuthToken();

 var options = { 

  headers: { 'Authorization': 'Bearer ' + token }

   }

 // helps initialize first time using the script
 var driveCall = DriveApp.getRootFolder();  

 // create the pdf
 var responseYesterday = UrlFetchApp.fetch(url + url_ext + yesterdaySheet.getSheetId(), options);
 var response7Days = UrlFetchApp.fetch(url + url_ext + last7DaysSheet.getSheetId(), options);
 var response30Days = UrlFetchApp.fetch(url + url_ext + last30DaysSheet.getSheetId(), options);
 

 // send the email with the PDF attachment
 GmailApp.sendEmail(email, subject, body, {
  cc: cc_email,
  bcc: bcc_email,
  htmlBody: body,
  attachments:[responseYesterday,response7Days, response30Days]   
 });
}

Upvotes: 1

Views: 1633

Answers (1)

Marios
Marios

Reputation: 27380

Explanation:

Just add these three lines after the pdf creation part:

var blob_Y = responseYesterday.getBlob().setName('Yesterday' + '.pdf');
var blob_L7D = response7Days.getBlob().setName('Last 7 Days' + '.pdf');
var blob_L30D = response30Days.getBlob().setName('Last 30 Days' + '.pdf');

and then change this part:

 GmailApp.sendEmail(email, subject, body, {
  cc: cc_email,
  bcc: bcc_email,
  htmlBody: body,
  attachments:[blob_Y, blob_L7D, blob_L30D ]   
 });

Solution:

//Menu in Google Sheet

function onOpen() {

 var ui = SpreadsheetApp.getUi();

 ui.createMenu('Dashboard')

   .addItem('Email Dashboard','emailDashboard')

   .addToUi();

}

//Convert dashboard to PDF and email a copy to user
function emailDashboard() {

 // get sheet id

 var ss = SpreadsheetApp.getActiveSpreadsheet();

 var id = ss.getId();

 // setup sheets
 var yesterdaySheet = ss.getSheetByName('Yesterday');
 var last7DaysSheet = ss.getSheetByName('Last 7 Days');
 var last30DaysSheet = ss.getSheetByName('Last 30 Days');
 var settingsSheet = ss.getSheetByName('Settings');
 var dashboardURL = ss.getUrl() + "?usp=sharing";

 // Send the PDF of the spreadsheet to this email address
 // get this from the settings sheet
 var email = settingsSheet.getRange(8,2).getValue();
 var cc_email = settingsSheet.getRange(9,2).getValue();
 var bcc_email = settingsSheet.getRange(10,2).getValue();

 // Subject of email message
 var subject = "Dashboard PDF generated from " + ss.getName() + " - " + new Date().toLocaleString(); 

 // Email Body
 var body = `A pdf copy of your dashboard is attached.<br><br>

  To access this Google Sheet,; 
   <a href="` + dashboardURL + `" >click here</a>`;


 // Base URL
 var url = "https://docs.google.com/spreadsheets/d/" + id + "/export?";
 var url_ext = 'exportFormat=pdf&format=pdf&size=A4&portrait=false&fitw=true&gid=';
  
 // Auth value
 var token = ScriptApp.getOAuthToken();

 var options = { 

  headers: { 'Authorization': 'Bearer ' + token }

   }

 // helps initialize first time using the script
 var driveCall = DriveApp.getRootFolder();  

 // create the pdf
 var responseYesterday = UrlFetchApp.fetch(url + url_ext + yesterdaySheet.getSheetId(), options);
 var response7Days = UrlFetchApp.fetch(url + url_ext + last7DaysSheet.getSheetId(), options);
 var response30Days = UrlFetchApp.fetch(url + url_ext + last30DaysSheet.getSheetId(), options);

 var blob_Y = responseYesterday.getBlob().setName('Yesterday' + '.pdf');
 var blob_L7D = response7Days.getBlob().setName('Last 7 Days' + '.pdf');
 var blob_L30D = response30Days.getBlob().setName('Last 30 Days' + '.pdf');
 

 GmailApp.sendEmail(email, subject, body, {
 cc: cc_email,
 bcc: bcc_email,
 htmlBody: body,
 attachments:[blob_Y, blob_L7D, blob_L30D ]   
 });
 }

Upvotes: 1

Related Questions