I have a script on my Google sheets that sends out a sheet converted as PDF to a mailing list. This script is set up on many different spreadsheets, but I found that some of them were starting to fail in the last coupe of days.
Error content is:
Exception: Request failed for https://docs.google.com returned code 500. Truncated server response: <meta name="viewport" c... (use muteHttpExceptions option to examine full response)
It seems that OAuthToken is not working on these spreadsheets, but it is working on others.
Here is the code:
function sendSheetToPdf(){ // this is the function to call
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = SpreadsheetApp.getActive().getSheetByName('Acknowledged Orders');
// if you change the number, change it also in the parameters below
var ssC = SpreadsheetApp.getActive().getSheetByName('Acknowledged Orders')
var toEmail = ""
var shName = sh.getName()
if (sh.getRange("A11").getValue()!="Rows") {
sendSpreadsheetToPdf(sh, shName, toEmail,"Daily Acknowledged Orders", "Daily Acknowledged Orders");
}
}
function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var spreadsheetId = spreadsheet.getId()
var sheetId = sheetNumber ? '0' : null;
var url_base = spreadsheet.getUrl().replace(/edit$/,'');
var url_ext = 'export?exportFormat=pdf&format=pdf' //export as pdf
+ (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))
// following parameters are optional...
+ '&size=A3' // paper size
+ '&portrait=true' // orientation, false for landscape
+ '&fitw=true' // fit to width, false for actual size
+ '&sheetnames=false&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
var options = {
headers: {
'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(),
}
}
var response = UrlFetchApp.fetch(url_base + url_ext, options);
var blob = response.getBlob().setName(pdfName + '.pdf');
if (email) {
var mailOptions = {
attachments:blob, htmlBody:htmlbody
}
var ccEmail = ""
MailApp.sendEmail(
email+","+ccEmail,
subject+" (" + pdfName +")",
"html content only",
mailOptions);
}
}
The response received:
<!DOCTYPE html><html lang="en"><head><meta name="description" content="Web word processing, presentations and spreadsheets"><meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0, user-scalable=0"><link rel="shortcut icon" href="//ssl.gstatic.com/docs/common/drive_favicon1.ico"><title>Error</title><meta name="referrer" content="origin"><link href="//fonts.googleapis.com/css?family=Product+Sans" rel="stylesheet" type="text/css"><style nonce="VuOBJHMXpK2pKNeVo731zA">/* Copyright 2020 Google Inc. All Rights Reserved. */
.goog-inline-block{position:relative;display:-moz-inline-box;display:inline-block}* html .goog-inline-block{display:inline}*:first-child+html .goog-inline-block{display:inline}#drive-logo{margin:18px 0;position:absolute;white-space:nowrap}.docs-drivelogo-img{background-image:url('//ssl.gstatic.com/images/branding/googlelogo/1x/googlelogo_color_116x41dp.png');background-size:116px 41px;display:inline-block;height:41px;vertical-align:bottom;width:116px}.docs-drivelogo-text{color:#000;display:inline-block;opacity:0.54;text-decoration:none;font-family:'Product Sans',Arial,Helvetica,sans-serif;font-size:32px;text-rendering:optimizeLegibility;position:relative;top:-6px;left:-7px;-webkit-font-smoothing:antialiased;-moz-osx-font-smoothing:grayscale}@media (-webkit-min-device-pixel-ratio:1.5),(min-resolution:144dpi){.docs-drivelogo-img{background-image:url('//ssl.gstatic.com/images/branding/googlelogo/2x/googlelogo_color_116x41dp.png')}}</style><style type="text/css" nonce="VuOBJHMXpK2pKNeVo731zA">body {background-color: #fff; font-family: Arial,sans-serif; font-size: 13px; margin: 0; padding: 0;}a, a:link, a:visited {color: #112ABB;}</style><style type="text/css" nonce="VuOBJHMXpK2pKNeVo731zA">.errorMessage {font-size: 12pt; font-weight: bold; line-height: 150%;}</style></head><body><div id="outerContainer"><div id="innerContainer"><div style="position: absolute; top: -80px;"><div style="margin: 18px 0; position: absolute; white-space: nowrap;"><a href="//support.google.com/docs/"><img height="35px" src="//ssl.gstatic.com/docs/common/product/spreadsheets_lockup1.png" alt="Google logo"/></a></div></div><p style="padding-top: 15px">Google Docs encountered an error. Please try reloading this page, or coming back to it in a few minutes.</p><p>To learn more about the Google Docs editors, please visit our <a href="https://support.google.com/docs/?hl=en&p=error_help" target="_blank">help center</a>.</p><p><br><b>We're sorry for the inconvenience.</b><br><i>- The Google Docs Team</i></p></div></div></body><style nonce="VuOBJHMXpK2pKNeVo731zA">html {height: 100%; overflow: auto;}body {height: 100%; overflow: auto;}#outerContainer {margin: auto; max-width: 750px;}#innerContainer {margin-bottom: 20px; margin-left: 40px; margin-right: 40px; margin-top: 80px; position: relative;}</style></html>
