Gantt Chart on Dependencies with Recursion in Google Sheets. Part 1

Max Makhrov
4 min readJun 21, 2024

--

Our goal is a GANTT chart in Sheets based on dependencies.

Here is a sample for pizza making:

We know how much it takes for each operation:

| Action                          | Duration, min. |
|---------------------------------|----------------|
| Prepare Pizza dough | 15 |
| Wait for dough to 2x in size | 90 |
| Preheat the oven | 30 |
| Assemble the ingredients | 10 |
| Allow dough to rest | 7 |
| Create an 10-inch dough circle | 10 |
| Put ingredients on the pizza | 10 |
| Cook | 8 |

And we have dependencies, what comes before what and what comes next:

| Action before                   | Action after                  |
|---------------------------------|-------------------------------|
| Prepare Pizza dough | Wait for dough to 2x in size |
| Wait for dough to 2x in size | Preheat the oven |
| Wait for dough to 2x in size | Assemble the ingredients |
| Wait for dough to 2x in size | Allow dough to rest |
| Allow dough to rest | Create an 10-inch dough circle|
| Create an 10-inch dough circle | Put ingredients on the pizza |
| Put ingredients on the pizza | Cook |
| Preheat the oven | Cook |

The question is:

When will it take to make a pizza?

Critical Chain

A Chain is:

A set of tasks you need to accomplish where you can’t start the next task until you finish the dependent one.

A simple analogy:

We are meant to make pizza tonight.

We need to understand the chain's time.

A “chain” here is:

  1. Prepare Pizza dough
  2. Wait for dough to 2x in size
  3. Preheat the oven
  4. Cook

Now let’s add another question:

How do we get all chains and calculate their total time?

Recursion

Recursion is one of the nerdiest concepts in computer science.

  1. A task or function calls itself. And calls itself again. And again. Forever.
  2. Recursion stops when it reaches some condition specified by the writer of the function.

> Why do nerds like recursive tasks?

>> Because they can do the same complex calculation a NORMAL task would… but much faster, with much less logic. PLUS it’s cool. 😎

Recursion is the only way (I see) to cook our pizza.

Recursion + Google Sheets

🚨 REALLY SPECIAL THANKS goes to the following functions:

- LAMBDA and
- LET

These Google sheet functions allow us define and use RECURSIVE functions 🔄

Take this λ function:

=λ(n, FACT, IF(n=0, 1, n* FACT(n-1, FACT)))

If you’re willing to set it up in your sheet, you can use the following:

=LET(FACT, λ(n, FACT, IF(n=0, 1, n * FACT(n-1, FACT))), FACT(5, FACT)) 🚀

Explanation:

  • λ(n, FACT, ...): This defines a λ function that takes n (the number for which we want to calculate the factorial) and FACT (a reference to the λ function itself for recursion.)
  • IF(n=0, 1, n * FACT(n-1, FACT)): This checks if n is 0. If it is, it returns 1 (base case). Otherwise, it multiplies n by the factorial of n-1.
  • LET(FACT, λ(...), FACT(5, FACT)): This part defines the FACT function using LET and then calls FACT with 5 as the initial value.

The formula for getting all chains and their time

The formula is…

— — LONG DRUM ROLLS — —

The formula is…

No fear. Let’s spend the night with this formula explanation.

Drum rolls…

♪└|∵|┐♪└|∵|┐♪└|∵|┐

=LET(_durations,B4:C11, _dependencies,E4:F11,c_0,

"Toolset ↓",
mapVlookup_,LAMBDA(dataToFind, keyValuePairs, INDEX(VLOOKUP(dataToFind, keyValuePairs, 2, ))),
i_,LAMBDA(element, indx, INDEX(element,indx)),
last_,LAMBDA(element, INDEX(element, ROWS(FLATTEN(element)))),
col_,LAMBDA(array, columnIndex, INDEX(array,,columnIndex)),
find_,LAMBDA(array, value, columnIndex, IFNA(FILTER({array, SEQUENCE(ROWS(array))},
col_(array, columnIndex) = value))), c_1,

"Critical chain with recursion ↓",
chain_,LAMBDA(chain, dependencies, timesMap, chainTimes, self_,
LET(
firstElement, i_(chain, 1),
foundDependencies, find_(dependencies, firstElement, 2),
if (foundDependencies = "", if(1=0,
"Base case",
{ JOIN(","&char(10), chain),
JOIN(",", chainTimes),
SUM(chainTimes)}),if(1=0,
"Recursion in a loop, where `self_` calls chain_ from itself",
BYROW(foundDependencies, LAMBDA(foundLine,
self_(
FLATTEN(i_(foundLine, 1), chain),
dependencies,
timesMap,
FLATTEN( i_(timesMap, last_(foundLine)), chainTimes), self_)))) ) )),c_3,

"Calculating logics. Mapping time and launching chain_ in a MAP ↓",
timesMap,mapVlookup_(_dependencies, _durations),
MAP(SEQUENCE(ROWS(_dependencies)),LAMBDA(r,
chain_(
i_(_dependencies, r),
_dependencies,
timesMap,
i_(timesMap, r),
chain_)) ) )

♪┌|∵|┘♪┌|∵|┘♪┌|∵|┘

This formula gives us 3 important columns for the next calculations:

  1. Chain with all steps listed
  2. Each step (one action) length from this chain
  3. Total time needed to accomplish all steps in a chain

By letting Google Sheets do its black magic, we can understand:

🍕Each chain tasks

  • How long does each one go through different steps
  • How long the whole process takes → the longest chain!

Now — maybe you have a critical business decision to make. Like

When can you send the email newsletter for the 🏖️SUMMER mega-sale offer? (hint: after you eat the pizza)

Conclusion

Welcome to“Pizza-making” ultimate quiz! This was part one. Stay tuned!

--

--

Max Makhrov
Max Makhrov

Written by Max Makhrov

Google Sheets Developer, master of Online Accounting

No responses yet