How to visualize a dependency graph (product roadmap) in Google Sheets

user_745121 0
Viewed 1127

I have a table like this in Google Sheets:

+------------------------------+---------------------------------------------------+
|            Items             |                    depends on                     |
+------------------------------+---------------------------------------------------+
| Get 2 slices of bread        |                                                   |
| Put peanut butter on 1 slice | Get 2 slices of bread                             |
| Put jelly on 1 slice         | Get 2 slices of bread                             |
| Put away peanut butter jar   | Put peanut butter on 1 slice                      |
| Put slices together          | Put peanut butter on 1 slice,Put jelly on 1 slice |
| Put away jelly jar           | Put jelly on 1 slice                              |
+------------------------------+---------------------------------------------------+

and want to display a dependency graph synced with it in the same sheet, how do I do that?

google-sheets
2 Answers

Disclaimer: I made the add-on.

Since Google Sheets do not support Dependency graph visualization. There is now an add-on for it.

enter image description here

Note: on the technical side, a URL is generated by the add-on and passed to Image-Charts to render the graph as SVG, then the IMAGE(url) Google Sheet function is used to render it directly inside a cell.

user_7451210

If you rather not install an add-on (because of the permissions you need to grant it) and the data in your spreadsheet doesn't change very often. You can use this solution that doesn't require an add-on. After a one-time setup (column C), you can recreate the graph manually in under a minute.

  1. Next to column A and B, use these formula's in column C:
="  """&A2&""" -> """&B2&""""
="  """&A3&""" -> """&B3&""""
etc
  1. Copy column C into the digraph G { ... } block on webgraphviz.com
  2. Click Generate Graph!
  3. Screen capture the graph part back to the spreadsheet

And you're done. Here's an example spreadsheet with the formula's:

result in webgraphviz.com

Limitation

This solution doesn't work when an item depends on multiple items using the comma notation. Need to fix that... I work around for now is to copy the offending line into multiple lines each with one of the dependency. I created a workaround sheet in the spreadsheet to demonstrate that. Than you get this:

digraph G {
  "Get 2 slices of bread" -> ""
  "Put peanut butter on 1 slice" -> "Get 2 slices of bread"
  "Put jelly on 1 slice" -> "Get 2 slices of bread"
  "Put away peanut butter jar" -> "Put peanut butter on 1 slice"
  "Put slices together" -> "Put peanut butter on 1 slice" /*first one*/
  "Put slices together" -> "Put jelly on 1 slice" /*second one*/
  "Put away jelly jar" -> "Put jelly on 1 slice"
}
user_13854290
Related