How to Print All Options in a Drop-down List in Google Sheets?

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. Or if you want to head straight over the script, go to the source code.

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.

1. Open your spreadsheet.
2. Open Script editor by click menu Tools > Script editor.
3. Copy all the content of apps script and paste in the script editor. Save it and give the project a name. Not sure what the hell I’m talking about? Please follow this How To Add An Apps Script To Your Google Docs? article.
   
4. Refresh your spreadsheet.
5. you will see the same Power Print menu as the demo.
6. Your show is up. Have fun!

The source code

This is a compiled apps script, so it will fry your brain to understand it.

function onOpen() {
}
function help() {
}
function expandDropdown() {
}/*!
 * @license
 * The source code is subject to the terms of service in
 * https://xfanatical.com/terms-of-service/
 * 
 * © 2019 xfanatical.com. All Rights Reserved
 */
!function(e, t) {
    for (var a in t) e[a] = t[a];
}(this, function(a) {
    var n = {};
    function o(e) {
        if (n[e]) return n[e].exports;
        var t = n[e] = {
            i: e,
            l: !1,
            exports: {}
        };
        return a[e].call(t.exports, t, t.exports, o), t.l = !0, t.exports;
    }
    return o.m = a, o.c = n, o.d = function(e, t, a) {
        o.o(e, t) || Object.defineProperty(e, t, {
            enumerable: !0,
            get: a
        });
    }, o.r = function(e) {
        "undefined" != typeof Symbol && Symbol.toStringTag && Object.defineProperty(e, Symbol.toStringTag, {
            value: "Module"
        }), Object.defineProperty(e, "__esModule", {
            value: !0
        });
    }, o.t = function(t, e) {
        if (1 & e && (t = o(t)), 8 & e) return t;
        if (4 & e && "object" == typeof t && t && t.__esModule) return t;
        var a = Object.create(null);
        if (o.r(a), Object.defineProperty(a, "default", {
            enumerable: !0,
            value: t
        }), 2 & e && "string" != typeof t) for (var n in t) o.d(a, n, function(e) {
            return t[e];
        }.bind(null, n));
        return a;
    }, o.n = function(e) {
        var t = e && e.__esModule ? function() {
            return e["default"];
        } : function() {
            return e;
        };
        return o.d(t, "a", t), t;
    }, o.o = function(e, t) {
        return Object.prototype.hasOwnProperty.call(e, t);
    }, o.p = "", o(o.s = 3);
}([ function(e, t, a) {
    "use strict";
    function n(e) {
        var t = e.getSheet().getName(), a = function(e) {
            var t = e.getName(), a = new Date();
            return e.copy("".concat(t, "-snapshot-").concat(a.toLocaleDateString(), "_").concat(a.toLocaleTimeString()));
        }(SpreadsheetApp.getActiveSpreadsheet()), n = a.getSheetByName(t), o = n.getRange(e.getA1Notation());
        SpreadsheetApp.setActiveSpreadsheet(a);
        var r = o.getDataValidation();
        if (null !== r) {
            var i, l = r.getCriteriaType(), p = r.getCriteriaValues();
            if (Logger.log("The data validation rule is ".concat(l, ", args = ").concat(p)), 
            l === SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) Logger.log("value in range, args=".concat(JSON.stringify(p))), 
            i = p[0].getValues().map(function(e) {
                return e[0];
            }); else {
                if (l !== SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST) return void SpreadsheetApp.getUi().alert("The range you selected is not a drop down list. Please try again.");
                Logger.log("value in list, args=".concat(JSON.stringify(p))), i = p[0];
            }
            for (var c = 0; c < i.length; c += 1) {
                var s = i[c];
                "" !== s && (Logger.log("selecting dropdown value ".concat(s)), o.setValue(s), SpreadsheetApp.flush(), 
                Logger.log("copying ".concat(t, " [").concat(s, "] to ").concat(a.getName())), a.insertSheet("".concat(n.getName(), "-").concat(s), {
                    template: n
                }));
            }
            var d = HtmlService.createHtmlOutput("<p>A spreadsheet copy including all ".concat(t, 's is exported to <a href="').concat(a.getUrl(), '" target="_blank">').concat(a.getName(), "</a></p><p>You may print the workbook.</p>")).setWidth(300).setHeight(150);
            SpreadsheetApp.getUi().showModalDialog(d, "Export Successful");
        } else SpreadsheetApp.getUi().alert("The range you selected is not a dropdown list. Please try again.");
    }
    function o() {
        var e = SpreadsheetApp.getActive().getActiveRange(), t = e.getDataValidation(), a = SpreadsheetApp.getUi();
        if (null === e) a.alert("Please select the dropdown list range and try again."); else if (null === t || t.getCriteriaType() !== SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE && t.getCriteriaType() !== SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST) a.alert("The range you selected is not a dropdown list. Please try again."); else {
            a.alert("Confirm", "Do you want to print all options in dropdown list of '".concat(e.getSheet().getName(), "'!").concat(e.getA1Notation(), "?"), a.ButtonSet.YES_NO) === a.Button.YES ? n(e) : Logger.log("user cancelled expansion");
        }
    }
    a.d(t, "a", function() {
        return o;
    });
}, function(e, t, a) {
    "use strict";
    (function(global) {
        var e = a(0);
        global.onOpen = function() {
            SpreadsheetApp.getUi().createAddonMenu().addItem("Expand dropdown", "expandDropdown").addSeparator().addItem("Help", "help").addToUi();
        }, global.help = function() {
            var e = HtmlService.createHtmlOutput('\n    <div style="font-family: Arial, sans-serif;">\n      <h3>Quick Start</h3>\n      <ul>\n        <li>Select the dropdown cell you\'d like to expand and print</li>\n        <li>Click the menu <b>Add-ons > Power Print > Expand dropdown</b></li>\n        <li>Power Print will export a spreadsheet copy with all dropdown options expanded into separated sheets</li>\n        <li>You may print the spreadsheet copy using Workbook</li>\n      </ul>\n      <h3>Contact us</h3>\n      <p>Please contact <a href="mailto:support@xfanatical.com" target="_blank">support@xfanatical.com</a> if you have problems with this addon.</p>\n    </div>\n    ');
            SpreadsheetApp.getUi().showModalDialog(e, "Power Print Help");
        }, global.expandDropdown = function() {
            Object(e["a"])();
        };
    }).call(this, a(2));
}, function(e, t) {
    var a;
    a = function() {
        return this;
    }();
    try {
        a = a || new Function("return this")();
    } catch (e) {
        "object" == typeof window && (a = window);
    }
    e.exports = a;
}, function(e, t, a) {
    "use strict";
    a.r(t);
    a(1);
} ]));

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.