Cleaning up data from Google Sheets

I’m successfully bringing in survey data from Google sheets, related to surveying, but the survey data is not great from an analytics perspective once it is in CODA because some of it is not numbers.

For example:

We take Net Promoter Score on all closed tickets but because of the way the survey is formatted, some of the data comes in with text. See picture. How do I automatically map and update the data I am getting in from Sheets to another column?

Use case:
I want to take “Extremely Likely - 10” to be mapped to “10”
I want “Extremely Unlikely - 1” to be mapped to “1”
I want “About what I expected” to be mapped to “3”


Hi @Bennett_Potter, one of the ways would be to create a new formula column in Coda and use the Switch() statement to map the score.

“Extremely likely - 10”, 10,
“About what I expected”, 3,
"Extremely Unlikely - 1, 1,

Hope this helps!