Sunday, September 20, 2015

Page Break for Google Sheets

Project Status: Working on a Workaround
Project Update: Page Break Progress
How you can help: Star and vote up this product forum topic

Unfortunately as of today there is still no built-in feature for Google Sheets to insert a page break. Fear not though; you can actually help to make this feature request a reality for Google Sheets by going to the product forums and clicking to star the post. The more people that go to this page and star the post the more likely Google is to implement the feature request.

You can also click the "Vote up this post" button for the main question posed at the top of the page which is "How do I insert a page break in a spreadsheet" asked by Ray Moran on 6-19-2013. That's right, way back from the year 2013 with 136 replies and over 10,000 views to date. 

Until the great people working on the Google Sheets team decide to make this a reality, here is some potentially helpful information:

This is possible with the use of Google Apps Script, at least in theory. I am in mid development of some projects and have started a solution for this but I currently do not have code to give directly to you here because my solution is specific to my project.

The way i am doing this through code is to... set a pixel size for the maximum page size, that is the maximum number of pixels that can print from top to bottom of the page in your case (I just learn this size manually through trial and error). Then with a script I walk row by row down my sheet and as it goes it decides if the current row should be on a new page (in my case this is complex because I am grouping data and sub-grouping data and attempting to keep groups together on a single page).

Each time the script steps down a row it measures the row's size in pixels and adds it to the counter. anytime it decides to force content to a new page it uses this counter to automatically inflate the rest of the current page with blank rows (which is the current best manual workaround, I am just getting a script to automate it for me).

To measure the pixel size of a row look up "getRowHeight" using "google apps script". Then you can start to do stuff like the following, but keep in mind these are all my custom variable names except getRowHeight().

currPageSize += destSheet.getRowHeight(destCursor)

This is the solution for me, but reading through most of the comments on the product forum it seems that instead of a predetermined algorithm to make paging choices, most people would prefer to insert a page break at will by user defined choice.

An easy way to set a page break at the cursor's position... would be to use Google Apps Script and add a custom menu. Have an item in that menu called "mark page break" or something like that. Then anytime a user runs that it can add a cell "note" with some set text such as "page break here" to the cell at the cursor's current position. A cell "note" shows up as a small black triangle in the top right corner of the cell so it would be easy to see where breaks will be.

Then the clunky part... is you would need to add a second item to that custom menu so that you can "inflate page breaks". While you can have the script automatically inflate the blank rows once you mark a page break at a certain cell, the problem is that future updates and changes within this sheet may cause that spacing to change. Therefore; running the second menu item "inflate page breaks" just before printing would cause the script to walk through the sheet line by line and auto-adjust this spacing for you.

So to any coders out there that were looking for a solution, I hope that helps. The biggest things to note being look up "getRowHeight" and that it's possible to programmatically edit and view cell notes (keep in mind it is NOT possible yet through Google Apps Script to see or touch cell "comments", comments and notes are not the same).

To all the non-coders out there... sorry this isn't an easier plug and play solution for you, but know that it is possible and maybe I or someone will hash it out and drop the code in here for you to use.

Update: June 24, 2016
I have slowly been working on some code to make page breaks possible. It still has a long way to go but you can hear about some of the progress here.