Google Apps脚本 - 单独导出所有表格

Question

我已经按照我的要求贴上了以下脚本，但是由于某种原因，它总是在我的电子表格的第5或第6页之后终止，无论工作表的内容实际上是什么...任何人都可以发现错误或改进脚本通常是为了使其正常工作？

这是我收到的错误msg：

fehler
异常：

function savePDFs( optSSId, optSheetId ) {

  var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();
  var url = ss.getUrl().replace(/edit$/,'');
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }
    // Get array of all sheets in spreadsheet
  var sheets = ss.getSheets();
  
  // Loop through all sheets, generating PDF files.
  for (var i=0; i<sheets.length; i++) {
    var sheet = sheets[i];
    
    // If provided a optSheetId, only save it.
    if (optSheetId && optSheetId !== sheet.getSheetId()) continue; 
    
    //export parameters
    var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
        + '&gid=' + sheet.getSheetId()   //the sheet's Id
        // following parameters are optional...
        + '&size=A4'      // paper size
        + '&portrait=true'    // orientation, false for landscape
        + '&scale=4'          // 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
        + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
        + '&gridlines=false'  // hide gridlines
        + '&fzr=false';       // do not repeat row headers (frozen rows) on each page

    var options = {
      headers: {
        'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()
      }
    }

    var response = UrlFetchApp.fetch(url + url_ext, options);
    
    var blob = response.getBlob().setName(ss.getName() + ' - ' + sheet.getName() + '.pdf');
    //the pdf is named according to the sheet - this is important for me!

  }
}

Answer 1

当我看到您的脚本时，我担心以下情况。

1. 使用urlfetchapp.fetch的连续请求。
2. 隐藏的床单可能包含在电子表格中。

当这些要点反映在您的脚本中时，如下所示。

修改后的脚本：

function savePDFs(optSSId, optSheetId) {
  var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();
  var url = ss.getUrl().replace(/edit$/, '');
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  } else {
    folder = DriveApp.getRootFolder();
  }
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];

    var isSheetHidden = sheet.isSheetHidden(); // <--- Added
    if (isSheetHidden) continue; // <--- Added

    if (optSheetId && optSheetId !== sheet.getSheetId()) continue;
    var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
      + '&gid=' + sheet.getSheetId()   //the sheet's Id
      + '&size=A4'      // paper size
      + '&portrait=true'    // orientation, false for landscape
      + '&scale=4'          // 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
      + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
      + '&gridlines=false'  // hide gridlines
      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page
    var options = { muteHttpExceptions: true, headers: { 'Authorization': 'Bearer ' + ScriptApp.getOAuthToken() } }
    var response = UrlFetchApp.fetch(url + url_ext, options);
    var blob = response.getBlob().setName(ss.getName() + ' - ' + sheet.getName() + '.pdf');

    Utilities.sleep(5000); // <--- Added

  }
}

• 请调整5000 utilities.sleep.sleep（5000）通过测试此脚本，您的实际情况。

参考：

• https://developers.google.com/apps-script/reference/spreadsheet/sheet#issheethidend（）
• sleep（milliseconds）