Apps Script: Search and display sheet content in HTML-CodePudding

I have a sheet with 5 columns. Each column has following information about each student:

A: Full name
B: Level of education
C: Number of weekly home assignments
D: Grade for each home assignment
E: Notes/recommendations about a possible personal improvement plan

I want to use Apps Script instead of formulas to search for any student and display the details of that student in an HTML file. Creating the HTML file and connecting it to my script is not the issue, because I know how to do that and have done it before. But I am stuck with modifying the script in a way that it would allow me to search for any student within the sheet and get all information about that student in a (callback) function/variable/array or whatever that I can then call/use in my HTML file.

Thank you so much in advance for any hint or advice in the right direction.

Current code can find out how many times a specified student has been listed in the sheet (because a student can be listed many times with some additional information in the sheet!!!!), but I have no idea how to move on from here :(

function searchAndFindStudentData() {

  var ss = SpreadsheetApp.openById("SPREADSHEET_ID");
  var sheetname = ss.getSheetByName("SHEET_NAME");

  var countStart = 0;
  var lastColumn = sheetname.getLastColumn();
  var lastRow = sheetname.getLastRow();
  var sheetValues = sheetname.getRange(2, 1, lastRow, lastColumn).getValues();

  for (var row in sheetValues) {
    for (var colum in sheetValues[row]) {

      if (sheetValues[row][colum] == "John Smith") {
        countStart  ;
      }
    }
  }
  Logger.log(countStart);
}

CodePudding user response：

This should get you started:

function searchAndFindStudentData() {
  const ss = SpreadsheetApp.openById("SPREADSHEET_ID");
  const sh = ss.getSheetByName("SHEET_NAME");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  let info = {}
  const r = SpreadsheetApp.getUi().prompt("Enter Student Name" , SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
  if(r.getSelectedButton() == SpreadsheetApp.getUi().Button.OK) {
    let name = r.getResponseText();
    vs.forEach(r => {
      if(r[0] == name ) {
        if(!info.hasOwnProperty(name)) {
          info[name] = {level:[],assignments:[],notes:[]}
        } else {
          if(r[1]) {
            info[name].level.push(r[1]);
          }
          if(r[2] && r[3]) {
            info[name].assignments.push([{num:r[2],grades:r[3]}]);
          }
          if(r[4]) {
            info[name].notes.push(r[4]);
          }
        }
      }
    }); 
    return info;
  } else {
    SpreadsheetApp.getUi().alert('Search Canceled');
  }
}