Retrieving Quiz result using Google Apps Script for Google Sheets

Question

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.

To confirm, your quiz result appears in C3? Or in B2? data[2][2] is the 3rd element of the array that is the 3rd element of data (i.e. C3 if dataRange is obtained from sheet.getRange(1, 1, ... , ...);. if you share a screenshot of the sheet layout, that would be helpful in diagnosing the issue. — tehhowch

Rubén Rubén · Accepted Answer · 2018-12-01T23:28:57

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();

Retrieving Quiz result using Google Apps Script for Google Sheets

1 Answers