Coligo

/**
 * @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:support@xfanatical.com" target="_blank">support@xfanatical.com</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()
  //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')
    }
  }
}

Feature Tip:

Make sure you are using Google sheet file not Excel sheet file".Xlsx".