Monday, November 5, 2012

Unhide that Worksheet!

Or – Worksheet? What Worksheet? I don't see no stinking Worksheets.

Hidden Worksheets are one of Tableau's enduring mysteries. Once hidden, a Worksheet's only visible presence in Tableau is as a component of the Dashboard(s) in which it appears.

Their stealthy nature makes them difficult to work with. But there are ways to find them:

  • the simplest is to look through the Dashboards until they're spotted but this is tedious at best and since well-designed Dashboards tend to camouflage their individual Worksheets it's also very error-prone.
  • You can use an automated Workbook analysis approach, e.g.
    • The Tableau Companion (aka TWIS) is a full featured Workbook analysis and inventorying system. Its unique feature is the creation of diagrams—PDFs, GIFs, and SVGs, showing the Dashboards and Worksheets in your Workbooks:
      If TCC is a bit much, you could use...
    • Andy Cotgreave's TWB Auditor is a very nice, polished, easy to use way to get the information, or
    • This post presents a short (37 line) Ruby script that will pull the Workbook-Dashboard-Worksheet information from your Workbooks so that you can see what's where. And the code is right there so you can be sure of what it's doing, or even improve it if you want.

So that's where the Worksheets are. Now what?

Knowing where the Worksheets are is very nice, but it only addresses part of the problem. The hidden Worksheets are still hidden, and unhiding them means opening up their Workbooks with Tableau Desktop, and for each hidden Worksheet navigating to a Dashboard containing the hidden Worksheet, "Going to" the Workbook, and right-click 'unhiding' it.

Unless...

You download or copy/paste and run the following Ruby script in the directory containing the Workbooks you want the Worksheets unhid in. ('Unhid'–is that a real word?) 


# TCC_sW.rb - this Ruby script Copyright 2012, Christopher Gerrard

require 'nokogiri'
require 'open-uri'

$hwTwbs = 0
$recNum = 0

def init
    $f = File.open("TCC_HWs.csv",'w')
    $f.puts 'Record #,Workbook,Worksheet,class,auto-hidden,maximized' unless $f.nil?
    puts "\nHidden Windows found for Workbooks:\n-----------------------------------"
end

def showWindows twb
  doc = Nokogiri::XML(open(twb))
  h1 = handleHiddenNodes(twb, doc, '//workbook/windows/window[@class="hidden-worksheet"]' )
  h2 = handleHiddenNodes(twb, doc, '//workbook/windows/window[@auto-hidden="1"]'          )
  if h1 || h2
    writeTWB(doc, twb)
  end
end

def handleHiddenNodes(twb, doc, xPathStr)
  nodes = doc.xpath(xPathStr)
  if nodes.length > 0 then
     unhideWindows(nodes, twb)
     hidden = true
  else
     hidden = false
  end

end

def unhideWindows(wNodes, twb)
  puts twb
  wNodes.each do |n|
    puts n
    wName  = n.xpath('./@name')
    wClass = n.xpath('./@class')
    autoH  = n.xpath('./@auto-hidden')
    max    = n.xpath('./@maximized')
    $f.puts "#{$recNum += 1},#{twb},#{wName},#{wClass},#{autoH},#{max}" unless $f.nil?
    n.set_attribute('class','worksheet')
    n.set_attribute('auto-hidden','0')
    n.set_attribute('maximized','0')
  end
end

def writeTWB(doc, twb)
  newTwb = File.open("#{twb.sub(/.twb$/,'')}.unhidden.twb",'w')
  newTwb.puts(doc)
  newTwb.close

end

init

Dir.glob("*.twb") {|twb| showWindows twb }

$f.close unless $f.nil?

What TCC_sW.rb does.

Each of the Workbooks in the current directory is opened up and examined for the presence of hidden Worksheets. If any are found they are unhidden and a copy of the Workbook is made with the unhidden Worksheets.

No Workbooks are harmed during the unhiding of the Worksheets.

The name of the new copy Workbook is the same as the name of the original Workbook with the trailing ".twb" replaced with ".unhidden.twb"

For example, the Workbook "FinancialAnalyses.twb" is copied, Worksheets unhidden, to ""FinancialAnalyses.unhidden.twb""

How to use TCC_sW.rb

  • Prerequisites
    • Minimal technical skills.
    • Have Ruby installed and ready to run.
    • Have the Nokogiri Ruby gem installed—it's used in the XML parsing.
    • Have TCC_sW.rb in place—it doesn't matter where, or what name you use, as long as you know where it is.
      You can copy the code above and paste it into your favourite text editor.
  • Running TCC_sW.rb
    • Open a command prompt.
      (you can run it otherwise, but this is simple and straightforward)
    • CD to the directory containing the Workbooks you're interested in.
    • Run it: "[path to]\ruby    [path to]\TCC_sW.rb"
  • Presto. You now have a set of your Workbooks with all of their Worksheets unhidden.

The usual caveats.

TCC_sW.rb works fine with the limited testing it's been through, but it's definitely not bulletproof and hardened. It's only been run against Tableau v7 Workbooks, and it's entirely possible that hidden Worksheets in other Tableau version Workbooks, or other types of Worksheet-hiding conditions might not be detected.

I hope it works for you, but make no guarantees. If you do use it and make improvements I hope that you'll post them back here as comments so I can learn from them, and hopefully other people can benefit from them too.

No Workbooks are harmed during the unhiding of the Worksheets.

The name of the new copy Workbook is the same as the name of the original Workbook with the trailing ".twb" replaced with ".unhidden.twb"

For example, the Workbook "FinancialAnalyses.twb" is copied, Worksheets unhidden, to ""FinancialAnalyses.unhidden.twb""

How to use TCC_sW.rb

  • Prerequisites
    • Minimal technical skills.
    • Have Ruby installed and ready to run.
    • Have the Nokogiri Ruby gem installed—it's used in the XML parsing.
    • Have TCC_sW.rb in place—it doesn't matter where, or what name you use, as long as you know where it is.
      You can copy the code above and paste it into your favourite text editor.
  • Running TCC_sW.rb
    • Open a command prompt.
      (you can run it otherwise, but this is simple and straightforward)
    • CD to the directory containing the Workbooks you're interested in.
    • Run it: "[path to]\ruby    [path to]\TCC_sW.rb"
  • Presto. You now have a set of your Workbooks with all of their Worksheets unhidden.

The usual caveats.

TCC_sW.rb works fine with the limited testing it's been through, but it's definitely not bulletproof and hardened. It's only been run against Tableau v7 Workbooks, and it's entirely possible that hidden Worksheets in other Tableau version Workbooks, or other types of Worksheet-hiding conditions might not be detected.

I hope it works for you, but make no guarantees. If you do use it and make improvements I hope that you'll post them back here as comments so I can learn from them, and hopefully other people can benefit from them too.

No Workbooks are harmed during the unhiding of the Worksheets.

The name of the new copy Workbook is the same as the name of the original Workbook with the trailing ".twb" replaced with ".unhidden.twb"

For example, the Workbook "FinancialAnalyses.twb" is copied, Worksheets unhidden, to ""FinancialAnalyses.unhidden.twb""

How to use TCC_sW.rb

  • Prerequisites
    • Minimal technical skills.
    • Have Ruby installed and ready to run.
    • Have the Nokogiri Ruby gem installed—it's used in the XML parsing.
    • Have TCC_sW.rb in place—it doesn't matter where, or what name you use, as long as you know where it is.
      You can copy the code above and paste it into your favourite text editor.
  • Running TCC_sW.rb
    • Open a command prompt.
      (you can run it otherwise, but this is simple and straightforward)
    • CD to the directory containing the Workbooks you're interested in.
    • Run it: "[path to]\ruby    [path to]\TCC_sW.rb"
  • Presto. You now have a set of your Workbooks with all of their Worksheets unhidden.

The usual caveats.

TCC_sW.rb works fine with the limited testing it's been through, but it's definitely not bulletproof and hardened. It's only been run against Tableau v7 Workbooks, and it's entirely possible that hidden Worksheets in other Tableau version Workbooks, or other types of Worksheet-hiding conditions might not be detected.

I hope it works for you, but make no guarantees. If you do use it and make improvements I hope that you'll post them back here as comments so I can learn from them, and hopefully other people can benefit from them too.

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)