title: one-to-many reporting between sheets in Google Spreadsheets
body: <p>I am trying to replicate a "one-to-many relationship" (as in a database) from one sheet to another (inside the same spreadsheet).</p>
  
  <p>I've done a research in similar topics, but no solution seems to cover me.</p>
  
  <p>In essence, this can be described in principle as following:</p>
  
  <ul>
  <li>Let's say we have a sheet (PrimaryTable) where Elements are stored
  uniquely (no duplicates) with an pkElementID column (primary key),and a</li>
  <li>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).</li>
  </ul>
  
  <p>Some complimentary notes about this:</p>
  
  <ul>
  <li>The PrimaryTable consists of more than 2000 lines, where
  SecondaryTable is around the same in extent.</li>
  <li>The aforementioned "properties" in the SecondaryTable are in fact
  spread among several columns which are also joined in one column by
  an array formula.</li>
  <li>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.</li>
  <li>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.</li>
  </ul>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <blockquote>
    <p>ArrayFormula(TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE('Secondary
    Table'!B1:B&amp;", ");(A1:A=TRANSPOSE('Secondary
    Table'!A1:A))*(LEN(A1:A)>0))&amp;REPT(CHAR(9)&amp;"
    "&amp;CHAR(9);TRANSPOSE(ROW('Secondary Table'!A1:A))=ROWS('Secondary
    Table'!A1:A)));", "&amp;CHAR(9);0)))</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <blockquote>
    <p>ArrayFormula(IF(ROW(A1:A)=1;"Column Header";
    TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE('Secondary Table'!B1:B&amp;",
    ");(A1:A=TRANSPOSE('Secondary
    Table'!A1:A))*(LEN(A1:A)>0))&amp;REPT(CHAR(9)&amp;"
    "&amp;CHAR(9);TRANSPOSE(ROW('Secondary Table'!A1:A))=ROWS('Secondary
    Table'!A1:A)));", "&amp;CHAR(9);0))))</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Should I try implementing a query method?
  Can anybody help?</p>