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.

It can not be done. The displayed row height can not be less than the height of the content. You can set the row height manually or in code, and it will do nothing. If the content is deleted, then the row will go back to the default, but as long as the content is higher than the row height, the row will always adjust to show the entire height. The width is different. You can "clip" the width, but not the height. It's kind of stupid, that it's set up this way, but that's the way it is. — Alan Wells, May 03 '17 at 00:49
I've voted to close your question, as being "to broad." A "good" question includes code, and shows what you have tried. But don't feel bad, I've broken the rules by answering the question in the comment section, which I'm not supposed to do. I do that in part because comments can't get down voted. Welcome to Stack Overflow. — Alan Wells, May 03 '17 at 00:53
Thanks for the help! You are saying that the Excel notion of creating a hyperlink that links to a given cell in another spreadsheet is impossible? https://productforums.google.com/forum/#!topic/docs/8WecJzLDc9U — Eric Henry, May 03 '17 at 12:14
The approach I have been trying is to create a note in every row for a given column. I see that addNote can't work in the context of a cell so I am starting to write code that would run in the menu context and add all the notes at once using addNotes. But I'm afraid this is an abuse of the notes concept and that there is a better way. I wanted to ask before I get down in the weeds and get too wedded to my approach. function gradeDisplay(sid) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var cell = ss.getActiveCell(); cell.setNote("Student: "+sid+"\\nTranscript: "+transcript); — Eric Henry, May 03 '17 at 12:15

score 0 · Accepted Answer · answered May 03 '17 at 12:53

0

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

}

answered May 03 '17 at 12:53

OblongMedulla

1,471
9
21

1

Can a script add a button to each cell in a column? It looks like this is impossible: http://stackoverflow.com/questions/28668766/adding-buttons-to-google-sheets-and-set-value-to-cells-on-clicking The alternative would require teaching each of the teachers to use the call from the menu which may be prohibitive. Is it a good approach to add a menu function that I could run before the teachers use the spreadsheet that would add a note to ever cell in the GPA column? This would allow teachers to simply click on the GPA for a student and to see a note popup with the full transcript. – Eric Henry May 03 '17 at 14:40
in the example above, you select the student and press the student data button- then the popup appears for the student selected- – OblongMedulla May 03 '17 at 14:43
1

I think I see. I have a single button located somewhere like in a frozen header row that is labeled something like "View transcript" and linked to your code. The user experience is to be in a cell in the row of the student they are considering and to click the button and get the transcript of that student. Very cool! I definitely hadn't understood how to correctly use buttons like this, thanks! – Eric Henry May 03 '17 at 15:59
1

Definitely. I am making good progress now. I'll add my code when I finish. I upvoted your answer. It says that it won't show up because I'm too new. I think the question is answered completely at this point. Thank you very much. – Eric Henry May 03 '17 at 19:46
1

@eric Besides upvoting OblongMedulla's answer you can also [accept](http://stackoverflow.com/help/accepted-answer) it (if it works for you) ;) – pkowalczyk May 04 '17 at 16:04

Effectively display a chunk of data in a single cell

1 Answers1