You have a sheet template where there is a drop down to make it a dynamic sheet. Now you want to print all the options to paper sheets for the drop down list. In another words, how to print a separate sheet for each drop down option?
It’s hard to explain, but let’s take an example. You have a student transcript template from which you can select the student name from a drop down. You will get the selected student’s scores filled. The goal is to print a transcript for each student.
The problem is you can only select one option and print one sheet at one time with Google Sheets’s print feature. It’s time consuming when you have dozens of students or employees to print.
Solution: Power Print
We built a free lightweight spreadsheet add-on Power Print to help you print all the options of the drop down list. It’s not on the add-on store, so don’t rush into finding it.
How it works
In a nutshell, Power Print will expand all the options in a drop down list into separate sheets. To avoid corrupting your original spreadsheet, a copy will be saved.
Get started with a demo
If you’re running short of time, skim this 1-min Power Print demo.
Step 1. Copy the demo spreadsheet
First of all, copy the Print all option of dropdown list demo sheet. It contains 2 sheets, Student Grades and Transcript. The Student Grades sheet provides data to the Transcript sheet. And the Transcript sheet is the template you want to expand and print for all.
Step 2. Expand all options in the drop down list
Now you have a writable copy of the demo. Open it.
- Go to Transcript sheet.
- Select the Name drop down in C10.
- Click menu Add-ons > Power Print > Expand dropdown.
- In the popped confirmation dialog, double check you selected the correct drop down and click Yes.
- It will may take seconds to minutes to expand all options depending on your template sheet size and the number of options.
- You will then see this Export Successful dialog. Click the link to open the expanded spreadsheet.
- In the copy spreadsheet, you will notice all the options are printed into separated sheets. Each sheet has different data (a unique transcript) now.
Step 3. Power Print!
- Now click File > Print to bring up the default print dialog.
- Configure how the sheets are printed.
- In Print, select Workbook.
- In Selection, uncheck sheets you don’t want to print, especially for the original data sheet Student Grades and Transcript.
- Click Apply.
- Adjust other layout parameters.
- Click Next to move toward the printer page.
You get all drop down options printed in one run! Check your watch for how much time Power Print has saved for you!
Now it’s your turn
Once you know how Power Print works, it’s easy to replicate it on your case.
- Open Script editor by click menu Tools > Script editor.
- Copy all the content of bundle.gs.
- Now paste the content to your sheet’s script editor. Once you’re done, you will see the same Power Print menu as the demo. Not sure what the hell I’m talking about? Please follow this How To Add An Apps Script To Your Google Docs? article.
- Your show is up. Have fun!
In the article, we show you how to use an add-on Power Print to print all options of a drop down list in Google Sheets. If you have questions or problems, please contact us or leave a comment.
Thanks for reading.