I'm trying to sent a print job to one of my printers in Cloud Print.
The information I would like to print is the current range the user is selecting inside a Spreadsheet.
For this I have used this guide: https://www.labnol.org/code/20061-google-cloud-print-with-apps-script in order to set up the printer with GAS.
Step 1: Get the information you want to print
Source: How to Print sheet/range using .gs script in Google Sheets?
Explanation: carlesgg97's solution opens a modal window that will show our spreadsheet range exported as a PDF. From that point the user would need to manually print the job.
However, the information is sent to the Html via script.
Code adapted:
var PRINT_OPTIONS = {
'size': 7, // paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B
'fzr': false, // repeat row headers
'portrait': false, // false=landscape
'fitw': true, // fit window or actual size
'gridlines': false, // show gridlines
'printtitle': false,
'sheetnames': false,
'pagenum': 'UNDEFINED', // CENTER = show page numbers / UNDEFINED = do not show
'attachment': false
}
var PDF_OPTS = objectToQueryString(PRINT_OPTIONS);
function printSelectedRange() {
SpreadsheetApp.flush();
var ss = SpreadsheetApp.getActiveSpreadsheet(); //Our Spreadsheet
var sheet = ss.getActiveSheet(); //Our Active Sheet
var range = sheet.getActiveRange(); // Our Active Range
var gid = sheet.getSheetId();
var printRange = objectToQueryString({
'c1': range.getColumn() - 1,
'r1': range.getRow() - 1,
'c2': range.getColumn() + range.getWidth() - 1,
'r2': range.getRow() + range.getHeight() - 1
});
//Here is variable that we will send to the htmlTemplate
//We will build the HtmlTemplate using this url
var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' +
PDF_OPTS + printRange + "&gid=" + gid;
var htmlTemplate = HtmlService.createTemplateFromFile('js');
//Here I'm wrongly trying to get the content from the Html.
//What I'll get is the content of js.html and not the info from the url
var pdf_html = htmlTemplate.evaluate().getContent();
//The blob needs to be build using an object.
//However I don't know how to access the pdf I'm exporting inside the url var
var blob = Utilities.newBlob(pdf_html, MimeType.HTML, 'temp').getAs(MimeType.PDF);
//random printer ID
var printerID = 'b71f9cd2-3e3e-qweq-12asd-aogt5keoqoa2';
printGoogleDocument(blob, printerID, 1);
}
function objectToQueryString(obj) {
return Object.keys(obj).map(function(key) {
return Utilities.formatString('&%s=%s', key, obj[key]);
}).join('');
}
js.html:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<script>
window.open('<?=url?>', '_blank', 'width=800, height=600');
google.script.host.close();
</script>
</body>
</html>
Step 2: Print the Job
function printGoogleDocument(docBlob, printerID, docName) {
var ticket = {
version: "1.0",
print: {
color: {
type: "STANDARD_COLOR",
vendor_id: "Color"
},
duplex: {
type: "NO_DUPLEX"
}
}
};
var payload = {
"printerid" : printerID,
"title" : docName,
"content" : docBlob,
"contentType": "application/pdf",
"ticket" : JSON.stringify(ticket)
};
var response = UrlFetchApp.fetch('https://www.google.com/cloudprint/submit', {
method: "POST",
payload: payload,
headers: {
Authorization: 'Bearer ' + getCloudPrintService().getAccessToken()
},
"muteHttpExceptions": true
});
response = JSON.parse(response);
if (response.success) {
Logger.log("%s", response.message);
} else {
Logger.log("Error Code: %s %s", response.errorCode, response.message);
}
}
I believe that what I need to modify is how the HtmlTemplate is build so that I can create a correct BLOB and send it to the printer.
EDIT
Applying Tanaike recommendation it works just fine when I call the function directly from the Script Editor.
Although in the scenario where we want to call this function printSelectedRange() using another function I got the following error:
Exception: Request failed for https://docs.google.com returned code 500.
Truncated server response: <!DOCTYPE html><html lang="en"><head>
<metaname="description" content="Web word processing, presentations and
spreadsheets"><meta name="viewport" c... (use muteHttpExceptions option to`
examine full response)`
The error points out to this line of code
var blob = UrlFetchApp.fetch(url, {headers: {authorization: "Bearer " +
ScriptApp.getOAuthToken()}}).getBlob();
In order to avoid this I updated the parameters for the UrlFetchApp:
var htmlTemplate = HtmlService.createTemplateFromFile('js');
htmlTemplate.url = url;
var params = {
headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()},
muteHttpExceptions: true
};
var blob = UrlFetchApp.fetch(url, params).getBlob();
var printerID = 'randomprinterID';
printGoogleDocument(blob, printerID, 1);
How about this modification?
In this modification, blob is directly retrieved from url as the PDF data.
var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' +
PDF_OPTS + printRange + "&gid=" + gid;
var htmlTemplate = HtmlService.createTemplateFromFile('js');
//Here I'm wrongly trying to get the content from the Html.
//What I'll get is the content of js.html and not the info from the url
var pdf_html = htmlTemplate.evaluate().getContent();
//The blob needs to be build using an object.
//However I don't know how to access the pdf I'm exporting inside the url var
var blob = Utilities.newBlob(pdf_html, MimeType.HTML, 'temp').getAs(MimeType.PDF);
//random printer ID
var printerID = 'b71f9cd2-3e3e-qweq-12asd-aogt5keoqoa2';
printGoogleDocument(blob, printerID, 1);
var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' + PDF_OPTS + printRange + "&gid=" + gid;
var blob = UrlFetchApp.fetch(url, {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()}}).getBlob();
var printerID = 'b71f9cd2-3e3e-qweq-12asd-aogt5keoqoa2';
printGoogleDocument(blob, printerID, 1);