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

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:

Prepare Pizza dough
Wait for dough to 2x in size
Preheat the oven
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.

A task or function calls itself. And calls itself again. And again. Forever.
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_)) ) )