Sean Dooley Sean Dooley - 2 months ago 7
Javascript Question

How do I return a different column in a 2d array?

I have this code here:

function getName() {
var id = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
var data = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/spreadsheetIdHere/edit#gid=0')
.getSheetByName("Paste Data")
.getDataRange()
.getValues();
for (var i in data) {
if (data[i][8] = id) {
return data[i][5];
} else {
return "Student Not Found";
}
}
}


It should be looking at the user input in the spreadsheet cell and matching it to a row in another spreadsheet. I'm trying to make it match row [i] and return me column [5] when matched to column [8]. At the moment, it seems to return the first row and the id it found, but they are not in the same row.

Please help.

Answer

While trying to compare the value in data[i][8] to the id instead of actually comparing you were assigning id value to data[i][8]. Corrected version as follows:

function getName() {
  var id = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
  var data = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/spreadsheetIdHere/edit#gid=0')
                           .getSheetByName("Paste Data")
                           .getDataRange()
                           .getValues();
  for (var i = 0; i < data.length; i++) {
    if (data[i][8] == id) {
      return data[i][5];
    }
  }
  return "Student Not Found";
}