在Google Apps脚本中创建多个PDF时出错 [英] Error when creating multiple PDFs in Google Apps Script

查看：108
本文介绍了在Google Apps脚本中创建多个PDF时出错的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我创建了一个将电子表格(工作表)另存为PDF到指定文件夹的功能.该函数很好用，但是当我多次运行它(我需要执行20次)时，在第7、8或9次运行后会出现错误.错误是429.它并不能给我很多信息，而且我似乎也找不到错误的根源和纠正方法.我尝试添加一个Utilities.sleep(xxx)，当我进行5秒钟的睡眠时它确实可以工作(但是当它少于5秒钟时却不能)！

这是我的代码(带有Utilities.sleep):

/**
 * Creates a PDF file 
 *
 * 2019-12-17 Simon: Created
 *
 * @param {?} token                ScriptApp.getOAuthToken();
 * @param {?} spreadsheet          Spreadsheet (SpreadsheetApp.getActiveSpreadsheet())
 * @param {string} sheetName       Name of the sheet to print
 * @param {string} pdfName         Name of the pdf file (excluding .pdf)
 * @param {string} folder          Folder to save in
 * @param {string} portrait        true=portrait, false=landscape
 * @param {number} scale           1 = Normal 100% -- 2 = Fit to width -- 3 = Fit to height -- 4 = Fit to Page
 * @param {number} margins         In inches. Dot as decimal separator, e.g. '0.2'
 * @param {string} range           Optional. E.g. 'D4:AX74'
 */ 
function savePdf(spreadsheet, sheetName, pdfName, folder, portrait, scale, margins, range) { 
  var rangeUse = (range ? '&range=' + range : '');
  var sheetId = spreadsheet.getSheetByName(sheetName).getSheetId();
  var url_base = spreadsheetId.getUrl().replace(/edit$/,'');
  var url_ext = 'export?'
  + '&gid=' + sheetId  
  + rangeUse
  + '&format=pdf'                   // export format
  + '&size=a4'                      // A3/A4/A5/B4/B5/letter/tabloid/legal/statement/executive/folio
  + '&portrait=' + portrait         // true = Potrait / false= Landscape
  + '&scale=' + scale               // 1 = Normal 100% -- 2 = Fit to width -- 3 = Fit to height -- 4 = Fit to Page
  + '&top_margin=' + margins        // all four margins must be set!
  + '&bottom_margin=' + margins     // all four margins must be set!
  + '&left_margin=' + margins       // all four margins must be set!
  + '&right_margin=' + margins      // all four margins must be set!
  + '&gridlines=false'              // true/false
  + '&printnotes=false'             // true/false
  + '&pageorder=2'                  // 1 = Down, then over -- 2 = Over, then down
  + '&horizontal_alignment=CENTER'  // LEFT/CENTER/RIGHT
  + '&vertical_alignment=MIDDLE'    // TOP/MIDDLE/BOTTOM
  + '&printtitle=false'             // print title --true/false
  + '&sheetnames=false'             // print sheet names -- true/false
  + '&fzr=true'                     // repeat row headers (frozen rows) on each page -- true/false
  + '&fzc=true'                     // repeat column headers (frozen columns) on each page -- true/false
  + '&attachment=false'             // true/false
  var token = ScriptApp.getOAuthToken();
  var url_options = {headers: {'Authorization': 'Bearer ' + token, 'muteHttpExceptions': true,}};
  Utilities.sleep(5000);
  var response = UrlFetchApp.fetch(url_base + url_ext, url_options);
  var blob = response.getBlob().getAs('application/pdf').setName(pdfName + '.pdf');
  folder.createFile(blob);
}

解决方案

我曾经遇到过这个问题，并且能够通过从函数的递归部分中删除对ScriptApp.getOAuthToken()的调用来解决此问题.我认为在您的情况下，最简单的方法是使用CacheService.

替换行var token = ScriptApp.getOAuthToken();

具有:

var token;
  if(CacheService.getScriptCache().get('token')!=null) {
    token = CacheService.getScriptCache().get('token');
  } else {
    token = ScriptApp.getOAuthToken();
    CacheService.getScriptCache().put('token',token,120);   
  }

这会将令牌值存储在CacheService中，而不是通过脚本循环递归调用它.希望这能解决您遇到的问题.

在上述操作不能解决您的问题后，我回头查看了我所做的事情，并错误地认为它正在获取引起问题的令牌，但这是从Google Sheets API导出的速率限制.这是我当时发现实际上已解决我的问题的内容:

速率限制(关于此事的想法，请参阅我的最后一段)不是针对每个SHEET，而是针对每个用户-我的递归脚本当时正在访问两个不同的工作表，因此该函数的自然延迟会为我的脚本可以毫无问题地运行.

现在可以修复您的戏剧:

复制您的问题后，我修改了父函数以创建主电子表格的副本:

var mainsheetcopy = mainsheet.copy('Copy of main sheet')

，然后在两个电子表格之间切换，以将调用发送给函数以提取PDF.我能够迭代提取20个pdf文件，而睡眠延迟仅为750ms，并且内置了18个迭代，完全没有延迟.

for(var i=0; i<20; i++) {
    if(isEven(i)) {
      sheetid = mainsheet.getId();
    } else { 
      sheetid = mainsheetcopy.getId()}

供参考，isEven函数如下:

function isEven(n) {
   return n % 2 == 0;
}

然后，在脚本末尾，我删除了副本:

DriveApp.getFileById(mainsheetcopy.getId()).setTrashed(true);

如果时间是一个因素，则可以创建主电子表格的第三份副本，并且仅通过该函数自然需要的延迟，它会使您超出他们将表格导出为PDF时的速率限制.

实际的速率限制有点难以捉摸，但是每7.5-8秒一张纸似乎可以解决这个问题.我能够对每张纸最多导出5个PDF文件进行迭代，而100％的时间没有速率限制，偶尔有6个.

I've created a function that saves a spreadsheet (sheet) as PDF to a specified folder. The function works great but when I run it in multiple times (I need to do it 20 times), I get an error after the 7th, 8th or 9th run. The error is 429. It doesn't give me a whole lot of info and I can't seem to find what the error is and how to correct. I've tried adding a Utilities.sleep(xxx) and it does work when I do a 5 second sleep (but not when it's less than 5 seconds)!

Here's my code (with Utilities.sleep):

/**
 * Creates a PDF file 
 *
 * 2019-12-17 Simon: Created
 *
 * @param {?} token                ScriptApp.getOAuthToken();
 * @param {?} spreadsheet          Spreadsheet (SpreadsheetApp.getActiveSpreadsheet())
 * @param {string} sheetName       Name of the sheet to print
 * @param {string} pdfName         Name of the pdf file (excluding .pdf)
 * @param {string} folder          Folder to save in
 * @param {string} portrait        true=portrait, false=landscape
 * @param {number} scale           1 = Normal 100% -- 2 = Fit to width -- 3 = Fit to height -- 4 = Fit to Page
 * @param {number} margins         In inches. Dot as decimal separator, e.g. '0.2'
 * @param {string} range           Optional. E.g. 'D4:AX74'
 */ 
function savePdf(spreadsheet, sheetName, pdfName, folder, portrait, scale, margins, range) { 
  var rangeUse = (range ? '&range=' + range : '');
  var sheetId = spreadsheet.getSheetByName(sheetName).getSheetId();
  var url_base = spreadsheetId.getUrl().replace(/edit$/,'');
  var url_ext = 'export?'
  + '&gid=' + sheetId  
  + rangeUse
  + '&format=pdf'                   // export format
  + '&size=a4'                      // A3/A4/A5/B4/B5/letter/tabloid/legal/statement/executive/folio
  + '&portrait=' + portrait         // true = Potrait / false= Landscape
  + '&scale=' + scale               // 1 = Normal 100% -- 2 = Fit to width -- 3 = Fit to height -- 4 = Fit to Page
  + '&top_margin=' + margins        // all four margins must be set!
  + '&bottom_margin=' + margins     // all four margins must be set!
  + '&left_margin=' + margins       // all four margins must be set!
  + '&right_margin=' + margins      // all four margins must be set!
  + '&gridlines=false'              // true/false
  + '&printnotes=false'             // true/false
  + '&pageorder=2'                  // 1 = Down, then over -- 2 = Over, then down
  + '&horizontal_alignment=CENTER'  // LEFT/CENTER/RIGHT
  + '&vertical_alignment=MIDDLE'    // TOP/MIDDLE/BOTTOM
  + '&printtitle=false'             // print title --true/false
  + '&sheetnames=false'             // print sheet names -- true/false
  + '&fzr=true'                     // repeat row headers (frozen rows) on each page -- true/false
  + '&fzc=true'                     // repeat column headers (frozen columns) on each page -- true/false
  + '&attachment=false'             // true/false
  var token = ScriptApp.getOAuthToken();
  var url_options = {headers: {'Authorization': 'Bearer ' + token, 'muteHttpExceptions': true,}};
  Utilities.sleep(5000);
  var response = UrlFetchApp.fetch(url_base + url_ext, url_options);
  var blob = response.getBlob().getAs('application/pdf').setName(pdfName + '.pdf');
  folder.createFile(blob);
}

解决方案

I ran into this once and was able to fix it by removing the call to ScriptApp.getOAuthToken() out of the recursive portion of the function. I think the easiest way to do this in your case with minimal complication would be to use the CacheService.

replace the line var token = ScriptApp.getOAuthToken();

with: 

var token;
  if(CacheService.getScriptCache().get('token')!=null) {
    token = CacheService.getScriptCache().get('token');
  } else {
    token = ScriptApp.getOAuthToken();
    CacheService.getScriptCache().put('token',token,120);   
  }

This will store the token value in the CacheService instead of calling it recursively with your script loop. Hopefully this solves your issue as it did mine.

Edit:

After the above didn't fix your issue I looked back at what I had done and wrongly assumed that it was fetching the token that had caused my issue, but it was a rate limit on exporting from the Google Sheets API. Here's what I noticed ACTUALLY fixed MY issue at the time:

The rate limit (see my last paragraph for my thoughts on this) is per SHEET and not per user -- my recursive script was accessing two different sheets at the time so the natural delay in the function created just enough time delay for my script to run its course without problems.

Now to a fix for your drama:

After duplicating your problem I modified my parent function to create a copy of the main spreadsheet:

var mainsheetcopy = mainsheet.copy('Copy of main sheet')

and then switch between the two spreadsheets to send the call to the function to extract the PDF. I was able to iterate through the extraction of 20 pdfs with a sleep delay of only 750ms, and 18 iterations with no delay at all built in.

for(var i=0; i<20; i++) {
    if(isEven(i)) {
      sheetid = mainsheet.getId();
    } else { 
      sheetid = mainsheetcopy.getId()}

for reference, the isEven function is below: 

function isEven(n) {
   return n % 2 == 0;
}

Then, at the end of the script I deleted the copy: 

DriveApp.getFileById(mainsheetcopy.getId()).setTrashed(true);

If time is a factor, you could create a third copy of the main spreadsheet and through only the delay that the function takes naturally it would put you outside of the rate limit that they have on exporting the sheet as a PDF.

The actual rate limit is a bit elusive, but one sheet every 7.5-8 seconds seems to skirt this. I was able to iterate up to 5 PDF files exported per sheet with no rate limit 100% of the time and 6 occasionally.

这篇关于在Google Apps脚本中创建多个PDF时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆