How to efficiently extract Individual Plannings given a list of Tasks and Owners in google sheets?

Question

Based on the following table of tasks:

PROJECT     TASK    START       END         IN CHARGE
P1          Task 1  16/03/2021  19/03/2021  AAA
P1          Task 2  16/03/2021  19/03/2021  BBB
P1          Task 3  31/03/2021  31/03/2021  AAA
P2          Task 4  06/04/2021  07/04/2021  
P2          Task 5  17/03/2021  07/04/2021  BBB
P2          Task 6  20/04/2021  15/04/2021  
P3          Task 7  06/04/2021  15/04/2021  CCC

I am trying to build the following plannings :

IN CHARGE    16/03    17/03   18/03   19/03 
  AAA         P1      P1      P1      P1            
  BBB         P1      P1/P2   P1/P2   P1/P2  
  CCC                           

Currently, I am doing this using the following formula, but I need to put it in each cell for it to work, which gets very slow when the number of distinct values of persons in charge is large.

=ARRAYFORMULA(IFERROR(
  SI($A3<>"";
    JOIN("/"; UNIQUE(

      FILTER(INPUT!$A:$A;
             INPUT!$C:$C<=B$2; 
             INPUT!$D:$D>=B$2;
             INPUT!$E:$E=$A3)
    ))
  ;"")
;""))

Is there an efficient way to compute this ?

I got a generic example on this link :

GSheet GANTT

Answer 1

I'm not sure whether you can do it efficiently using only formulas, but you can use Google App Script to implement your function optimally in javascript.

You can initially collect the set of owners and the list of all days, then construct the resulting table by iterating over the tasks.

Here is an example implementation:

https://docs.google.com/spreadsheets/d/1zk01y8wwLvjJPwc3ov8f2NDUMIEcxPWMNhciFXvCaVw/edit?usp=sharing

And the javascript code:

class Task{
  /**
   * @param {[string, string, Date, Date][]} tasks
   */
  constructor(cells) {
    this.project = cells[0];
    this.owner = cells[1];
    this.start = cells[2];
    this.end = cells[3];
  }
}
/**
 * @param {[string, string, Date, Date][]} tasks
 */
function makeTimeSheet(task_cells) {
  task_cells = remove_empty(task_cells);
  const tasks = task_cells.map(cells => new Task(cells));
  console.log(`Running makeTimeSheet on the following ${tasks.length} tasks`);
  const start = new Date(Math.min(...tasks.map(t => t.start)));
  const end = new Date(Math.max(...tasks.map(t => t.end)));
  const header = ["", ...days_in_task({start, end})];
  const results = owners_dates(tasks);
  const table = final_table(results, start);
  table.unshift(header);
  console.log(`Returning a table with ${table.length} lines and ${table[0].length} columns`);
  return table;
}

/**
 * In an array of arrays, remove all the empty inner arrays.
 * This is required to work on infinite google sheet ranges
 */
function remove_empty(arr){
  return arr.filter(arr => arr.some(x => x));
}

/**
 * @param {{start:Date, end:Date}} task
 */
function* days_in_task(task) {
   const day = 24*60*60*1000;
   const duration = task.end - task.start;
   if (duration < 0 || duration > 365*day) throw new Error(`Invalid task: ${JSON.stringify(task)}`);
   let d = new Date(task.start);
   while(d <= task.end) {
     yield d;
     d = new Date(d.getTime()+day);
   }
}


// Given a map and a key,
// returns the value associates with the given key in the map if it exists
// otherwise insert a value using the given default_val function and returns it
function get_or_default(map, key, default_val){
      let value = map.get(key);
      if (value === undefined) {
         value = default_val();
         map.set(key, value);
      }
      return value;
}

// Returns a mapping from owner to a mapping from dates to projects
function owners_dates(tasks){
   const result = new Map();
   for(const task of tasks) {
      const owner = get_or_default(result, task.owner, () => new Map);
      for(const date of days_in_task(task)) {
         const projects = get_or_default(owner, date.getTime(), () => new Set);
         projects.add(task.project);
      }
   }
   return result;
}

function final_table(results, start) {
   const day = 24*60*60*1000;
   return Array.from(results).map(([owner, dates])=> {
     const line = [owner];
    for([date, projects] of dates) {
      line[1 + (date - start)/day] = [...projects].join(", ")
    }
    return line;
   });
}