Email a Custom Sheets as PDF in Attachment

Script export and send email of custom sheet on PDF in Attachment for Google Sheets with GAS (Google Apps Script) & Javascript Language, don't forget to change the variables according to your goal, read tips and watch video to understand more about how to run it even you are not a programmer

How to use it 

• Sheets you want to print :

you can add more inside the array

var wantedSheets=[' Sheet1','Sheet2','Sheet3'.....];

• Print Options :

• Email parameters :

Seperate each email with comma :

MailApp.sendEmail('pace3man@gmail.com,medrx82@gmail.com', 'Title', 'Body',...

The Code

	//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();
	}
	}

view raw ExportAndEmailCustomSheetsAsPDF.gs hosted with ❤ by GitHub

Please Comment and help us with share !