To have separate sounds for individual app and individual people, go to settings, notifications, advanced settings, scroll down to manage notification categories and toggle it on. Now in each and every app there will be a section called notification categories and you can set tones there.
If you choose Google messages, to set a text tone, open a contact conversation, tap the 3 dots, go to details, notifications, there you will be able to pick a tone you wish to set.
