Effectively display a chunk of data in a single cell

Question

What is the best way to include a large chunk of readable data (with line breaks) in a single row without making that row's height grow to fit the data. As an example, if I want to view the full transcript by clicking on the GPA cell:

row: Name GPA # AP's Grade Makayla 4.0 3 10th

Transcript 2014-2015 -- Algebra 1: A, English 9: A, PE: F, History: A, Spanish 1: C, Physis: C 2015-2016 -- Geometry: A, English 10: A, Music: B, History: A, Spanish 2: C, Chemistry: C 2016-2017 -- Algebra 1: A, English: A, Music: A, History: A, Spanish 3: C, Biology: C

-- Detailed decription: We are improving the process we use for selecting students for AP classes. We offer 20 AP classes and have a spreadsheet for each class with 50-200 students ranked preferences in each sheet. The students are all drawn from a set of ~500 incoming juniors and seniors at the school. Each AP teacher is responsible for going through the list of students who have indicated an interest in their class and selecting the students that will end up in the course. The data for the students is spread across multiple spreadsheets. It would be far more efficient if the data was included in a single spreadsheet. It is straightforward to build summary functions that accomplish most of this but sometimes when looking at a particular student it would be nice if there was a way to view the detailed information without losing your context in the spreadsheet. As an example, if you are looking at the GPA of a student and you want to see how they did in their math classes. The two techniques I have imagined for doing this: 1. Create a script that populates the gpa cell with note (using setNote) that contains the full transcript of the student. 2. Create a link in the cell next to the gpa cell that somehow provides a view of the student's transcript without causing the reader to lose their place in original sheet, possibly by changing the focus in a sheet on another browser tab to the relevant student.

Answer 1

I was able to create a html popup with stu data within. And call the function from a drawing button, or menu item.

function stuData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var activeCell = ss.getActiveRange().getRow();
  Logger.log(activeCell);
  var stuName = sheet.getRange(activeCell, 1).getValue();
  var GPA = sheet.getRange(activeCell, 2).getValue();
  var AP = sheet.getRange(activeCell, 3).getValue();
  var grade = sheet.getRange(activeCell, 4).getValue();
  var t16 = sheet.getRange(activeCell, 5).getValue();
  var t17 = sheet.getRange(activeCell, 6).getValue();
  var t18 = sheet.getRange(activeCell, 7).getValue();

  var htmlApp = HtmlService
  .createHtmlOutput('Student Name: ' + stuName + '<br> GPA:' + GPA + '<br> AP:' + AP + '<br> Grade:' + grade + '<br><br> Transcript - :' + t16 + '<br> Transcript - :' + t17 + '<br> Transcript - :' + t18)
     .setTitle(stuName)
     .setWidth(750)
     .setHeight(220);

 SpreadsheetApp.getActiveSpreadsheet().show(htmlApp);

}