CODEWITHSUNDEEP
javascriptemailpdfgoogle-apps-scriptgoogle-sheets
Sebastiano Ligato
Sebastiano Ligato

Reputation: 1

Sending only one sheet or active sheet as pdf via email

Hello I have been trying to write my first script to send just one of the sheet from one of my google document but everytime I end up sending all the sheets instead. The document has a main page daily where i have the list of emails and then 7 sheets called monday, tuesday, etc

This is the code i use - can you help?

function emailActivesheetAsPDF() {
  DocumentApp.getActiveDocument();
  DriveApp.getFiles();

  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1_3VpJaFliJVYt1JL3cLSKw2ftsmsLeDjznHJ66-wq9w/edit");

  const value = ss.getSheetByName("Daily").getRange("B5").getValue();
  const email = value.toString();

  const subject = 'Yesterday Handover';

  const body = "Please find attached yesterday handover";

  const url = 'https://docs.google.com/spreadsheets/d/1_3VpJaFliJVYt1JL3cLSKw2ftsmsLeDjznHJ66-wq9w/export?';

  const exportOptions =
    'exportFormat=pdf&format=pdf'
    '&size=legal'
    '&portrait=true'
    '&fitw=false'
    '&sheetnames=false&printtitle=false'
    '&pagenumbers=false&gridlines=false'
    '&fzr=false'
    '&gid=1263775066';
 
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
  
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [{
            fileName: "Handover" + ".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
        }]
    });
}

Upvotes: 0

Views: 273

Answers (1)

Marios
Marios

Reputation: 27350

Explanation / Issue:

  • You forgot to concatenate the options in the exportOptions variable and therefore exportOptions gets only the value of &size=legal and ignores the rest of the parameters including gid which you specify the sheet id. One solution is to concatenate the options via +.

  • I also improved your code to make it more flexible. For example, instead of defining a sheet by its gid in the code, you can define it by its name in the variable sheetToPrint and then GAS will get its gid and use it as an export option for the pdf.

Solution:

function emailActivesheetAsPDF() {
  const sheetToPrint = "Daily"; // name of the sheet to print
  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1_3VpJaFliJVYt1JL3cLSKw2ftsmsLeDjznHJ66-wq9w/edit");
  const ssID = ss.getId();
  const email = ss.getSheetByName("Daily").getRange("B5").getValue();
  const subject = 'Yesterday Handover';
  const body = "Please find attached yesterday handover";
  const shID = ss.getSheetByName(sheetToPrint).getSheetId();
  const url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=pdf&id="+ssID;
  const exportOptions =
    '&size=legal'+
    '&portrait=true'+
    '&fitw=false'+
    '&sheetnames=false&printtitle=false'+
    '&pagenumbers=false&gridlines=false'+
    '&fzr=false'+
    '&gid='+shID; 
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
  
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [{
            fileName: "Handover" + ".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
        }]
    });
}

Upvotes: 1

Related Questions