Google Apps Script Transpose & Cartesian Product

Question

I have been building a solution for project resource management in Google Sheets. The idea is that the team manager would input the resource demand per project on a weekly level on a single row. This is a very user friendly and easy solution for the end user. See image below for a description of what the input sheet looks like. Input_Sheet

Since my organization has multiple teams, we want to have a separate sheet for each team. In order to still keep the reporting centralized, I have connected each sheet into BigQuery where I am combining the data from different sheets and finally creating reports in Power BI.

Currently, I am using a mix of google functions such as QUERY, ARRAYFORMULA, SPLIT, FLATTEN to transpose the data into a database suitable format, which is presented in the image below. In order to have the data in a database, I need to transpose the data from a horizontal format into a vertical format. Result_Sheet

My current issue is that there can be around 300 rows and 100 columns and at this point a simple google function is getting too heavy to run. I am now looking for a solution to do the processing using apps script, if that would prove to be a more efficient solution and would allow me to add some logic for how often the script is run.

Dear Experts, do you see that something like this would be possible to do in apps script and how should one do this? I have some coding experience, but I'm new to apps script. I am struggling especially with producing the Cartesian Product, which would allow me to link date with the demand.

I have added a link to my example sheet below.

Link to sheet: https://docs.google.com/spreadsheets/d/1XKyt3BAo5L2RsK2vYpqrlBuEZoehIztGJ_Nl2DN-h-8/edit?usp=sharing

Answer 1

Use an { array expression }, like this:

=arrayformula( query( 
  { 
    Input_Sheet!B2:D2 \ "Date" \ "Demand";
    flatten( iferror(Input_Sheet!E3:H / 0; Input_Sheet!B3:B) ) \ 
    flatten( iferror(Input_Sheet!E3:H / 0; Input_Sheet!C3:C) ) \ 
    flatten( iferror(Input_Sheet!E3:H / 0; Input_Sheet!D3:D) ) \ 
    flatten( to_date( iferror(Input_Sheet!E3:H / 0; Input_Sheet!E2:H2) ) ) \
    flatten(Input_Sheet!E3:H) 
  }; 
  "where Col5 is not null 
   order by Col1"; 
  1 
) )

This is not a Cartesian product. It is more like unpivot.

See the new Solution sheet in your sample spreadsheet.