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

workspace

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.

  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.
    Copy and paste power print apps script to your script editor
  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 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 suggestions or issues, please leave a comment below.

Thanks for reading.

Share on LinkedIn Share on Reddit Share on Pinterest