Print several non-consecutive columns of a Google Spreadsheet using a script

Question

I have script for print. I need print range without some column (e.g. print just B,C,D,E,G and miss F) and without all notes in sheet.

The script below prints all the columns.

function printPdf() { 
    SpreadsheetApp.flush();
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();


var gid = sheet.getSheetId();


var pdfOpts = '&size=A4&fzr=false&portrait=false&fitw=true&gridlines=false&printtitle=false&sheetnames=false&pagenum=UNDEFINED&attachment=false&gid='+gid;
var last_row = sheet.getLastRow();
var printRange = '&c1=0' + '&r1=0' + '&c2=34' + '&r2='+last_row // B2:APn
var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' + pdfOpts  + printRange;

var app = UiApp.createApplication().setWidth(200).setHeight(50);
app.setTitle('Verze pro tisk');

var link = app.createAnchor('Zobrazit PDF', url).setTarget('_new');

app.add(link);

ss.show(app);

}

Answer 1

I don't know of a way to select a non-contiguous range via URL parameters. The solution I found is to create a new temporary spreadsheet, copy selected columns there, and export the new sheet into PDF. In the script, the array cols holds the columns to be taken: B-E and G correspond to 2,3,4,5,7.

Version 1: print without formatting

function printPDF() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var cols = [2, 3, 4, 5, 7];    // columns to print
  var lastRow = sheet.getLastRow();
  var newSs = SpreadsheetApp.create(ss.getName() + ' for print', lastRow, cols.length);
  var newSheet = newSs.getSheets()[0];
  for (var i = 0; i < cols.length; i++) {
    var values = sheet.getRange(1, cols[i], lastRow, 1).getValues();
    newSheet.getRange(1, i + 1, lastRow, 1).setValues(values);
  }
  SpreadsheetApp.flush();

  //  copied from your code, including deprecated UiApp  
  var pdfOpts = '&size=A4&fzr=false&portrait=false&fitw=true&gridlines=false&printtitle=false&sheetnames=false&pagenum=UNDEFINED&attachment=false&gid=0';
  var url = newSs.getUrl().replace(/edit$/, '') + 'export?format=pdf' + pdfOpts;
  var app = UiApp.createApplication().setWidth(200).setHeight(50);
  app.setTitle('Print Version');
  var link = app.createAnchor('Show PDF', url).setTarget('_new');
  app.add(link);
  ss.show(app);
  // End of copy

  DriveApp.getFileById(newSs.getId()).setTrashed(true); // delete temp file
}

Version 2: print with formatting

Similar to the above, but instead of creating a new spreadsheet, a new sheet 'print' is inserted (if such a sheet already exists, it is reused).

function printPDFold() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var cols = [2, 3, 4, 5, 7];
  var lastRow = sheet.getLastRow();
  var newSheet = ss.getSheetByName('print');
  if (!newSheet) {
    newSheet = ss.insertSheet('print');
  }
  var gid = newSheet.getSheetId();
  for (var i = 0; i < cols.length; i++) {
    sheet.getRange(1, cols[i], lastRow, 1).copyTo(newSheet.getRange(1, i + 1, lastRow, 1), {contentsOnly: true});
    sheet.getRange(1, cols[i], lastRow, 1).copyTo(newSheet.getRange(1, i + 1, lastRow, 1), {formatOnly: true});
  }
  SpreadsheetApp.flush();
  var pdfOpts = '&size=A4&fzr=false&portrait=false&fitw=true&gridlines=false&printtitle=false&sheetnames=false&pagenum=UNDEFINED&attachment=false&gid=' + gid;
  var printRange = '&c1=0&r1=0&c2=' + cols.length + '&r2=' + lastRow;
  var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' + pdfOpts + printRange;
  var app = UiApp.createApplication().setWidth(200).setHeight(50);
  app.setTitle('Print Version');
  var link = app.createAnchor('Show PDF', url).setTarget('_new');
  app.add(link);
  ss.show(app);
}

The presence of two copyTo commands may look strange, but this is necessary because:

• contentsOnly: true is for copying the values and not the formulas (since formulas will likely break if moved to another sheet where not all columns are present)
• formatOnly: true is to copy the formatting.

Unfortunately, it does not seem to be possible to hide the 'print' sheet: if it's hidden, the PDF file fails to load.

Remark

As an alternative to export via URL parameters, one can save a PDF to Drive from where it can be opened as before. Here is an outline of this approach; I don't know how to turn off gridlines, etc.

  var file = DriveApp.createFile(newSs.getAs('application/pdf'));
  file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  var url = file.getUrl();