Is there a way to create a pdf with google apps script that includes only a specific sheet of a spreadsheet?

Question

In the spreadsheet app itself, you have the options to print only a specific sheet. You also can turn off the grid lines when creating the pdf from within the app. Is there any way to do this via script when creating the pdf?

Intersidereal

Answer 1

Take a look at this code snippet: https://gist.github.com/4169590

What it does is uses Google Spreadsheet built-in download as PDF functionality so the same sort of customization is possible, including hiding gridlines and specifying a worksheet. Play with GET parameters to get the desirable output. In the end, script converts PDF file to a blob which you can manipulate further in your own script. To get this running though you'll need an additional authorization for Spreadsheet feeds scope.

Hope it helps.

Jacobina

Get an error saying "Script is using OAuth config which has been shut down" – Epiphragm

@Epiphragm This is a very old example. Look at the timestamp of the post! Apps Script has changed and evolved quite a bit since then. However, I don't think there's still a way to perform this particular task using built-in API. The gist of the old method still works though! I've modified the example to be working again: gist.github.com/rcknr/93c8fdd88dc9cf90a4628c847d24fc95 – Jacobina

Yes I saw! Thanks for changing it! – Epiphragm

Answer 2

@rcknr, worked for me, more options below. (From: http://productforums.google.com/forum/#!msg/apps-script/wQvCF6yY1Qk/R0uyVmf-Xx0J )

URL like this: https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=tOHm3f8tdIxf7tSZzWvBGiA&gid=0&size=legal&fitw=true&gridlines=false&portrait=false&exportFormat=pdf

other settings

fmcmd=12
size=legal/A4
fzr=true/false
portrait=false/true
fitw=true/false
gid=0/1/2  
gridlines=false/true
printtitle=false/true
sheetnames=false/true
pagenum=UNDEFINED
attachment=false/true  

true/false where sometimes required, I could not use 0/1 instead.

Clyve

Answer 3

Google apps script to create a pdf of a specific sheet of a spreadsheet.

function generatePdf() {
    var originalSpreadsheet = SpreadsheetApp.getActive();

    var sourcesheet = originalSpreadsheet.getSheetByName("Sheet - Name");
    var sourcerange = sourcesheet.getRange('A1:G7');  // range to get - here I get all of columns which i want
    var sourcevalues = sourcerange.getValues();
    var data = sourcesheet.getDataRange().getValues();

    var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export"); // can give any name.
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var projectname = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = sourcesheet.copyTo(newSpreadsheet);
    var destrange = sheet.getRange('A1:G7');
    destrange.setValues(sourcevalues);
    newSpreadsheet.getSheetByName('Sheet1').activate();
    newSpreadsheet.deleteActiveSheet();

    var pdf = DriveApp.getFileById(newSpreadsheet.getId());
    var theBlob = pdf.getBlob().getAs('application/pdf').setName("name");

    var folderID = "Folder Id"; // Folder id to save in a folder.
    var folder = DriveApp.getFolderById(folderID);
    var newFile = folder.createFile(theBlob);

    DriveApp.getFileById(newSpreadsheet.getId()).setTrashed(true); 
}

Clerkly

Answer 4

Unfortunately this currently isn't possible with Apps Script, but you can file a feature request on our issue tracker.

Theorist

Answer 5

When you choose export to pdf, you get a messagebox with options. In the right side of this messagebox there are five checkboxes. Check the second one, then you will hide the gridlines in the export. I don't know the label of this checkbox, because I use another language than english.

Good luck!

- Robert

Nutritionist

Answer 6

this is a solution

var blob = DriveApp.getFileById(idsheet).getAs("application/pdf");
blob.setName("name".pdf");
var dossier = DriveApp.getFolderById("0Bx71KaTqXxQPQUYyVEVyYkJ0bEU");
dossier.createFile(blob);

Drenthe

Answer 7

I simply hide the sheets that I don't want put in the pdf

Bethsaida