one-to-many reporting between sheets in Google Spreadsheets

3
votes

I am trying to replicate a "one-to-many relationship" (as in a database) from one sheet to another (inside the same spreadsheet).

I've done a research in similar topics, but no solution seems to cover me.

In essence, this can be described in principle as following:

  • Let's say we have a sheet (PrimaryTable) where Elements are stored uniquely (no duplicates) with an pkElementID column (primary key),and a
  • second sheet (SecondaryTable) where properties for each Element are stored, according to the ElementID they correspond to (there is also an fkElementID column acting as foreign key).

Some complimentary notes about this:

  • The PrimaryTable consists of more than 2000 lines, where SecondaryTable is around the same in extent.
  • The aforementioned "properties" in the SecondaryTable are in fact spread among several columns which are also joined in one column by an array formula.
  • The "reported" data from the secondary table are not numbers, but large strings (~1000 chars or more) of HTML text, which need to be joined.
  • The formula should be an Array Formula, so that it gets populated automatically as more rows are added to the sheet, preferably in a header cell of the first row so it can be locked down.

An example of all this would be the urls of several images per Element inside the HTML code ( tag) for each row of the secondary table, reported to the corresponding cells in the PrimaryTable.

Until now, I had found a solution after some discussion on Ted Juch's blog and a contribution to a public spreadsheet for that purpose, which used the following 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)))

The above formula was suggested I think from TC Adam, and while I seem to have got an understanding of how it works, I'm almost incapable of maintaining it or modifying it to different cases. In a last example it returns an error of "out of range" and can't figure why.

The only modification I have since made to the formula was to add an "if (row(A1:A)=1;"Column Header"; FORMULA)" inside the initial array formula, resulting to:

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 am suspicious as to whether the large dataset produces such a big array that cannot be handled properly under the limitations of Google spreadsheet hence the cause of the problem, or it's me who have messed up the formula trying to adapt it.

I have also tried to make a custom function using the ArrayLib library which has a similar function named ArrayLib.filterByText but it only runs for a few rows until it gets an "error: Service timed out" message.

Should I try implementing a query method? Can anybody help?

1
javascriptgoogle-apps-scriptgoogle-sheets
The bottom line is that spreadsheets are not meant to represent a one-to-many relationship. – Pete B.
@PeteBelford I know it's not a RDBMS but I need this implementation to produce live KML data via google fusion tables. – enma
You could always try to write an app that will determine if another app crashes or write a regular expression that ensures that there are n number of a's followed by the n number of b's. – Pete B.
Adam is active on this board too. I am sure he, and others, would be able to offer advice re your Google Apps Script ideas (either using ArrayLib.filterByText or other). You should tag this question with google-apps-script. Personally I wouldn't be able to look at the question further without an example spreadsheet containing some sample data, and an example of the formula successfully working, so we can see what output is expected and test new ideas against the data - so if you can provide a link to a spreadsheet, that would be great. – David Tew
I've shared a version of the mentioned document here: <docs.google.com/spreadsheet/…> If it looks like Greek to you, it's because IT IS IN GREEK! Following my question, I would like the calculated result of column I from rows entered in Sheet "pinFEKs" to be filtered and reported back to sheet "Pins"according to the matching columns PinID in both sheets. PLEASE HELP!!! – enma

1 Answers

0
votes

Here is the "best" solution I found for this (credits go to Alexander Ivanov)

Please note that the one-to-many relationship is not saved in a normalized way, rather in a serialized, but it does work out of the box, and might be an inspiration for a more complete and thorough solution.