使用Google Apps脚本将电子表格中的单张作为pdf保存在特定文件夹中 [英] Using Google Apps Script to save a single sheet from a spreadsheet as pdf in a specific folder

问题描述

我使用Google电子表格准备发票，并且正在寻找一个简单的脚本，将发票所在的工作表保存在发票文件夹中以构建存档。
我从Stackoverflow和youtube上的众多贡献者那里借来了代码，并提出了一个可行的代码。我必须将发票复制到新创建的电子表格中，因为从类型电子表格中的单张工作表创建PDF似乎是不可能的。我还必须使用一段代码将pdf从根目录移动到发票文件夹
我无法解决的唯一问题是，第6行创建的电子表格由两张纸片组成。一个空的和正确复制的。创建的pdf因此alsa有2张，一张空白和一张正确的纸张。
任何人都知道如何解决这个问题？
顺便说一下，有时在文件夹中显示pdf之前需要几分钟的时间。

以下是代码

  function generatePdf（）{
 //创建一个临时电子表格，从电子表格中存储所需的表格。
 var originalSpreadsheet = SpreadsheetApp.getActive（）; 
 originalSpreadsheet.setActiveSheet（originalSpreadsheet.getSheets（）[4]）; 
 var name =Testname
 var newSpreadsheet = SpreadsheetApp.create（name）; 
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getActiveSheet（）; 
 sheet = originalSpreadsheet.getActiveSheet（）; 
 sheet.copyTo（newSpreadsheet）; 
 //将所需的表格另存为pdf 
 var pdf = DriveApp.getFileById（newSpreadsheet.getId（））。getAs（'application / pdf'）; 
 var saveCopy = DriveApp.createFile（pdf）; 
 //删除临时电子表格
 DriveApp.getFilesByName（name）.next（）。setTrashed（true）; 
 //将pdf文件从根文件夹移至要存储发票的文件夹。 
 var files = DriveApp.getRootFolder（）。getFiles（）; 
 while（files.hasNext（））{
 var file = files.next（）; 
 var destination = DriveApp.getFolderById（0B3ok04PZOVbgLXA2dy14MVlLRXM）; 
 destination.addFile（file）; 
 var pull = DriveApp.getRootFolder（）; 
 pull.removeFile（file）; 
} 
} 
  

解决方案

I 'm不知道你是否遇到过这段代码，但你可以试试这个：

 函数checkSheet（）{
 var sheetName =Sheet1; 
 var folderID =FOLDER_ID; //文件夹ID保存在文件夹中。 
 var pdfName =Invoice+ Date（）; 
 
 var sourceSpreadsheet = SpreadsheetApp.getActive（）; 
 var sourceSheet = sourceSpreadsheet.getSheetByName（sheetName）; 
 var folder = DriveApp.getFolderById（folderID）; 
 
 //复制整个电子表格
 var destSpreadsheet = SpreadsheetApp.open（DriveApp.getFileById（sourceSpreadsheet.getId（））.makeCopy（tmp_convert_to_pdf，folder））
 
 //删除冗余表单
 var sheets = destSpreadsheet.getSheets（）; 
 for（i = 0; i< sheets.length; i ++）{
 if（sheets [i] .getSheetName（）！= sheetName）{
 destSpreadsheet.deleteSheet（sheets [i ]）; 
} 
} 
 
 var destSheet = destSpreadsheet.getSheets（）[0]; 
 //用文本重设空格值（以避免引用中断）
 var sourceRange = sourceSheet.getRange（1,1，sourceSheet.getMaxRows（），sourceSheet.getMaxColumns（））; 
 var sourcevalues = sourceRange.getValues（）; 
 var destRange = destSheet.getRange（1，1，destSheet.getMaxRows（），destSheet.getMaxColumns（））; 
 destRange.setValues（sourcevalues）; 
 
 //保存为pdf 
 var theBlob = destSpreadsheet.getBlob（）。getAs（'application / pdf'）。setName（pdfName）; 
 var newFile = folder.createFile（theBlob）; 
 
 //删除临时表单
 DriveApp.getFileById（destSpreadsheet.getId（））。setTrashed（true）; 
} 
  

注 ：
在测试此代码时，它立即创建PDF，但可能取决于发票模板。

参考文献：

• 
  

  I am using a Google spreadsheet to prepare invoices and was looking for a simple script that saves a sheet, where the invoice is in, in a "invoices" folder to build an archive. I "borrowed" code from numerous contributors on Stackoverflow and youtube and came up with a code that works. I had to copy the invoice to a newly created spreadsheet, because it seems to be impossible to create a pdf from one single sheet in type spreadsheet. I also had to use a piece of code to move the pdf from the root to an "invoices" folder The only thing I am not able to solve is that the spreadsheet created in line 6 consistes of 2 sheets. An empty one and a correctly copied one. The created pdf thus alsa has 2 sheets, one empty and one correct sheet. Anyone got a clue how to solve this ? By the way sometimes it takes some minutes before the pdf shows in the folders.

  Below is the code

  function generatePdf(){
      //Create a temporary spreadsheet, to store the desired sheet from the spreadsheet in. 
      var originalSpreadsheet = SpreadsheetApp.getActive();
      originalSpreadsheet.setActiveSheet(originalSpreadsheet.getSheets()[4]);
      var name = "Testname"
      var newSpreadsheet = SpreadsheetApp.create(name);
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      sheet = originalSpreadsheet.getActiveSheet();
      sheet.copyTo(newSpreadsheet);
      //Save the desired sheet as pdf
      var pdf = DriveApp.getFileById(newSpreadsheet.getId()).getAs('application/pdf');
      var saveCopy = DriveApp.createFile(pdf);
      //Delete temporary spreadsheet
      DriveApp.getFilesByName(name).next().setTrashed(true);
      //Move the pdf file from the rootfolder to the folder where invoices are to be stored.
      var files = DriveApp.getRootFolder().getFiles();
      while (files.hasNext()) {
          var file = files.next();
          var destination = DriveApp.getFolderById("0B3ok04PZOVbgLXA2dy14MVlLRXM");
          destination.addFile(file);
          var pull = DriveApp.getRootFolder();
          pull.removeFile(file);    
      }
  }
  

  解决方案

  I'm not sure if you have encountered this code but you can try this:

  function checkSheet() {
  var sheetName = "Sheet1";
  var folderID = "FOLDER_ID"; // Folder id to save in a folder.
  var pdfName = "Invoice "+Date();
  
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID);
  
  //Copy whole spreadsheet
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))
  
  //delete redundant sheets
  var sheets = destSpreadsheet.getSheets();
  for (i = 0; i < sheets.length; i++) {
  if (sheets[i].getSheetName() != sheetName){
  destSpreadsheet.deleteSheet(sheets[i]);
  }
  }
  
  var destSheet = destSpreadsheet.getSheets()[0];
  //repace cell values with text (to avoid broken references) 
  var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
  var sourcevalues = sourceRange.getValues();
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  destRange.setValues(sourcevalues);
  
  //save to pdf
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);
  
  //Delete the temporary sheet
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
  }
  

  Note: While testing this code, it creates the pdf in an instant, but it may depend on the invoice template.

  References:

  • Simple Google Apps Script to export a single sheet to PDF and email it to a contact list
  • Sample Template - Professional Invoice Template (for Testing Purpose)

  Hope this helps!

  UPDATE

  这篇关于使用Google Apps脚本将电子表格中的单张作为pdf保存在特定文件夹中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！