google-apps-script - 如何在更少的列中打印较长的 Goggle 表列

Question

我有一个谷歌表，有两个长列（图 1）的结果（比如 1 到 1500 条记录）和第 2 列我有一个公式生成的 QR 码。

现在我需要将其导出或打印为 pdf 文件，但我喜欢打破长列，以便 pdf 文件充满 8 列（图 2），这样打印时就不会浪费纸张。事实上，如果打印页面，它会给我很多空白空间。

标签： google-apps-scriptgoogle-sheets

Answer 1

有几个选项可以将选定的列从电子表格导出到 PDF。

最简单的可能是在导出 URL 中指定要导出的所需范围

样本：

function hideColumns(){ 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');  
  var url = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/export?";
  var parameters = 'exportFormat=pdf&format=pdf'        // export as pdf / csv / xls / xlsx
  + '&size=letter'                                   // paper size legal / letter / A4
  + '&portrait=true'                                // orientation, false for landscape
  + '&fitw=true&source=labnol'                       // fit to page width, false for actual size
  + '&sheetnames=false&printtitle=false'             // hide optional headers and footers
  + '&pagenumbers=false&gridlines=false'             // hide page numbers and gridlines
  + '&fzr=false'                                     // do not repeat row headers (frozen rows) on each page
  + '&gid=';                                         // the sheet's Id

  var token = ScriptApp.getOAuthToken();
  var lastColumn = sheet.getLastColumn();
  var lastRow = sheet.getLastRow();
  var columnNumber = 4;
  for (var i = 0; i < Math.ceil(lastColumn / columnNumber); i++){
    var startColumn = (columnNumber*i) + 1;
    var range = sheet.getRange(1, startColumn, lastRow, columnNumber).getA1Notation(); Logger.log(range);
    var response = UrlFetchApp.fetch(url + parameters + sheet.getSheetId() + "&range=" + range, {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });
    var name = "export selected columns "+i; 
    DriveApp.createFile(response.getBlob().setName(name));
    SpreadsheetApp.flush();
  }
}

更新：

要执行相反的操作 - 将 2 列打印为 8 列，您需要：

• 通过将工作表数据均匀地放入所需数量的列中来修改工作表数据
• 将其导出为 pdf
• 将数据移回原始状态

这是执行此任务的示例的修改：

function hideColumns(){ 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');  
  var url = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/export?";
  var parameters = 'exportFormat=pdf&format=pdf'        // export as pdf / csv / xls / xlsx
  + '&size=letter'                                   // paper size legal / letter / A4
  + '&portrait=true'                                // orientation, false for landscape
  + '&fitw=true&source=labnol'                       // fit to page width, false for actual size
  + '&sheetnames=false&printtitle=false'             // hide optional headers and footers
  + '&pagenumbers=false&gridlines=false'             // hide page numbers and gridlines
  + '&fzr=false'                                     // do not repeat row headers (frozen rows) on each page
  + '&gid=';                                         // the sheet's Id
  // split columns into 8 
  var token = ScriptApp.getOAuthToken();
  var lastColumn = sheet.getLastColumn();
  var lastRow = sheet.getLastRow();
  var realColumnNumber = 2;
  var desiredColumnNumber = 8;
  var rowsPerColumn = Math.ceil(lastRow / desiredColumnNumber * realColumnNumber);
  for (var i = 0; i < desiredColumnNumber / realColumnNumber; i++){
    var startRow = rowsPerColumn * (i+1) + 1;  
    var range = sheet.getRange(startRow, 1, rowsPerColumn, realColumnNumber); 
    range.moveTo(sheet.getRange(1, (1 + realColumnNumber * (i+1))));
    SpreadsheetApp.flush();
  }
  // export as pdf
  var response = UrlFetchApp.fetch(url + parameters + sheet.getSheetId() + "&range=" + sheet.getDataRange().getA1Notation(), {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });
  var name = "export into 4 columns"; 
  DriveApp.createFile(response.getBlob().setName(name));
  SpreadsheetApp.flush();
  // move columns back into original position
  for (var i = 0; i < desiredColumnNumber / realColumnNumber; i++){
    var startRow =  1;  
    var range = sheet.getRange(startRow, (i+1)*realColumnNumber + 1, rowsPerColumn, realColumnNumber); 
    range.moveTo(sheet.getRange((sheet.getLastRow() + 1), 1));
    SpreadsheetApp.flush();
  }
}