IME having worked for my local council some years ago, if the council bid on it for you rather than you bidding on properties you are interested in, that generally means you have been allocated it through the homelessness policy. With my council, if you turn it down the council will discharge their duty to you unless you can prove it is not suitable on medical grounds. Distance from support networks/not having transport won’t count.
Are you not on the general council housing waiting list? That can take many years though.
I’d advise you to check your council’s allocation policy for homelessness OP, and you should seriously think about taking it as private renting with children is very insecure and obviously a lot more rent.
You could try it and then go into private renting if it really is awful. You have nothing to lose at this point.
How old are your DC? If you work, would you be better off on benefits if under school age for a few years so you don’t need childcare? Retrain for a better job in the meantime or study (with childcare grants if studying) so you are in a better position to have more housing choices later on? Could you learn to drive now you can save money for cheaper rent if DC are pre-school etc. Even on benefits with maintenance loan for a Uni course, you may be better off.
This offer may well change your life, if you use it cleverly to improve your prospects, within the safety net of a secure home.