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

You have a sheet template with a drop-down menu to make it dynamic. For example, selecting a student's name from the drop-down automatically fills in that student's transcript with their scores. Now, your goal is to print a separate transcript for each student based on every available drop-down option. In other words, you want to print a separate sheet for each drop-down option not just the currently selected one. This means looping through all the student names in the drop-down list, auto-updating the template and printing each filled version on paper.

If you’re wondering how to extract or display all the options from a drop-down list, you might also be searching for how to print drop down list in Excel or Google Sheets. Get the list of choices and perform actions like printing for each item.

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.

1. Go to Transcript sheet.
2. Select the Name drop down in C10.
3. Click menu Add-ons > Power Print > Expand dropdown.
4. 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!

1. Now click File > Print to bring up the default print dialog.
2. Configure how the sheets are printed.
   1. In Print, select Workbook.
   2. In Selection, uncheck sheets you don't want to print, especially for the original data sheet Student Grades and Transcript.
   3. Click Apply.
   4. Adjust other layout parameters.
3. 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!

Limitation

The script does not support in-cell images. Otherwise, you would see a message "Exception: Service error: Spreadsheets". As a workaround, use the IMAGE formula to load the image into the cell.

Source code

/**
 * @license MIT
 *
 * © 2020 xfanatical.com. All Rights Reserved.
 *
 * @since 1.0.4 fix an issue of numeric value
 * @since 1.0.3 fix a bug of #N/A items
 * @since 1.0.2 fix a bug of nested formula
 * @since 1.0.1 fix a bug of values with spaces
 * @since 1.0.0 print all dropdown options from a validation list
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem('Expand dropdown', 'expandDropdown')
    .addSeparator()
    .addItem('Help', 'help')
    .addToUi()
}

function help() {
  var htmlOutput = HtmlService
    .createHtmlOutput('<div style="font-family: Arial, sans-serif;">'
      + '<h3>Quick Start</h3>'
      + '<ul>'
      + '  <li>Select the dropdown cell you\'d like to expand and print</li>'
      + '  <li>Click the menu <b>Add-ons > Power Print > Expand dropdown</b></li>'
      + '  <li>Power Print will export a spreadsheet copy with all dropdown options expanded into separated sheets</li>'
      + '  <li>You may print the spreadsheet copy using Workbook</li>'
      + '</ul>'
      + '<h3>Contact us</h3>'
      + '<p>Please contact <a href="mailto:[email protected]" target="_blank">[email protected]</a> if you have problems with this addon.</p>'
      + '</div>')
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Power Print Help')
}

function createSnapshotSpreadsheet(original) {
  var currentSpreadsheetName = original.getName()
  var today = new Date()
  return original.copy(currentSpreadsheetName + '-snapshot-' + today.toLocaleDateString() + '_' + today.toLocaleTimeString())
}

function expandSheets(dropdownRange) {
  var dropdownSheetName = dropdownRange.getSheet().getName()

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var snapshotSpreadsheet = createSnapshotSpreadsheet(spreadsheet)
  var snapshotDropdownSheet = snapshotSpreadsheet.getSheetByName(dropdownSheetName)
  var snapshotDropdownRange = snapshotDropdownSheet.getRange(dropdownRange.getA1Notation())
  SpreadsheetApp.setActiveSpreadsheet(snapshotSpreadsheet)

  var rule = snapshotDropdownRange.getDataValidation()
  if (rule !== null) {
    var criteria = rule.getCriteriaType()
    var args = rule.getCriteriaValues()
    Logger.log('The data validation rule is ' + criteria + ', args = ' + args)

    var values
    if (criteria === SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) {
      Logger.log('value in range, args=' + JSON.stringify(args))
      values = args[0].getValues().map(function (val) { return val[0] })
      Logger.log('values=' + JSON.stringify(values))
    } else if (criteria === SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST) {
      Logger.log('value in list, args=' + JSON.stringify(args))
      // eslint-disable-next-line prefer-destructuring
      values = args[0]
    } else {
      SpreadsheetApp.getUi().alert('The range you selected is not a drop down list. Please try again.')
      return
    }
    for (var i = 0; i < values.length; i += 1) {
      Logger.log('i=' + i)
      var dropdownItemValue = values[i]
      if (typeof dropdownItemValue === 'number'
        || (dropdownItemValue.toString().trim() !== '' && dropdownItemValue.toString().trim() !== '#N/A')) {
        Logger.log('selecting dropdown value ' + dropdownItemValue)
        snapshotDropdownRange.setValue(dropdownItemValue)
        SpreadsheetApp.flush()

        Logger.log('copying ' + dropdownSheetName + ' [' + dropdownItemValue + '] to ' + snapshotSpreadsheet.getName())
        var copySheet = snapshotSpreadsheet.insertSheet(snapshotDropdownSheet.getName() + '-' + dropdownItemValue, {
          template: snapshotDropdownSheet,
        })
        var dataRange = copySheet.getDataRange()
        dataRange.setValues(dataRange.getValues())
      }
    }

    // Display a modal dialog box with custom HtmlService content.
    var htmlOutput = HtmlService
      .createHtmlOutput('<p>A spreadsheet copy including all ' + dropdownSheetName + 's is exported to <a href="' + snapshotSpreadsheet.getUrl() + '" target="_blank">' + snapshotSpreadsheet.getName() + '</a></p><p>You may print the workbook.</p>')
  .setWidth(300)
      .setHeight(150)
    SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
  } else {
    SpreadsheetApp.getUi().alert('The range you selected is not a dropdown list. Please try again.')
  }
}

function expandDropdown() {
  var spreadsheet = SpreadsheetApp.getActive()
  var activeRange = spreadsheet.getActiveRange()
  var dataValidation = activeRange.getDataValidation()
  var ui = SpreadsheetApp.getUi()

  if (activeRange === null) {
    ui.alert('Please select the dropdown list range and try again.')
  } else if (dataValidation === null
    || (dataValidation.getCriteriaType() !== SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE
      && dataValidation.getCriteriaType() !== SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST)) {
    ui.alert('The range you selected is not a dropdown list. Please try again.')
  } else {
    var response = ui.alert('Confirm', 'Do you want to print all options in dropdown list of \'' + activeRange.getSheet().getName() + '!' + activeRange.getA1Notation() + '\'?', ui.ButtonSet.YES_NO)
    if (response === ui.Button.YES) {
      expandSheets(activeRange)
    } else {
      Logger.log('user cancelled expansion')
    }
  }
}

Conclusion

In this article, we demonstrated how to use the Power Print add-on to effortlessly print all options from a drop-down list in Google Sheets. Whether you're managing data validation lists or creating dynamic reports, this solution simplifies the process. If you're exploring how to print a Google Spreadsheets drop down list or wondering how to print drop down list in Excel, this method offers a practical approach, especially for Google Workspace users.

Thanks for reading.