GAS – Create a pdf from a Spreadsheet tab

This should be easy peasy, but it actually takes a fair bit of scripting to get it to work, especially if you have more than one tab on your base spreadsheet.

1. Get spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();

2. Get sheet by name (replace “Sheet2” with name of tab)
var sh = ss.getSheetByName("Sheet2");

3. Set the folder ID of the save folder (you find this in Google Drive)
var folderID = "0B1quMlsbdXXdQVBSemFaOGJkTFU";

4. Now we make a copy of the current spreadsheet and remove all sheets except the one we want to save
var folder = DriveApp.getFolderById(folderID);
//Copy whole spreadsheet
var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(ss.getId()).makeCopy("tmp_convert_to_pdf", folder))
//delete redundant sheets
var sheets = destSpreadsheet.getSheets();
for (i = 0; i < sheets.length; i++) { if (sheets[i].getSheetName() != sheetName){ destSpreadsheet.deleteSheet(sheets[i]); } }

5. We now create the pdf

// name for the pdf - choose your own or make up from variables
var pdfName = "MySheetPDF1";
var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);

6. Finally some tidying up to remove the temporary spreadsheet created
DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);

You should now find a pdf of your sheet/tab in the folder you selected.

Putting it all together:

Leave a Reply

Your email address will not be published. Required fields are marked *