使用脚本将值和格式复制到Google工作表中的新工作表 [英] Copy Value and Format to new sheet in Google sheets using a script

问题描述

在Google Apps脚本中，Im使用以下脚本创建pdf并作为电子邮件发送。
问题是，当它创建新工作表并复制时，公式将被复制。通过电子邮件发送的PDF将显示REF错误。

我需要复制值和格式。

谢谢

  function exportPDF（）{$​​ b 
 $ b var originalSpreadsheet = SpreadsheetApp.getActive（）; 
 var message =请参阅附件; 
 var projectname = originalSpreadsheet.getRange（ a2：c2）。getValues（）; 
 var period = originalSpreadsheet.getRange（ B24：c24）。getValues（）; 
 var subject =项目名称+-每日控制总计- +期限； 
 var联系人= originalSpreadsheet.getSheetByName（联系人）; 
 var numRows = contact.getLastRow（）; 
 var emailTo = contact.getRange（2，2，numRows，1）.getValues（）; 
 var newSpreadsheet = SpreadsheetApp.create（要导出的电子表格）; 
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getActiveSheet（）; 
 var projectname = SpreadsheetApp.getActiveSpreadsheet（）; 
工作表= originalSpreadsheet.getActiveSheet（）; 
 sheet.copyTo（newSpreadsheet）; 
 
 newSpreadsheet.getSheetByName（’Sheet1’）。activate（）; 
 newSpreadsheet.deleteActiveSheet（）; 
 
 DriveApp.getFileById（newSpreadsheet.getId（））。getAs（’application / pdf’）。getBytes（）; 
 var attach = {fileName： Daily Totals.pdf，content：pdf，mimeType： application / pdf}； 
 
 MailApp.sendEmail（emailTo，主题，消息，{attachments：[attach]}）; 
 DriveApp.getFileById（newSpreadsheet.getId（））。setTrashed（true）;} 
  

解决方案

当前，如果要复制的工作表中有其他工作表的引用，则在复制后会出现REF错误，这是有道理的，因为数据不再位于应有的位置。 p>

为避免这种情况，我们可以使用范围的 copyTo（Range，Object）; 方法。但是，此方法只能在单个电子表格中使用，因此我们需要创建一个临时表来存储我们的值，然后将其复制到新的电子表格中以通过电子邮件发送：

  var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var newSS = SpreadsheetApp.create（要导出的电子表格）; 
 
 var temp = ss.duplicateActiveSheet（）; //创建临时表，让我们复制值
 range = temp.getDataRange（）; 
 range.copyTo（range，{contentsOnly：true}）; //复制值
 
 temp.copyTo（newSS）; //将值复制到新的电子表格
 ss.deleteSheet（temp）; //删除我们的临时表。 
  

已更新：因此，您的代码应如下所示：

 函数exportPDF（）{$​​ b 
 $ b var originalSpreadsheet = SpreadsheetApp.getActive（）; 
 
 var message =请参阅附件; 
 var projectname = originalSpreadsheet.getRange（ a2：c2）。getValues（）; 
 var period = originalSpreadsheet.getRange（ B24：c24）。getValues（）; 
 var subject =项目名称+-每日控制总计- +期限； 
 var联系人= originalSpreadsheet.getSheetByName（联系人）; 
 var numRows = contact.getLastRow（）; 
 var emailTo = contact.getRange（2，2，numRows，1）.getValues（）; 
 
 var newSpreadsheet = SpreadsheetApp.create（要导出的电子表格）; 
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getActiveSheet（）; 
 
工作表= originalSpreadsheet.getActiveSheet（）; 
 var temp = originalSpreadsheet.duplicateActiveSheet（）; //创建临时表，让我们复制值
 
 range = temp.getDataRange（）; 
 range.copyTo（range，{contentsOnly：true}）; //复制值
 
 temp.copyTo（newSpreadsheet）; //将值复制到新的电子表格
 ss.deleteSheet（temp）; //删除我们的临时表。 
 
 newSpreadsheet.getSheetByName（’Sheet1’）。activate（）; 
 newSpreadsheet.deleteActiveSheet（）; 
 
 DriveApp.getFileById（newSpreadsheet.getId（））。getAs（’application / pdf’）。getBytes（）; 
 var attach = {fileName： Daily Totals.pdf，content：pdf，mimeType： application / pdf}； 
 
 MailApp.sendEmail（emailTo，主题，消息，{attachments：[attach]}）; 
 DriveApp.getFileById（newSpreadsheet.getId（））。setTrashed（true）; 
} 
  

In Google Apps Script, Im using the script below to create a pdf and send as an email. The problem is that when it creates the new sheet and copies across, the formulas are copied. The emailed PDF than shows a REF error.

I need to copy the Value and Format.

Thanks

function exportPDF() {

var originalSpreadsheet = SpreadsheetApp.getActive();
var message = "Please see attached"; 
var projectname = originalSpreadsheet.getRange("a2:c2").getValues(); 
var period = originalSpreadsheet.getRange("B24:c24").getValues(); 
var subject = projectname + " - Daily Control Totals - " + period;
var contacts = originalSpreadsheet.getSheetByName("Contacts");
var numRows = contacts.getLastRow();
var emailTo = contacts.getRange(2, 2, numRows, 1).getValues();
var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var projectname = SpreadsheetApp.getActiveSpreadsheet();
sheet = originalSpreadsheet.getActiveSheet();
sheet.copyTo(newSpreadsheet);

newSpreadsheet.getSheetByName('Sheet1').activate();
newSpreadsheet.deleteActiveSheet();

DriveApp.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();
var attach = {fileName:'Daily Totals.pdf',content:pdf, mimeType:'application/pdf'};

MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
DriveApp.getFileById(newSpreadsheet.getId()).setTrashed(true);}

解决方案

Currently, if you have references to other sheets in the sheet you are copying, after copying you will get REF errors, which makes sense as the data is no longer where it should be.

To avoid that when we can use Range's copyTo(Range, Object); method. However, this method can only be used within a single spreadsheet, so we need to create a temporary sheet that will store our values then copy the sheet to a new spreadsheet to send in an email:

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var newSS = SpreadsheetApp.create("Spreadsheet to export");

  var temp = ss.duplicateActiveSheet(); //create temp sheet that lets us copy the values
  range = temp.getDataRange();
  range.copyTo(range, {contentsOnly: true}); //copy the values

  temp.copyTo(newSS); //copy the values to a new spreadsheet
  ss.deleteSheet(temp); //delete our temp sheet.

Updated: So your code would look something like this:

function exportPDF() {

  var originalSpreadsheet = SpreadsheetApp.getActive();

  var message = "Please see attached"; 
  var projectname = originalSpreadsheet.getRange("a2:c2").getValues(); 
  var period = originalSpreadsheet.getRange("B24:c24").getValues(); 
  var subject = projectname + " - Daily Control Totals - " + period;
  var contacts = originalSpreadsheet.getSheetByName("Contacts");
  var numRows = contacts.getLastRow();
  var emailTo = contacts.getRange(2, 2, numRows, 1).getValues();

  var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  sheet = originalSpreadsheet.getActiveSheet();
  var temp = originalSpreadsheet.duplicateActiveSheet(); //create temp sheet that lets us copy the values

  range = temp.getDataRange();
  range.copyTo(range, {contentsOnly: true}); //copy the values

  temp.copyTo(newSpreadsheet); //copy the values to a new spreadsheet
  ss.deleteSheet(temp); //delete our temp sheet.

  newSpreadsheet.getSheetByName('Sheet1').activate();
  newSpreadsheet.deleteActiveSheet();

  DriveApp.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();
  var attach = {fileName:'Daily Totals.pdf',content:pdf, mimeType:'application/pdf'};

  MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
  DriveApp.getFileById(newSpreadsheet.getId()).setTrashed(true);
}

这篇关于使用脚本将值和格式复制到Google工作表中的新工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！