Introduction
The rest of this documentation has a complex situation and worksheet example for using three google sheet formulas. But the following link offers just the formulas with a brief description of each one.
Situation
Back during Covid Lockdown (mostly 2020) I created a Django Site that stored most of the content in a Postgres database and deployed it dynamically on AWS. When my free year was up, it got too expensive and I was happily working for money, so I undeployed my site. My application still works in development. I am in the process of deploying some of my content to this site, which is super cheap.
- My goal was get all the paragraphs for a given group and then all the references for a given paragraph. I am doing this in Google Sheets, where one tab represents on table.
- It helps that the paragraph records in my database already have html coding.
- The data was stored in five tables, with the main one being a paragraphs table.
- Each paragraph can have many groups and also many references. These are both many-to-many relationships, so that references and groups can each have many paragraphs.
- Since the table relations are many-to-many and the csv data is flat, the filtering strategy is complex.
Note - This probably should have been done using a postgres data dump and loaded into a new database on my new computer, but CSV files seemed easier at the time. I was probably wrong, but at least I'll learn about google sheets filtering formulas!
Action
Here is a worksheet example to refer to. To return from modal, click the X box at the top right of the modal, or Close at the bottom right.
The example tab [sheet] is named paragraphs for group
Note - the steps are top (first three) to bottom (final formula) and from left to right. May need to scroll down a bit.
Original Setup
- In the example we will be working on a worksheet on the paragraphs for group tab
- In the planning stage, paste the expected column headings.
- For example, start with the following headings
- Group Id at A1
- Number of Paragraphs at B1
- Two columns: group_id at F1 and paragraph_id at G1
Step One (orange) - Find the group_id you want.
- This is a manual step and not transparent.
- Keep in mind: one group has one-to-many paragraphs, sometimes ordered and sometimes not.
- Go to the group tab with many rows and columns, then sort by group_id and then group_sort, thus keeping the ordered paragraphs in order within the parent group.
- By looking at the row data, find the type of paragraph you want. The group id on that row will be the one you want.
- Copy the group_id value that is associated with the paragraph(s) you want to find.
- Paste the group_id in the paragraphs for group tab in field A2.
Step Two (purple) - Count the paragraphs in the selected group.
- Underneath the heading in B1 (heading is Count Group ID should be Number of Paragraphs)
- In B2, type the formula =COUNTif(groups_paragraph!E2:E712,A2)
- The number of paragraphs that are children of the given group_id will then be in B2.
- Counting promotes reuse by ensuring the list of list of paragraphs does not overwrite the actual paragraph data.
- Now it does not matter, but if you are doing it again, you may need to add rows above the paragraph date, based on the paragraph count.
Step Three (green) - get the paragraph_ids for the hard-coded group id in A2
- The column headings are group_id (F1) and the associated paragraph id (G1)
- Start at the row under the column heading, Group Id
- In F2, type the formula =Filter(groups_paragraph!E2:F780,groups_paragraph!E2:E780=A2)
- The group_ids (all 2s) and the paragraphs_ids will be listed under their respective headings
- The paragraph_id field locations G2 through G5 will be in the next formula that actually retrieves the paragraphs
Step Four (blue) - get the complete paragraph row for each paragraph id in the G columns (G2 through G5 in the example)
- The paragraph tab has all the paragraph headings, they need to be cut and pasted below the end of the list of paragraph ids.
- In the example, I pasted the paragraph headers in A10 through L10
- In A11, type the formula =Filter(paragraphs!A2:L678, (paragraphs!A2:A678=G2) + (paragraphs!A2:A678=G3)+ (paragraphs!A2:A678=G4) + (paragraphs!A2:A678=G5))
- The results appeared in A11 through L14 (see the worksheet link above)
Results
Here a usage example from this site.
