Apps Script email column range in Google Sheets tab, hide all other worksheet tabs

Question

Good morning,

I have a requirement to email a specific column range from a Google Sheets tab into a PDF and ensure all other columns are not included in the email. I have created a function in Apps script to email column range (A1:F105) from worksheet tab: Email Invoice.

Problem - Although the function sends the email in pdf format, the entire worksheet tab is included, not the specified range. Also as the function runs, several of the other columns are hidden, but it doesn't un-hide the worksheet tabs as needed.

Below I have provided the function, identifying a solution is greatly appreciated. Thanks!

function sendReport() {

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Fuel Usage").hideSheet();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Gross Metrics").hideSheet();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Historical Tracking Metrics").hideSheet();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Scale Ticket Form").hideSheet();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Pay Stub").hideSheet();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lookups").hideSheet();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Trucking Data").hideSheet();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Indexes").hideSheet();

  var driverName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Invoice").getRange("J7"); 
  var driveremail = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Invoice").getRange("J14"); 
  var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Invoice").getRange("A1:F105");


  var emailDriverName = driverName.getValue();
  var driveremailaddr = driveremail.getValue();
  var printrange = range.getValue();

  var message = {
    to: driveremailaddr,
    subject: "Loads by " + emailDriverName,
    body: "Please do not respond to this email as it is an unmonitored address.\n\nIf you have any questions please call User or email example-email2.com\n\nSee Attachment",
    cc: 'example-email@gmail.com',
    // {cc: 'example-email2.com',attachments:[att]}
    attachments: [SpreadsheetApp.getActiveSpreadsheet().getAs(MimeType.PDF).setName("Load Invoice")]
  }
  MailApp.sendEmail(message);



{

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Fuel Usage").activate();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Gross Metrics").activate();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Historical Tracking Metrics").activate();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Scale Ticket Form").activate();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Pay Stub").activate();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lookups").activate();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Trucking Data").activate();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Indexes").activate();

}

}

score 0 · Accepted Answer · answered Jun 20 '23 at 01:31

I believe your goal is as follows.

You want to export the range of "A1:F105" of "Email Invoice" sheet as a PDF file using Google Apps Script.

In this case, how about using an endpoint for exporting Spreadsheet to PDF format? In that case, it is not required to hide the excluded sheets. When this is reflected in your script, it becomes as follows.

Modified script:

function sendReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Email Invoice");
  var rangeA1Notation = "A1:F105";
  var rangeObj = sheet.getRange(rangeA1Notation);
  var r1 = rangeObj.getRow() - 1;
  var c1 = rangeObj.getColumn() - 1;
  var r2 = r1 + rangeObj.getNumRows();
  var c2 = c1 + rangeObj.getNumColumns();
  var endpoint = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?gid=${sheet.getSheetId()}&format=pdf&r1=${r1}&c1=${c1}&r2=${r2}&c2=${c2}`;
  var pdfBlob = UrlFetchApp.fetch(endpoint, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
  var [[emailDriverName], , , , , , , [driveremailaddr]] = sheet.getRange("J7:J14").getDisplayValues();
  var message = {
    to: driveremailaddr,
    subject: "Loads by " + emailDriverName,
    body: "Please do not respond to this email as it is an unmonitored address.\n\nIf you have any questions please call User or email example-email2.com\n\nSee Attachment",
    cc: 'example-email@gmail.com',
    attachments: [pdfBlob]
  }
  MailApp.sendEmail(message);
}

When this script is run, the range of "A1:F105" of "Email Invoice" sheet is exported as PDF data.

References:

Related threads and posts

Thanks for following up and providing guidance on resolving the issue I mentioned. I have revised the code in Apps Script, as recommended and it's working as needed. Thanks! — Jarvis Davis, Jun 21 '23 at 02:09
@Jarvis Davis Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. — Tanaike, Jun 21 '23 at 02:42

Apps Script email column range in Google Sheets tab, hide all other worksheet tabs

1 Answers1

Modified script:

References: