I have created a financial model in Google Sheets. I am trying to extract dynamic arrays of various yearly values in a table on a particular sheet.
I use Make to send data between Bubble IO and Google Sheets. In total, there are 25 arrays that I want to extract and send to Bubble. Each array will be sent to Bubble as a “list of numbers” through Make by concatenating the array and using commas and then its sent to Bubble.
I have two versions: the first, where the arrays are arranged horizontally, and the second, where the arrays are arranged vertically.
The length of the array is dynamic and based on the life expectancy of my Bubble users. For example, the current age of a user could be 30, and their life expectancy is 100. As a result, I know the length of my array should be 70. In Google Sheets, I have used a formula to find the 70th row or column of the array. The formula/location of the 70th row or column also considers the starting point of the 1st row or column.
See example screenshots below.
I then use the “Get Value from a Cell” module to send this information to Make which forms to the next step. This ensures I only call the values I need.
The next step is using the “Get Values from a Range” module to call the table and the arrays within it dynamically.
The starting point of the table is fixed, but the ending point is dynamic based on the array’s length.
Example Make Call Module Set up when array is arranged horizontally
- A4:[last column obtained from previous module]29
Example Make Call Module Set up when array is arranged vertically
- A4:Z[last row obtained from previous module]
Please see the example based on array arranged horizontally
The Request - Converting the Array into Concatenated String Text Seperated By Commas
Please see the link to a Google sheets spreadsheet with yearly values that I want to extract values, concatenate to a list as text. As mentioned above, there is a tab for both array orientations (rows & columns)
Forecasted Cashflows - Horizontal - Rows
When the arrays are set up in rows, the array output is a Bundle. I then want to turn the Bundle into concatenated string text separated by commas. I would prefer to receive Bundles in the hope that Makes dynamically concatenates all values within each bundle.
Forecasted Cashflows - Vertical - Columns
When the arrays are set up as columns, this becomes problematic as Make reads data in rows. Each value and its position in each bundle relates to a particular column. I would first need to isolate each value and its position in a bundle and then collate them to ensure I have an array which gives the correct information.
Following the above, I don’t know how to take the bundle, concatenate it, and then turn it into a text string separated by commas using the array. Here’s an extract of my Bubble Database to show how the data should look once it has been sent from Make. This was based on when I used Zapier.
I know I need to use an array aggregator or iterator, but i’m not sure how to do it.
Can someone give me a hand? If someone can help me get one array, I can do the others.
Thanks
GB