google-apps-scriptgoogle-sheetsgoogle-formsgoogle-form-quiz

Retrieving Quiz result using Google Apps Script for Google Sheets

A google forms quiz is linked to my spreadsheet, and returns the result of the quiz in a certain cell.

Let us say the result is "2 / 20"

I would like to convert this result into a percentage in Google Apps Script

There is probably a neater way, but I wanted to do something like this:

  var dataRange = sheet.getRange(startRow, startCol, numRows, numCols);
  var data = dataRange.getValues();
  var cell=data[2][2]; //address of quiz score

  var slashIndex=cell.indexOf("/"); 
  var numerator=cell.substr(0,slashIndex-1);  
  var denominator=cell.substr(slashIndex+1);
  var result=numerator/denominator;

The problem is that when retrieving the result in the cell, I only get up to (and not including) the slash. So my code never starts, because the index of the slash is -1.

Solution

  • Google Sheets use cell formatting to display the Google Forms quiz score. If you want to get the displayed value, then instead of

    var data = dataRange.getValues();

    use

    var data = dataRange.getDisplayValues();