I am facing a challenge in replicating a "one-to-many relationship" (similar to a database) from one sheet to another within the same spreadsheet.
Despite researching similar topics, I have not found a solution that fits my specific needs.
Basically, here is what I need to accomplish:
- There is a sheet (PrimaryTable) where unique Elements are stored with a pkElementID column as the primary key, and
- a separate sheet (SecondaryTable) where properties for each Element are stored, linked through an fkElementID column acting as a foreign key.
Additional details about this setup include:
- The PrimaryTable has over 2000 rows, while the SecondaryTable is of similar size.
- In the SecondaryTable, the properties for each element are spread across multiple columns but can be concatenated into one using an array formula.
- The data from the secondary table consists of large strings of HTML text (~1000 characters or more) that need to be combined.
- The formula should be an Array Formula so it automatically populates as new rows are added, preferably in a locked header cell in the first row.
An example scenario would involve storing URLs of multiple images per Element inside the HTML code (<img> tag) in corresponding cells of the PrimaryTable.
Previously, I found a solution on Ted Juch's blog which used a complex formula:
ArrayFormula(TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE('Secondary Table'!B1:B&", ");(A1:A=TRANSPOSE('Secondary Table'!A1:A))*(LEN(A1:A)>0))&REPT(CHAR(9)&" "&CHAR(9);TRANSPOSE(ROW('Secondary Table'!A1:A))=ROWS('Secondary Table'!A1:A)));", "&CHAR(9);0)))
Although I understood how it works, I struggle to maintain or modify it for different scenarios. It recently threw an "out of range" error, leaving me puzzled.
The only change I made was adding an "if (row(A1:A)=1;"Column Header"; FORMULA)" snippet within the initial formula:
ArrayFormula(IF(ROW(A1:A)=1;"Column Header"; TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE('Secondary Table'!B1:B&", ");(A1:A=TRANSPOSE('Secondary Table'!A1:A))*(LEN(A1:A)>0))&REPT(CHAR(9)&" "&CHAR(9);TRANSPOSE(ROW('Secondary Table'!A1:A))=ROWS('Secondary Table'!A1:A)));", "&CHAR(9);0))))
I suspect the issue may be due to the large dataset creating an oversized array beyond Google Spreadsheet limits, or perhaps I messed up the formula trying to adapt it.
I also attempted to create a custom function using the ArrayLib library, specifically the ArrayLib.filterByText function, but encountered a "Service timed out" error after processing only a few rows.
Should I consider using a query method instead? Any assistance would be greatly appreciated.