Google SpreadSheet Scripts for Better Printing

Justin | GoogleDocs | Tuesday, February 14th, 2012

I’ve been using Google Docs more lately. Especially for spreadsheets. One thing that I noticed when trying to print is the effect of excess empty cells. When you choose File > Print in Google Docs, the system renders a PDF from the server and prompts you to save that PDF. If you have several empty rows and column, those will be included in your PDF. If you selected Fit to Page (the default), your text (like mine) could be tiny. I went looking for a Set Print Area option similar to the one found in MS Excel, but could not find it. So I wrote a few custom scripts to solve the issue instead.

Locating the Scripts

The 2 scripts below have been submitted to the Google Script Gallery and should be available by choosing Tools > Script gallery… then searching for the term crop. As of writing this post, the first script below is already available.

I’m including the code below in case anyone has difficulty finding them or would like to copy and modify the code for their own purposes.

Crop to Selection

Crops cells to the current selection for better printing.
function cropToSelection() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = sSheet.getActiveSheet();
  var range = sSheet.getActiveSelection();
  var startCol = range.getLastColumn();
  var startRow = range.getLastRow();
  var endCol = sheet.getMaxColumns();
  var endRow = sheet.getMaxRows();
  try{sSheet.deleteColumns(startCol, endCol - startCol);} catch(e){};
  try{sSheet.deleteRows(startRow, endRow - startRow);} catch(e){};
}

Crop All Sheets

Crops each sheet in the worksheet to the cells that contain content.
function cropAllSheets() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var numSheets = sSheet.getNumSheets();
  var sheets = sSheet.getSheets();
  var i;
  for(i=0; i < numSheets; i++){
    var sheet = sheets[i];
    var range = sheet.getDataRange();
    sheet.setActiveSelection(range);
    var startCol = range.getLastColumn();
    var startRow = range.getLastRow();
    var endCol = sheet.getMaxColumns();
    var endRow = sheet.getMaxRows();
    if(endCol > startCol) sheet.deleteColumns(startCol, endCol - startCol);
    if(endRow > startRow) sheet.deleteRows(startRow, endRow - startRow);
  }
}

Get updates from Ajar Productions

Splittext id 400px

Sign up today and get the InDesign Split Text premium extension for free!

Unsubscribe at any time. Powered by ConvertKit

One thought on “Google SpreadSheet Scripts for Better Printing”

  1. Traci Freese says:

    Thank you. It is an addon now but this led me to it!

Leave a Reply

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