无法再为某些用户从 Google Sheets 电子表格生成 PDF [英] Can no longer produce PDF from Google Sheets spreadsheet for some of the users

问题描述

近一年来，我们一直在使用以下代码将 Google 表格工作表(保存在 theSheetName 中)导出为 InboundID 中指定的 PDF.>

上周，多个用户无法再生成 PDF.我在包含var newFile = DriveApp.createFile(blob);"的行中失败错误是:

<块引用>

从文本/html 到应用程序/pdf 的转换失败."

果然，UrlFetchApp.fetch 返回的是 HTML 而不是 PDF.同样，仅适用于某些用户.有没有人对我的用户为什么会看到这个有任何想法?

function sendPDFToDrive(theSheetName, InboundID){var theSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();var theSpreadsheetId = theSpreadSheet.getId();var thisSheetId = theSpreadSheet.getSheetByName(theSheetName).getSheetId();var url_base = theSpreadSheet.getUrl().replace(/edit$/,'');var theOutFileName = "GASFILE_M_";+ (Math.floor(Math.random() * 8997) + 1000) + '.pdf'//导出为pdfvar url_ext = 'export?exportFormat=pdf&format=pdf'+ (thisSheetId ? ('&gid=' + thisSheetId) : ('&id=' + theSpreadsheetId))//以下参数是可选的...+ '&size=A4'//纸张大小+ '&scale=2'//1= 正常 100%/2= 适合宽度/3= 适合高度/4= 适合页面+ '&portrait=true'//方向，横向为假+ '&horizo​​ntal_alignment=CENTER'//左/中/右+ '&fitw=true'//适合宽度，false 适合实际尺寸+ '&sheetnames=false&printtitle=false&pagenumbers=false'//隐藏可选的页眉和页脚+ '&gridlines=true'//隐藏网格线+ '&printnotes=false'//不显示注释+ '&fzr=true';//在每一页上重复行标题(冻结行)//设置选项变量选项 ={标题:{'授权':'承载' + ScriptApp.getOAuthToken(),}}//构建文件var response = UrlFetchApp.fetch(url_base + url_ext, options);var blob = response.getBlob().setName(theOutFileName);var folder = DriveApp.getFolderById(ABC123FooBarID");var newFile = DriveApp.createFile(blob);//从blob创建一个新文件newFile.setName(InboundID + ".pdf");//设置新文件的文件名newFile.makeCopy(文件夹);}

解决方案

我遇到了这个确切的问题.经过一些调试后，我发现我的 URL 被错误地创建.

我的代码和你的几乎一样.我发现罪魁祸首是以下几行:

var url_base = theSpreadSheet.getUrl().replace(/edit$/,'');

这实际上并没有像多年来那样清除行尾的编辑".我不能说这是为什么，但证据在日志中.所以，我手工制作了网址:

var url = 'https://docs.google.com/spreadsheets/d/'+SpreadsheetApp.getActiveSpreadsheet().getId()+'/';

这似乎解决了问题.这不是一个完美的面向未来的解决方案，因为如果 Google 更改 URL 的制作方式，这将失败.但它现在有效.

我希望这会有所帮助.您可以将您的代码创建的 url 发送到日志并检查它们以查看您是否遇到了与我相同的问题.

For almost a year we have been using the below code to export a Google Sheets sheet (held in theSheetName) to a PDF named as specified in InboundID.

This last week, one at a time various users can no longer produce the PDF. I get a failure at the line containing "var newFile = DriveApp.createFile(blob);" with the error being:

"Conversion from text/html to application/pdf failed."

And sure enough, the UrlFetchApp.fetch is returning HTML instead of a PDF. Again, only for some users. Does anyone have any thoughts as to why my users might be seeing this?

function sendPDFToDrive(theSheetName, InboundID) 
{
  var theSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var theSpreadsheetId = theSpreadSheet.getId();
  var thisSheetId = theSpreadSheet.getSheetByName(theSheetName).getSheetId();  
  var url_base = theSpreadSheet.getUrl().replace(/edit$/,'');
  var theOutFileName = "GASFILE_M_" + (Math.floor(Math.random() * 8997) + 1000) + '.pdf'
  //export as pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf'   
      + (thisSheetId ? ('&gid=' + thisSheetId) : ('&id=' + theSpreadsheetId)) 
      // following parameters are optional...
      + '&size=A4'      // paper size
      + '&scale=2' // 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
      + '&portrait=true'    // orientation, false for landscape
      + '&horizontal_alignment=CENTER'  //LEFT/CENTER/RIGHT
      + '&fitw=true'        // fit to width, false for actual size
      + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
      + '&gridlines=true'  // hide gridlines
      + '&printnotes=false' // don't show notes
      + '&fzr=true';       // repeat row headers (frozen rows) on each page
  // Setup options
  var options = 
  {
    headers: 
    {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }
  // Build the file
  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(theOutFileName);
  var folder = DriveApp.getFolderById("ABC123FooBarID");
  var newFile = DriveApp.createFile(blob); //Create a new file from the blob
  newFile.setName(InboundID + ".pdf"); //Set the file name of the new file
  newFile.makeCopy(folder);
}

解决方案

I was having this exact problem. After some debugging I saw that my URL was being created incorrectly.

My code was nearly identical to yours. Where I found the culprit was the following line:

var url_base = theSpreadSheet.getUrl().replace(/edit$/,'');

This was not actually clearing out the 'edit' to the end of the line like it had for years. I cannot say why this is, but the proof was in the logs. So, instead I crafted the url by hand:

var   url = 'https://docs.google.com/spreadsheets/d/'+SpreadsheetApp.getActiveSpreadsheet().getId()+'/';

This seemed to solve the problem. This is not a perfect futureproof resolution, because if Google changes how the URLs are crafted, this will fail. But it works for now.

I hope this helps. You can send the url your code is creating to logs and check them to see if you have the same issue I did.

这篇关于无法再为某些用户从 Google Sheets 电子表格生成 PDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！