CODEWITHSUNDEEP
google-sheetsspreadsheet
Macterror
Macterror

Reputation: 431

Associate specific string to a number in google sheets

I am using a google forms to collect responses which I will then use to score people. Unfortunately some of those responses only make sense in a non numeric form, here is an example:

Q: What is your most common mode of transportation?

I want to be able to have google sheets automatically convert those string responses into a number, as in Car will be 20, carpool 15 and so on so that I can "grade" them and give them a score. Can this be done through google forms? Or maybe some sort of dictionary function?

Thank you!

Upvotes: 0

Views: 995

Answers (2)

ttarchala
ttarchala

Reputation: 4567

Another method, requiring no coding, would be to make a worksheet with the encoding of the options and then use VLOOKUP to translate them.

Upvotes: 2

Ray
Ray

Reputation: 3959

Yep, this can be done through Google Forms. Have a look at https://developers.google.com/apps-script/reference/forms/duration-item#setPoints(Integer)

Using their code, you could go something like

var formResponses = FormApp.getActiveForm().getResponses();
// Go through each form response
for (var i = 0; i < formResponses.length; i++) {
  var response = formResponses[i];
  var items = FormApp.getActiveForm().getItems();
  // Assume it's the first item
  var item = items[0];
  var itemResponse = response.getGradableResponseForItem(item);
  if (itemResponse != null && itemResponse.getResponse() == 'Car') {
   var points = item.asMultipleChoiceItem().getPoints();
   itemResponse.setScore(points * 20);
   // This saves the grade, but does not submit to Forms yet.
   response.withItemGrade(itemResponse);
   }
}
// Grades are actually submitted to Forms here.
FormApp.getActiveForm().submitGrades(formResponses);

Upvotes: 1

Related Questions