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

huangapple go评论65阅读模式
英文:

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

问题

早上好,

我有一个要求,需要将Google Sheets选项卡中的特定列范围以PDF形式发送电子邮件,并确保不包括电子邮件中的所有其他列。我已经在Apps脚本中创建了一个函数,以从工作表选项卡“Email Invoice”发送列范围(A1:F105)的电子邮件。

问题 - 尽管该函数以pdf格式发送电子邮件,但整个工作表选项卡都包含在内,而不是指定的范围。此外,随着函数的运行,一些其他列被隐藏了,但它没有按需要取消隐藏工作表选项卡。

以下是提供的函数,非常感谢您的帮助。谢谢!

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

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

答案1

得分: 0

  • 你希望使用Google Apps Script将“Email Invoice”表的“A1:F105”范围导出为PDF文件。
  • 在这种情况下,建议使用导出电子表格到PDF格式的端点。在这种情况下,不需要隐藏排除的工作表。当这反映在你的脚本中时,它变为如下所示。
  • 修改后的脚本:
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);
}
英文:

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:

huangapple
  • 本文由 发表于 2023年6月19日 23:10:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/76507920.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定