在 Google Apps Script 中将范围导出为 PDF [英] Export a range as a PDF in Google Apps Script

问题描述

我已经到处搜索了一些可以完成我想要的代码行，但我对谷歌应用程序脚本的工作方式太陌生了.我只用 Microsoft excel 编写代码，虽然我没有完全迷失，但我仍然很迷茫.

I've searched all over for some lines of code that would accomplish what I want, but I'm too unfamiliar with how google apps script works. I've only ever coded in Microsoft excel and although I'm not completely lost, I'm still pretty lost.

我正在寻找可以选择范围的代码，然后下载为.."选择为 PDF，适合宽度、纵向、无网格.理想情况下，可以将此 PDF 发送到我桌面上的特定文件夹，但我不知道应用程序脚本是否支持.

I am looking for code that would select a range, then "Download as.." the selection to PDF, fit width, portrait, no grid. Ideally this PDF could be sent to a specific folder on my desktop, but I don't know if the apps script supports that.

我找到了(我认为)建立宽度、纵向、无网格所需的方法.我所做的所有其他搜索都将整个工作表转换为 PDF 并通过电子邮件发送给它，这不是我想要做的，我找不到这些示例代码的哪些部分要删除，因为看起来 PDF 被识别为网络地址.任何帮助将不胜感激.

I've found the methods needed (I think) to establish the width, portrait, no grid. All the other searches I've done convert the entire sheet to a PDF and email it which isn't what I want to do and I couldn't find what parts of those sample codes to delete because it looks like PDFs are recognized as web addresses. Any help would be greatly appreciated.

推荐答案

可以从 Google 表格中导出范围，而无需隐藏工作表标签、行和列，或创建临时的新电子表格.

It is possible to export a range from your Google Sheet without hiding sheet tabs, rows and columns, or creating a temporary new spreadsheet.

function exportRangeToPDf(range) {
  var blob,exportUrl,options,pdfFile,response,sheetTabNameToGet,sheetTabId,ss,ssID,url_base;

  range = range ? range : "C4:D4";//Set the default to whatever you want

  sheetTabNameToGet = "Sheet1";//Replace the name with the sheet tab name for your situation
  ss = SpreadsheetApp.getActiveSpreadsheet();//This assumes that the Apps Script project is bound to a G-Sheet
  ssID = ss.getId();
  sh = ss.getSheetByName(sheetTabNameToGet);
  sheetTabId = sh.getSheetId();
  url_base = ss.getUrl().replace(/edit$/,'');

  //Logger.log('url_base: ' + url_base)

  exportUrl = url_base + 'export?exportFormat=pdf&format=pdf' +

    '&gid=' + sheetTabId + '&id=' + ssID +
    '&range=' + range + 
    //'&range=NamedRange +
    '&size=A4' +     // paper size
    '&portrait=true' +   // orientation, false for landscape
    '&fitw=true' +       // fit to width, false for actual size
    '&sheetnames=true&printtitle=false&pagenumbers=true' + //hide optional headers and footers
    '&gridlines=false' + // hide gridlines
    '&fzr=false';       // do not repeat row headers (frozen rows) on each page

  //Logger.log('exportUrl: ' + exportUrl)

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

  options.muteHttpExceptions = true;//Make sure this is always set

  response = UrlFetchApp.fetch(exportUrl, options);

  //Logger.log(response.getResponseCode())

  if (response.getResponseCode() !== 200) {
    console.log("Error exporting Sheet to PDF!  Response Code: " + response.getResponseCode());
    return;

  }
  
  blob = response.getBlob();

  blob.setName('AAA_test.pdf')

  pdfFile = DriveApp.createFile(blob);//Create the PDF file
  //Logger.log('pdfFile ID: ' +pdfFile.getId())
}

已使用多种策略将 Google 表格的一部分导出为 PDF.

There are various strategies that have been used to export part of a Google Sheet to a PDF.

• 创建一个仅包含所需内容的新电子表格文件
• 创建一个新的工作表标签，将内容放入工作表标签并隐藏任何其他工作表标签，隐藏不需要的行和列
• 使用范围设置构建下载 URL
  • 使用范围地址
  • 使用命名范围 - &gid=sheetId&range=NamedRange

  我会使用在下载 URL 中包含的范围构建下载 URL 的方法.

  I would use the method of building a download URL with the range included in the download URL.

  这篇关于在 Google Apps Script 中将范围导出为 PDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！