使用 pdf 选项使用 google 脚本将新版本的 google 电子表格导出(或打印)为 pdf 文件

问题描述

我正在尝试制作一个谷歌脚本，用于将新版本的谷歌电子表格(或工作表)导出(或打印)为 pdf，并带有页面参数(纵向/横向，...)

I'm trying to make a google script for exporting (or printing) a new version of google spreadsheet (or sheet) to pdf, with page parameters (portrait/landscape, ...)

我对此进行了研究并找到了一个可能的解决方案 此处.有几个类似的解决方案，但只适用于旧版本的谷歌电子表格.

I've researched about this and found a possible solution here.There are several similar solutions like this, but only work with old version of google spreadsheet.

请考虑以下代码:

function exportAsPDF() {
  //This code runs from a NEW version of spreadsheet 

  var oauthConfig = UrlFetchApp.addOAuthService("google");
  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://spreadsheets.google.com/feeds/");
  oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oauthConfig.setConsumerKey("anonymous");  oauthConfig.setConsumerSecret("anonymous");
  var requestData = { "method": "GET", "oAuthServiceName": "google","oAuthUseToken": "always" };

  var ssID1="0AhKhywpH-YlQdDhXZFNCRFROZ3NqWkhBWHhYTVhtQnc"; //ID of an Old version of spreadsheet
  var ssID2="10xZX9Yz95AUAPu92BkBTtO0fhVk9dz5LxUmJQsJ7yPM"; //ID of a NEW version of spreadsheet 

  var ss1 = SpreadsheetApp.openById(ssID1);  //Old version ss object
  var ss2 = SpreadsheetApp.openById(ssID2);  //New version ss object
  var sID1=ss1.getActiveSheet().getSheetId().toString();  // old version sheet id
  var sID2=ss2.getActiveSheet().getSheetId().toString();  // new version sheet id

  //For Old version, this runs ok.
  var url1 = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="+ssID1+"&gid="+sID1+"&portrait=true"+"&exportFormat=pdf";
  var result1 = UrlFetchApp.fetch(url1 , requestData);
  var contents1=result1.getBlob();
  var pdfFile1=DriveApp.createFile(contents1).setName("FILE1.pdf");

  //////////////////////////////////////////////
  var url2 = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="+ssID2+"&gid="+sID2+"&portrait=true"+"&exportFormat=pdf";
  var result2 = UrlFetchApp.fetch(url2 , requestData);
  var contents2=result2.getBlob();
  var pdfFile2=DriveApp.createFile(contents2).setName("FILE2.pdf");
}

它正常工作并生成文件FILE1.pdf"，可以正确打开.但是对于新版本的电子表格，它会在var result2 = UrlFetchApp.fetch(url2 , requestData);"处导致错误 302(截断服务器响应).嗯，没关系，因为新版本的 url 格式不包含key"参数.新版本的正确 url 必须类似于 "https://docs.google.com/spreadsheets/d/"+ssID2+"/export?gid="+sID2+"&portrait=true&format=pdf"

It works right and generates the file "FILE1.pdf", that can be opened correctly. But for the new version of spreadsheet, it results in error 302 (truncated server response) at "var result2 = UrlFetchApp.fetch(url2 , requestData);". Well, it’s ok because the url format for the new version doesn’t include the "key" argument. A correct url for new versions must be like "https://docs.google.com/spreadsheets/d/"+ssID2+"/export?gid="+sID2+"&portrait=true&format=pdf"

将此用于 url2 (var url2 = "https://docs.google.com/spreadsheets/d/"+ssID2+"/export?gid="+sID2+"&portrait=true&format=pdf") 它再次失败并出现错误无法为服务执行授权:google".好吧，这个错误可能是由于 RequestTokenUrl 的范围不正确造成的.我找到了替代范围 https://docs.google.com/feeds 并设置了它:oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://docs.google.com/feed/");代码再次运行后，在 UrlFetchApp.fetch(url2 , requestData); 行出现新错误:Error OAuth"……我不知道如何继续……我已经测试了数百种变体，但没有很好的结果.

Using this for url2 (var url2 = "https://docs.google.com/spreadsheets/d/"+ssID2+"/export?gid="+sID2+"&portrait=true&format=pdf") it fails again with error "Authorization can’t be performed for service: google". Well, this error could be due to an incorrect scope for the RequestTokenUrl. I’ve found the alternative scope https://docs.google.com/feeds and set it: oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://docs.google.com/feed/"); After the code runs again, a new error happens at the line with UrlFetchApp.fetch(url2 , requestData);: "Error OAuth" … I don’t know how to continue … I’ve tested hundreds of variations without good results.

有什么想法吗?新版电子表格的范围 docs.google.com/feeds 是否正确?oauthConfig 是否正确?

Any ideas? is correct the scope docs.google.com/feeds for new version of spreadsheets? is correct the oauthConfig?

提前致谢.

推荐答案

这是我的电子表格到 pdf 的脚本.它适用于新的 Google 电子表格 API.

Here is my spreadsheet-to-pdf script. It works with the new Google Spreadsheet API.

// Convert spreadsheet to PDF file.
function spreadsheetToPDF(id,index,url,name)
{
  SpreadsheetApp.flush();

  //define usefull vars
  var oauthConfig = UrlFetchApp.addOAuthService("google");
  var scope = "https://docs.google.com/feeds/";

  //make OAuth connection
  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
  oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");

  //get request
  var request = {
    "method": "GET",
    "oAuthServiceName": "google",
    "oAuthUseToken": "always",
    "muteHttpExceptions": true
  };

  //define the params URL to fetch
  var params = '?gid='+index+'&fitw=true&exportFormat=pdf&format=pdf&size=A4&portrait=true&sheetnames=false&printtitle=false&gridlines=false';

  //fetching file url
  var blob = UrlFetchApp.fetch("https://docs.google.com/a/"+url+"/spreadsheets/d/"+id+"/export"+params, request);
  blob = blob.getBlob().setName(name);

  //return file
  return blob;
}

我不得不使用muteHttpExceptions"参数来准确地知道新的 URL.使用此参数，我下载了带有 HTML 扩展名的文件，以获取带有最终网址的永久移动"页面(https://docs.google.com/a/"+url+"/spreadsheets/d/"+id+"/export"+params").

I've had to use the "muteHttpExceptions" parameter to know exactly the new URL. With this parameter, I downloaded my file with the HTML extension to get a "Moved permanently" page with my final url ("https://docs.google.com/a/"+url+"/spreadsheets/d/"+id+"/export"+params").

请注意，我在一个组织中.所以我不得不指定它的域名(url"参数，即mydomain.com").

And note that I am in an organization. So I've had to specify its domain name ("url" parameter, ie "mydomain.com").

这篇关于使用 pdf 选项使用 google 脚本将新版本的 google 电子表格导出(或打印)为 pdf 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！