Not sure if it is possible, however, I am looking if we can do a query in google sheet, which can give the result for the below table as

Expected Result:

As I am trying to create a dynamic dashboard in Google sheets, where a lot of data is being generated for Query to create plot and I would need the above to maintain consistency

Thanks a lot for any help in this regard

CodePudding user response：

This was posted as an answer but was deleted. I'm not sure why, but try this:

=arrayformula(
     query({A:B;split(sequence(max(A:A)-min(A:A) 1,1,min(A:A))&"_0", "_")},
     "select Col1, sum(Col2) group by Col1 
                             order by Col1 label sum(Col2) ''", 0)
)

CodePudding user response：

If you want a script (a custom function, to be precise) it's here:

function make_table(arr) {
  // [[1,...],[4,...],[1,...],[4,...],[1,...]] --> 4
  const latest_month = Math.max(...arr.map(x => x[0]));

  // 4 --> [1,2,3,4]
  const months = new Array(latest_month).fill().map((_,i) => i 1);

  // [1,2,3,4] --> { 1:0, 2:0, 3:0, 4:0 }
  const obj = months.reduce((ar,m) => ({ ...ar, [m]:0 }), {});

  // [[1,20], [4,30], [1,10], [4,30], [1,10]]   obj --> { 1:40, 2:0, 3:0, 4:60 }
  arr.forEach(row => obj[row[0]]  = row[1]);

  // { 1:40, 2:0, 3:0, 4:60 } --> [ [1,40], [2,0], [3,0], [4,60] ]
  return Object.keys(obj).sort().map(month => [ month, obj[month]]);
}