Skip to content

Instantly share code, notes, and snippets.

@chemiadel
Last active March 22, 2023 01:26
Show Gist options
  • Star 5 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save chemiadel/bee4cb03481b580275d5296a3e7adc0d to your computer and use it in GitHub Desktop.
Save chemiadel/bee4cb03481b580275d5296a3e7adc0d to your computer and use it in GitHub Desktop.
Script export and send email of custom sheet on PDF for Google Sheets with GAS
//https://gist.github.com/chemiadel/bee4cb03481b580275d5296a3e7adc0d
function EmailPDF() {
//Get Active Spreadsheet
var spreadSheet=SpreadsheetApp.getActiveSpreadsheet();
//Get All Sheets of the spreadsheets
var sheets=spreadSheet.getSheets();
//Sheet wanted to export on PDF
var wantedSheets=['Sheet1','Sheet2'];
//Save defaults hidden sheets
var arr=new Array();
for(var i in sheets){
if(sheets[i].isSheetHidden()){
arr.push(sheets[i].getSheetName());
}
}
//Hide All sheets except one from wantedsheets you can't hide all sheets
for(var j in sheets){
if(!sheets[j].isSheetHidden() && sheets[j].getSheetName()!=wantedSheets[0]){
sheets[j].hideSheet();
}
}
//Show Wanted sheets
for(var k in wantedSheets){
spreadSheet.getSheetByName(wantedSheets[k]).showSheet();
}
//Export URL with Parameters
var spreadSheetId = spreadSheet.getId();
var URL = "https://docs.google.com/spreadsheets/d/"+spreadSheetId+"/export"+
"?format=pdf&"+
"size=0&"+
"fzr=true&"+
"portrait=true&"+
"fitw=true&"+
"gridlines=true&"+
"printtitle=true&"+
"sheetnames=true&"+
"pagenum=CENTER&"+
"attachment=true";
//the HTTP method for the request: get and headers : authorization : Bearer tokens to access OAuth 2.0-protected resources
var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
//Return the data inside this object as a blob.
var response=UrlFetchApp.fetch(URL,params).getBlob();
//Email it
MailApp.sendEmail('To', 'Title', 'Body', {
attachments: [{
fileName: "FileTitle" + ".pdf",
content: response.getBytes(),
mimeType: "application/pdf"
}]
});
//Reshow all sheets
for(var x in sheets){
if(sheets[x].isSheetHidden()){
sheets[x].showSheet();
}
}
//Rehide default sheets
for(var y in arr){
spreadSheet.getSheetByName(arr[y]).hideSheet();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment