There's a few different questions here. Firstly, which music was he writing for his church? Second, what would the churchgoers have actually heard? Thirdly, what does it mean to appreciate that music?

Let's start with the first of these. Wikipedia's list follows the orderings made by various German academics over the course of the 19th century, once [J.S.] Bach had been "discovered" (canonized). This listing is not chronological, but rather goes by what sort of piece it was. This list puts vocal works first, including a massive amount of cantatas, all of which he'd composed for his church---but which aren't performed anywhere near as often today. Eventually one gets to keyboard and instrumental works, which were disproportionately important in consolidating his fame---much of the Well-Tempered Clavier, for instance, was written as teaching material for other musicians, rather than for an audience of churchgoers (and perhaps rather than for an audience at all). Likewise, the Musical Offering was, per one of those pieces of lore so essential to the mythology surrounding Bach, written because some king wanted to test if he was really that good.

Most of what the parishioners would have heard, from my understanding, would've been the cantatas. Notoriously, Bach nearly wrote one every week when he started his job in Leipzig in the 1723-24 cycle, and then largely stopped a year or two later once he had enough. (This is common knowledge among musicians, but I did have to check Christoph Wolff's Grove article to remember the dates. One a week is a slight exaggeration, but 40 a year isn't that far from 52.) So, by our modern notions of what makes something a "great work of genius" or the like, these certainly aren't the most accomplished works, and they weren't intended for posterity or for musical sophisticates the way that the keyboard works were. He evidently didn't value the act of writing them enough to continue writing them so often. Yet, that's an anachronistic criteria to look at them, and they're clearly a huge part of his output, and the one that the churchgoers would have heard most often.

Another question is whether the performance practice at the time would have allowed for a particularly subtle hearing. I don't have my New Bach Reader where I currently am, so I can't check the exact document, though it apears to be quoted in this article with some useful discussion. In summary, there are some letters from Bach to his church administration demanding that a certain number of performers be made available for services. The numbers are quite low by modern standards, to the point that many will joke that maybe it's good we modern listeners weren't at the premieres. His choirs were often just 2 or 3 people to a part, and with untrained singers, that's enough to sound very out of tune and wispy. So, would the music really have sounded grand in the way we now understand it?

With those caveats in mind---they wouldn't have heard the works that are now most famous, and they may not have been performed very well---we can now move to the most interesting question: what would it mean for them to appreciate that music anyways? Out of my own interests/specialties, I'll reframe that question a bit as, what would the audience have recognized in Bach's music?

Again, it varies by genre. And it's important to note that most of Bach's music was in recognizable genres common to North German church music at the time, and in that regard wouldn't have been all too different from what's at other churches. Many of these genres, furthermore, relied on materials that would already have been decently well-known. For example, the genre beaten to death in music theory study at music conservatories is the chorale, in which all four parts of the chorus sing roughly the same rhythm and form a harmonization of a melody. Bach didn't compose these melodies; they were from long-standing Lutheran tradition and likely would have been recognizable. The 16th-17th century composer Hans Leo Hassler is perhaps best known for this setting of a chorale that would be later and more famously adapted by Bach in the St. Matthew Passion (this is only one of its several appearances there). And this is just a single example---all of Bach's chorales, a central portion of most (if not all) of his sacred works) would have been based on such a melody. And such borrowings surely would have made his music more understandable to the parishioners---I think it's fair to say music always makes more sense when you know the tune.

Likewise, borrowings and common grammar make it into a lot of his works, even the ones that are now afforded the highest prestige by current standards. I'd mentioned above that the first few preludes of the Well-Tempered Clavier are essentially demonstrations of certain compositional/improvisational techniques, to the point that one of them is borrowed from a version he'd written out largely in shorthand to teach one of his sons (here's an excerpt from the Clavierbuchlein fur WF Bach). Likewise, the Goldberg Variations are certainly held in high esteem, but there's a reasonable theory out there that the aria wasn't even composed by Bach, and he was just creating some variations on a popular tune from the time. So, the pervasiveness of these elements likely would have given the audience at least enough to appreciate.

I've sidestepped what may have been the question you really wanted to ask, which is: did the audience know how good he was? To which I honestly have to say, I don't know, and we'd need a different commenter to answer. I'd say that the mere fact that the Musical Offering exists---that a king would know he was worth giving a hard task to---shows he had a reputation, but I don't know how the reputation developed at the time, and I won't pretend to. My own interests and training are more in what Bach would have had in common with other composers, rather than on the historical particularities and situations of his particular pieces. But, I hope this answer has at least illuminated some of what it might have meant to appreciate Bach's music.