Export Google Sheets to PDF without cell gridlines

Question

I'm trying to export PDF from Google Sheets via Apps Script. I've found useful code online, which works perfectly, except I can't find a way to export it without the grid, or change margins and page size.

function generatePdf() {
  var originalSpreadsheet = SpreadsheetApp.getActive();
  var sourcesheet = originalSpreadsheet.getSheetByName("TestSheet");
  var sourcerange = sourcesheet.getRange('B1:K55');  // range to get - here I get all of columns which i want
  var sourcevalues = sourcerange.getValues();
  var data = sourcesheet.getDataRange().getValues();

  var number =  originalSpreadsheet.getRange('G9:H9').getValue();

  var newSpreadsheet = SpreadsheetApp.create("Invoice pdf"); // can give any name.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var projectname = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = sourcesheet.copyTo(newSpreadsheet);
  var destrange = sheet.getRange('B1:K55');
  destrange.setValues(sourcevalues);
  newSpreadsheet.getSheetByName('Sheet1').activate();
  newSpreadsheet.deleteActiveSheet();

  var invoiceName = "Invoice "+number;
  var pdf = DriveApp.getFileById(newSpreadsheet.getId());
  var theBlob =     pdf.getBlob().getAs('application/pdf').setName(invoiceName);

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

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

I've been looking for answers everywhere, but I cannot apply some of the solutions I find to my code.

Are these threads useful for your situation? https://stackoverflow.com/questions/30367547 and https://stackoverflow.com/questions/46088042 — Tanaike, Aug 16 '18 at 08:20
I can't find a way to implement these solutions to my code =\ — Adam Butenko, Aug 16 '18 at 08:52
I'm really sorry for the inconvenience. I couldn't understand about your current issue from your reply. Can you provide the detail information of it? If you can do, can you update your question by including your latest script? — Tanaike, Aug 17 '18 at 01:24
the script above is the latest, i need to find a way to set the pdf to print without the grid and without the margins. thanks — Adam Butenko, Aug 19 '18 at 08:35
Thank you for replying. I think that you can get it by exporting spreadsheet using the query parameters. The query parameters and how to export it can be seen at above 2 references. — Tanaike, Aug 19 '18 at 22:04

score 1 · Answer 1 · answered Mar 23 '21 at 16:07

I'm not sure how to do it with the code, but if you want to do it without code... when downloading from Google Sheets, you have the option to remove the cell gridlines. Here are the directions:

File > Download > PDF Document (.pdf)
This will open a preview screen. Sidebar on the right has a dropdown menu called "Formatting."
Under "Formatting," uncheck the box labeled "Show gridlines."

Thats it!

score 0 · Answer 2 · edited Dec 18 '22 at 20:27

It’s not possible to pass options into the getAs function, but you can export the file yourself and download the URL:

function getPdf(spreadsheet) {
  var options = {
    format:       'pdf',
    exportFormat: 'pdf',
    size:         7, // A4
    portrait:     true,
    gridlines:    false,
  };

  // construct export URL
  var query = Object.keys(options).map(function (key) {
    return encodeURIComponent(key) + '=' + encodeURIComponent(options[key]);
  }).join('&');
  var exportUrl = spreadsheet.getUrl().replace(/\/edit.*$/, '/export?' + query);

  var response = UrlFetchApp.fetch(exportUrl, {
    headers: {
      Authorization: 'Bearer ' +  ScriptApp.getOAuthToken(),
    },
  });

  return {
    fileName: spreadsheet.getName() + '.pdf',
    content:  response.getBlob().getBytes(),
    mimeType: MimeType.PDF,
  };
}

The full list of export options can be found at https://stackoverflow.com/a/46312255.

Export Google Sheets to PDF without cell gridlines

2 Answers2