I am creating a non-code app that will use GoogleSheets to make calculations and send the results to BigQuery.
On Make I am having Google Sheets (watch rows) on the one end of the scenario and BigQuery (Upload data streaming) on the other.
The schema of the BigQuery table has two collections of arrays, one for inputs and another one for outputs, such as the one shown in the below image.
The number of items in a collection and number of values in an array will vary, so I cannot flatten the structure to manually indicate in Make’s configuration for BigQuery the heading in Google Sheets where to find them. Instead, I will need to use functions and a combination of intermediate modules such as ‘Repeater’, ‘Array aggregator’, Tools’,…
What I need is help on:
1. What would be the best way to arrange the data in Google Sheets to make it easily retrievable by BigQuery? My guess is that I need to use a denormalised structure with a different table for each of the two collections of arrays (one for the inputs and another one for the outputs). The image below shows the one for the inputs. Would that be correct?
2. The combination of intermediate modules to use in Make and their order of execution.
3. The functions to use in such modules and their formulas.
I do not expect one single answer to solve me this challenge, but I would really appreciate any information you could provide that helps me solve different sections of the puzzle.
Thank you.