Last active
March 22, 2023 01:26
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//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