谷歌应用脚本发送电子邮件活动表格

Question

我在网上找到了一个脚本，可以将当前工作表复制到一个临时新电子表格中，将其转换为PDF并通过电子邮件发送。我已经成功运行它，但是尝试仅发送某个区域却失败了。我尝试着做一些修改，但由于我的编程能力不足，所以未能成功。另外，我也想知道如何让它适应横向页面的1页PDF，并将其转换为没有网格线的格式（在线研究显示这似乎不可能？）或者甚至发送为XLS文件。

// Simple function to send Weekly Status Sheets to contacts listed on the "Contacts" sheet in the MPD.

// Load a menu item called "Project Admin" with a submenu item called "Send Status"
// Running this, sends the currently open sheet, as a PDF attachment
function onOpen() {
  var submenu = [{name:"Send Status", functionName:"exportSomeSheets"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Project Admin', submenu);  
}

function exportSomeSheets() {
  // Set the Active Spreadsheet so we don't forget
  var originalSpreadsheet = SpreadsheetApp.getActive();

  // Set the message to attach to the email.
  var message = "Please see attached"; // Could make it a pop-up perhaps, but out of wine today

  // Get Project Name from Cell A1
  var projectname = originalSpreadsheet.getRange("A1:A1").getValues(); 
  // Get Reporting Period from Cell B3
  var period = originalSpreadsheet.getRange("B3:B3").getValues(); 
  // Construct the Subject Line
  var subject = projectname + " - Weekly Status Sheet - " + period;


  // Get contact details from "Contacts" sheet and construct To: Header
  // Would be nice to include "Name" as well, to make contacts look prettier, one day.
  var contacts = originalSpreadsheet.getSheetByName("Contacts");
  var numRows = contacts.getLastRow();
  var emailTo = contacts.getRange(2, 2, numRows, 1).getValues();

  // Google scripts can't export just one Sheet from a Spreadsheet
  // So we have this disgusting hack

  // Create a new Spreadsheet and copy the current sheet into it.
  var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var projectname = SpreadsheetApp.getActiveSpreadsheet();
  sheet = originalSpreadsheet.getActiveSheet();
  sheet.copyTo(newSpreadsheet);

  // Find and delete the default "Sheet 1", after the copy to avoid triggering an apocalypse
  newSpreadsheet.getSheetByName('Sheet1').activate();
  newSpreadsheet.deleteActiveSheet();

  // Make zee PDF, currently called "Weekly status.pdf"
  // When I'm smart, filename will include a date and project name
  var pdf = DocsList.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();
  var attach = {fileName:'Weekly Status.pdf',content:pdf, mimeType:'application/pdf'};

  // Send the freshly constructed email 
  MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});

  // Delete the wasted sheet we created, so our Drive stays tidy.
  DocsList.getFileById(newSpreadsheet.getId()).setTrashed(true);  
}

- Jason Lehr.

Answer 1

发送单张纸时，您可以在发送前隐藏其他所有纸张。

var sheet_to_send = 'Sheet1';
//-----------------------------
var as = SpreadsheetApp.getActiveSpreadsheet();
var sheets = as.getSheets();
for(var i in sheets){
    if (sheets[i].getName()!=sheet_to_send){
        sheets[i].hideSheet();
    }
}
MailApp.sendEmail(email_to, email_subject, email_body, {attachments: SpreadsheetApp.getActiveSpreadsheet()});
for(var i in sheets){
    if (sheets[i].getName()!=sheet_to_send){
        sheets[i].showSheet();
    }
}

如果您有更多要发送的表格，您可以使用JavaScript对象进行筛选：

var sheets_to_send = {'Sheet1':1, 'Sheet3': 1};
...
// replace 
if (sheets[i].getName()!=sheet_to_send)
// by
if (!(sheets[i].getName() in sheet_to_send))

- Marco Zoqui

Answer 2

我正在使用这段代码自动发送一个带有电子邮件的谷歌电子表格，以pdf格式。它运行良好，但我需要通过删除网格线并设置显示A4来自定义pdf。有什么办法吗？谢谢。

Simple function to send Weekly Status Sheets to contacts listed on the "Contacts" sheet in the MPD.

// Load a menu item called "Project Admin" with a submenu item called "Send Status"
// Running this, sends the currently open sheet, as a PDF attachment
function onOpen() {
var submenu = [{name:"Invia", functionName:"exportSomeSheets"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu('Invia PDF', submenu);  
}

function exportSomeSheets() {
 // Set the Active Spreadsheet so we don't forget
var originalSpreadsheet = SpreadsheetApp.getActive();
// Set the message to attach to the email.
var message = "Messaggio email";
// Get Project Name from Cell A1
var projectname = originalSpreadsheet.getRange("A1:A1").getValues(); 
// Get Reporting Period from Cell A2
var period = originalSpreadsheet.getRange("A2:A2").getValues(); 
// Construct the Subject Line
var subject = projectname;


// Get contact details from "Contacts" sheet and construct To: Header
// Would be nice to include "Name" as well, to make contacts look prettier, one day.
var emailTo = originalSpreadsheet.getRange("A3:A3").getValues();


// Create a new Spreadsheet and copy the current sheet into it.
var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var projectname = SpreadsheetApp.getActiveSpreadsheet();
sheet = originalSpreadsheet.getActiveSheet();
sheet.copyTo(newSpreadsheet);

var aliases = GmailApp.getAliases()
Logger.log(aliases);
Logger.log(aliases[2]); //returns the alias located at position 0 of the aliases array


// Make zee PDF, currently called "Weekly status.pdf"
// When I'm smart, filename will include a date and project name
var pdf = DocsList.getFileById(originalSpreadsheet.getId()).getAs('application/pdf').getBytes();
var attach = {fileName:'Nome_allegato',content:pdf, mimeType:'application/pdf'};

// Send the freshly constructed email 
GmailApp.sendEmail(emailTo, subject, message, {'from': aliases[2], attachments:[attach]});

// Delete the wasted sheet we created, so our Drive stays tidy.
DocsList.getFileById(newSpreadsheet.getId()).setTrashed(true);  
} 

- Renzo

Answer 3

为了拆分电子表格，您必须首先将部分复制到临时工作表中，然后将该工作表复制到新电子表格中。

以下是我的代码，用于替换您的代码，从“创建电子表格…”到删除新电子表格的Sheet1。

  //variables firstRow & lastRow define the part of the sheet to copy
  //create new spreadsheet
  var newSpreadsheet = SpreadsheetApp.create('Spreadsheet to export');
  //create temporary sheet to copy to new spreadsheet
  var tempSheet = originalSpreadsheet.insertSheet();
  //if header copy it
  if (sheet.getFrozenRows() > 0)
  {
    dataSheet.getRange( 1, 1, sheet.getFrozenRows() ).copyTo( tempSheet.getRange(1,1) );
  }
  //copy relevant data to temporary sheet
  dataSheet.getRange( firstRow, 1, lastRow - firstRow + 1 ).copyTo(tempSheet.getRange( sheet.getFrozenRows() + 1, 1 ));
  //copy temp sheet to new spreadsheet
  tempSheet.copyTo( newSpreadsheet );

  //delete Sheet1 in new spreadsheet
  newSpreadsheet.getSheetByName('Sheet1').activate();
  newSpreadsheet.deleteActiveSheet();

  //delete temp sheet
  originalSpreadsheet.setActiveSheet(tempSheet);
  originalSpreadsheet.deleteActiveSheet();

- ajb1970