使用pdf选项将Google电子表格的新版本导出（或打印）为pdf文件 [英] Export (or print) with a google script new version of google spreadsheets to pdf file, using pdf options
问题描述
我试图制作一个谷歌脚本，用于将页面参数（纵向/横向，...）导出（或打印）新版Google电子表格（或表单）为pdf。 b
我对此进行了研究并找到了可能的解决方案此处。
有几种类似的解决方案，但只适用于老版本的Google电子表格。
请考虑以下代码：
函数exportAsPDF（）{$ b $ b //此代码从新版电子表格运行
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（ 匿名）;
var requestData = {method：GET，oAuthServiceName：google，oAuthUseToken：always};
var ssID1 =0AhKhywpH -YlQdDhXZFNCRFROZ3NqWkhBWHhYTVhtQnc; //旧版电子表格的ID
var ssID2 =10xZX9Yz95AUAPu92BkBTtO0fhVk9dz5LxUmJQsJ7yPM; //新版电子表格的ID
var ss1 = SpreadsheetApp.openById（ssID1）; //旧版本ss对象
var ss2 = SpreadsheetApp.openById（ssID2）; //新版本的ss对象
var sID1 = ss1.getActiveSheet（）。getSheetId（）。toString（）; //旧版表单ID
var sID2 = ss2.getActiveSheet（）。getSheetId（）。toString（）; //新版本图纸ID
//对于旧版本，这运行正常。
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参数。新版本的正确网址必须与 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）; ：错误OAuth ...我不知道如何继续......我测试过数百种变化没有好结果。
任何想法？新版电子表格的范围 docs.google.com/feeds 是否正确？是正确的 oauthConfig ？
在此先感谢。
这是我的spreadsheet-pdf脚本。它适用于新的Google Spreadsheet API。
//将电子表格转换为PDF文件。
函数spreadsheetToPDF（id，index，url，name）
{
SpreadsheetApp.flush（）;
//定义有用变量
var oauthConfig = UrlFetchApp.addOAuthService（google）;
var scope =https://docs.google.com/feeds/;
//使OAuth连接
oauthConfig.setAccessTokenUrl（https://www.google.com/accounts/OAuthGetAccessToken）;
oauthConfig.setRequestTokenUrl（https://www.google.com/accounts/OAuthGetRequestToken?scope=+范围）;
oauthConfig.setAuthorizationUrl（https://www.google.com/accounts/OAuthAuthorizeToken）;
oauthConfig.setConsumerKey（anonymous）;
oauthConfig.setConsumerSecret（anonymous）;
//获取请求
var request = {
method：GET，
oAuthServiceName：google，
oAuthUseToken ：always，
muteHttpExceptions：true
};
//定义参数URL以获取
var params ='？gid ='+ index +'& fitw = true& exportFormat = pdf& format = pdf& size = A4& portrait =真安培; sheetnames =假安培; printtitle =假安培;网格线=假';
//获取文件url
var blob = UrlFetchApp.fetch（https://docs.google.com/a/+ url +/ spreadsheets / d /+ id + / export+ params，request）;
blob = blob.getBlob（）。setName（name）;
//返回文件
返回blob;
$ b我必须使用muteHttpExceptions URL。使用此参数，我下载了带有HTML扩展名的文件，以便使用我的最终网址获得永久移动页面（ https://docs.google.com/a/ + url +/ spreadsheets / d /+ id +/ export+ params）。
<请注意，我在一个组织中，所以我必须指定它的域名（url参数，即mydomain.com）。
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.
Please, consider this code:
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");
}
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"
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.
Any ideas? is correct the scope docs.google.com/feeds for new version of spreadsheets? is correct the oauthConfig?
Thanks in advance.
解决方案 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;
}
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").
And note that I am in an organization. So I've had to specify its domain name ("url" parameter, ie "mydomain.com").
这篇关于使用pdf选项将Google电子表格的新版本导出（或打印）为pdf文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
