To summarise, the passé simple is an archaic form that has lost its uses along the years. A few centuries ago, it was used as a common past tense, similar to the simple past in English, but it was progressively replaced in everyday speech by the passé composé and the imparfait.
No living French speaker has ever used the passé simple in a normal conversation, except for some archaical/comical effect, or in very few idioms where the passé simple structure has been fossilised (e.g., « Il fut un temps »).
This said, yes, it is used in literature (novels, [fairy] tales...), only written thus, to narrate a sequence of events. It is thus strictly restricted to this specific literary (narrative) written form. For instance, it is extremely unlikely to see it in written correspondence, even very formal, or in essays. So, unless you are planning on writing literature in French, you simply do not need to use it, only to understand it in your reading.