What is Sections to Sheets?
Sections to Sheets is a free Google Sheets Add-on that separates rows by column sections and puts them in individual pages or sheets in a spreadsheet in preparation for export and printing.
You can find Sections to Sheets at the Google Workspace Marketplace.
Table of Contents
Example 1
Let’s say you want to print out a list of expenditures by department and you want each department to start on a new page. You’ll also want to keep the same header for each page.
Example 2
You might want to export a list of grades by class number. You sort the grades by class and then export the list with each class starting on a new page with a header and footer.
Sections to Sheets can help you achieve this quickly by creating a new Google Spreadsheet and separating each selection and putting it into a new Sheet(tab) with or without headers and footer. This will enable you to quickly export or print your sheets.
Spreadsheet programs generally don’t do a good job of automatically separating data by a column and printing or exporting it out. Usually, you are left with setting a header and footer and then dragging your cursor over each set of data you want to have on a separate page.
This is fine if you don’t have much data, but what happens if say, you want to separate your sales data for each city in your state and put that data on separate pages with a neat header and footer? Then it becomes a massive chore prone to error.
But not with Sections to Sheet.
What can Sections to Sheets do?
Sections to Sheets Can:
- Sets a column for you to separate your data by.
- Separates your data by column sections.
- Prepares your data in separate sheets(tabs) for printing or export.
- Automatically detect the width of your columns.
- Allow you to reset the width of your columns.
- Set a header and/or footer or none at all.
- Retains the original formatting of the original data.
- Retains the column width dimensions of your original data.
What can’t Sections to Sheets do?
Google does not allow its add-on programs to access printing on your computer. This is to ensure security on your computer. What Sections to Sheets will do is set up a spreadsheet ready for you to export or print.
Sections to Sheets also does not directly sort the column you want to separate by. This is a simple step that you, the user, will already have done before running the add-on.
Setting custom row heights can be a little slow. This is due to the way the Google Sheets API takes row data. While I have made improvements for this over the years, it is still going to take a little time to get your rows the right height if you select this option for a larger project.
How to use Sections to Sheets
Step 1
Sort your sheet by the column you want to separate by.
(Note you will need to select the advanced range sorting options from the sort range menu)
Step 2
To open, go to Extensions> Sections to Sheets and click Sections to Sheets.
(note Extensions was previously Add-ons).
Step 3
A sidebar will appear for you to make your selections.
Step 4
Select the column you have sorted. The one you wish to use to separate your data by.
Step 5
Select your headers and or footers if you have them.
Note: You will need to put your header at the top of your data and your footer at the bottom.
Step 6
Select the width of the columns you want to be displayed. Sections to Sheets automatically calculates the width of your data range in columns and will set it to standard. Sometimes you may wish to only include a selection of columns. You can do this by changing the width, or the starting column and the ending column.
Step 7
When you are happy with your selection, click “Submit”.
After you have clicked submit, Sections to Sheets will:
- create a new file in the same directory as your current spreadsheet. The new file will be named the same as your current file with “by selection” at the end of it.
- create sheet tabs in that file titled by the column identifier.
- add the data for each column identifier and add a header and footer if necessary.
Finally, if your sections have been separated successfully, you will be navigated to a new page in the sidebar where you will get a link to your newly created Google Sheet separated into sheet tabs by section.
Exporting or printing your data
By now all your data will be prepared so that each section sheet will start printing or will be displayed on a separate page.
EXPORT AS PDF:
Step 8
To export as a PDF go to File> Download as > PFD document (.pdf)
Step 9
Ensure you have set up the layout you want. Then under the “Export” label, click, “Workbook” to select all sheet tabs. and then click “EXPORT”.
Done. You have a neatly separated PDF document by sections.
Step 8
To print click the print icon on your quick access bar or (ctrl+p).
Step 9
After you have set up the layout you want. Select “Workbook” from the “Print” drop-down to select all the sheet tabs. Then select “NEXT”. The Print screen will appear and you can then go ahead and select “Print”.
Scopes and What You Are Authorising
Understanding what authorisation you are giving to an add-on is important for your own and your organisation’s security. Below is the list of scopes that I have used for Sections to Sheets. If your organisation’s admin would like further details before using the script feel free to reach out to me on my Contact Us page.
- See, edit, create, and delete only the specific Google Drive files you use with this app (https://www.googleapis.com/auth/drive.file): This required for the app to create a new Google Sheets file with your data separated into sheets. This level of authorisation cannot access any other Google Drive file or folder that the app did not create.
- See and download all your Google Drive files (https://www.googleapis.com/auth/drive.readonly): This is required for the app to get the parent folder ID for the source sheet so that the newly created sheet can be added to the same folder. No file information is stored.
- See, edit, create, and delete all your Google Sheets spreadsheets (https://www.googleapis.com/auth/spreadsheets): This is used to view the source spreadsheet and edit the newly created spreadsheet.
- Display and run third-party web content in prompts and sidebars inside Google applications (https://www.googleapis.com/auth/script.container.ui): This is used to create a sidebar inside the Google Sheet.
- See your primary Google Account email address (https://www.googleapis.com/auth/userinfo.email): This is a requirement for all Google Workspace Marketplace Add-ons. However, the app itself does not use your email.
- See your personal info, including any personal info you’ve made publicly available (https://www.googleapis.com/auth/userinfo.profile) Another requirement for all Google Workspace Marketplace Add-ons. However, the app itself does not use this tool.
The Legal
Here you can find the Privacy Policy and Terms and Conditions.
Online privacy is incredibly important to me. So much so that I have a donate button to the Electronic Frontier Foundation (EFF) at the bottom of my sidebar.
Sections to Sheets does not record your personal information or data.
Changelog
2023-12-12:
- Reduced the authorisation scopes for Google Drive access from
https://www.googleapis.com/auth/drive(See, edit, create, and delete all of your Google Drive files) tohttps://www.googleapis.com/auth/drive.file(See, edit, create, and delete only the specific Google Drive files you use with this app) andhttps://www.googleapis.com/auth/drive.readonly(See and download all your Google Drive files) This means that the add-on can only edit the Google Sheets file that it creates when it splits the sections into sheet tabs. The read-only for all files is required because there is no concise scope to examine the active sheet’s metadata to obtain the parent folders. - Improved performance when the user selects keep Row Widths. Now the add-on batches by groups of rows of mutual row height.
- Added a spinner overlay when:
- Initially loading the add-on into the sidebar.
- Selecting header and footer ranges.
- Submitting the request to build the new Google Sheet by section.
- Added an instructional video for the Google Workspace Marketplace Page.
- Added a Support Links dialogue.
2020-10-07:
- Added the option to include row height. Note. This may significantly increase processing speed.
It is only making a copy of the sheet and not separating it into multiple tabs.
Hi Clint,
Could you provide me with a little detail on your issue? I have checked my logs and don’t see any errors. What column reference are you using? Do you have a header?
Cheers,
Everything seems normal, and then I submit, and wait and wait, and it is never “Done”
Greetings overlord Yagi
Is there any way to get the tabs with alternating odd/even colors??
Hi Dav,
I’m not 100% on what you are looking for, sorry. But…
Probably the easiest approach would be to run two separate Sections to Sheets one with an even group and one separating them before running the add-on. You could do this with a filter function. Say you have a section column like 101-001, 101-002 etc in col 2. You do something like this
=FILTER(A2:D,ISODD(VALUE(RIGHT(C2:C))) = TRUE). Where A2:D is the range you want to copy into separate tabs.If you really must get just the alternating tabs from your completed Section to Sheet Google Sheet, you would have to do this with Google Apps Script by getting a list of the sheets with something like:
SpreadsheetApp.getActiveSpreadsheet().getSheets()(One example of using getSheets can be found here) and then iterate through each one finding either the odd or even groups and hiding the alternate tabs. You could then print workbook and only those tabs not hidden would print.Let me know if this is what you are after or you were looking for something else.
Cheers,
~Yagi