Mapping Google Sheets to Airtable When Spreadsheet does not Match Perfectly

What are you trying to achieve?

I have some content in My Google Sheets that I need to extract and place over in an Airtable. While the spreadsheet is structured, the contents of each record are not strictly contained to a single row in the spreadsheet. I don’t feel like my case is significantly crazy, but I find that this rift between the construction of the sheets and my table make the setup/mapping less intuitive for me as an early user. In essence, I’ve stored multiple choice math questions in my sheets page. Each complete question takes up 6 consecutive rows (1 row is for the Question, 5 rows are for the answer choices A - E). The columns are more information about the question. However, in AirTable, each record stores the necessary information of each complete question.

To expound, I have to capture these elements in my AirTable: (1) the Question Number, (2) the Question Text, (3) Answer Choice A, (4) Answer Choice B, (5) Answer Choice C, (6) Answer Choice D, (7) Answer Choice E, (8) the Correct Answer.

While all of these elements are simply fields in my AirTable, they are more like a matrix in the spreadsheet, with questions and answers split between rows.

As you can see from the screenshot, the first question (in purple) has the Question text in cell C2, while its corresponding Answer Choices (A - E) are in cells E3 - E6, under the column header “Answer Text”. The correct answer, to each question, is indicated with a “1” (I’ve considered changing the value to ‘TRUE’, but I’m not convinced that is necessary). Mapping these fields is my feat.

Steps taken so far

I began by using a “Search Rows” Google Sheed Module. I didn’t use any filters in this module. What is returned is a bundle for each row in the sheet. Then I used the array aggregator. My goal here was to prepare the data for the iterator to process each row in the spreadsheet. The iterator comes directly after the aggregator. It seems to return something very similar to the step prior, except now some fo the elements within the collection are blank (likely to represent empty cells). Next, I added a router. I thought if I separated rows where the {Question Name} was “Question” from rows where the {Question Name} was “Answer”. It was a goal i was unsuccessful at, but figured the logic might give me some sort of leverage. From there I’m pretty much stuck. I flirted with the idea of a Data Store, but that is completely new for me and I wouldn’t necessarily know how or what exactly was being stored. What I haven’t though through enough is how I’d identify the correct answer and map it correctly to the corresponding answer choice. I’ve shown the screenshots of my Airtable and my Sheets pages here if anyone has some suggestions for what I think shouldn’t be a crazy problem. I really appreciate any feedback!

Screenshots: scenario setup, module configuration, errors