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.

Student transcript template with a drop down list to select student names
Student transcript template with a drop down list to select student names

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.

Power Print Quick Demo
Power Print Quick 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.

Student grades sheet provides data for the transcript template sheet
Student grades sheet provides data for the transcript template sheet
The transcript template sheet with the drop down list is the one to expand and print
The transcript template sheet with the drop down list is the one to expand and print

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.
Expand all options in a drop down
Expand all options in a drop down
Select yes to print all options in the specific drop down
Select yes to print all options in the specific drop down
  • 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.
Exported successfully a copy of expanded sheet
Successfully exported a copy of expanded sheet
  • In the copy spreadsheet, you will notice all the options are printed into separated sheets. Each sheet has different data (a unique transcript) now.
All drop down options are expanded into separate sheets
All drop down options are expanded into separate sheets

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.
Print the entire workbook except sheets you don't need
Print the entire workbook except sheets you don’t need

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.
Open Script editor in the demo sheet
Open Script editor in the demo sheet
Copy all the content of bundle.gs
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!

Conclusion

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.