fill out template sheet when a name in a dropdown menu is selected

Question

I have created a grade report Google spreadsheet, but now I'm looking for a way to fill in my template report sheet with the grades from one student. How can I achieve this?

In file 1 I have all the grades of the students. In file 2 I have the template of the grade report. How can I create a dropdown menu in the template grade report that displays all the students and when I click on one of the students automatically fills in all the fields in the grade report? I now use the importrange function to import the data from the column into the template, but now I need a grade report file for every student. It would really be much more efficient than to create one grade report file for every student.

I hope you can help me.

File 1: https://docs.google.com/spreadsheets/d/12M8W7fARJJZN90wM79U4x8RmcnZ08kFSDcZYnB26HB0/copy

File 2: https://docs.google.com/spreadsheets/d/13-0qhCudC7EYRgOs2UpbJPRSq2nKoBiG2Md3-U8_U1Q/copy

Answer 1

You can use the following code:

var LIST_WITH_GRADES_ID = 'YOUR_ID';
var LIST_WITH_GRADES_STUDENTS_A1 = 'Rapportoverzicht!B2:Y2';

function onOpenSpreadsheet(e) {
  var students = SpreadsheetApp.openById(LIST_WITH_GRADES_ID).getRange(LIST_WITH_GRADES_STUDENTS_A1).getValues()[0];
  var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(students).setAllowInvalid(false);
  SpreadsheetApp.getActive().getRange('Rapport!E1').setDataValidation(rangeRule);
}

function onEditSpreadsheet(e) {
  var rapportSheet = SpreadsheetApp.getActive().getSheetByName('Rapport');
  if (e.range.getA1Notation() == 'E1' && e.range.getSheet().getName() == 'Rapport') {
    var studentCol = findStudentCol(e.range.getValue());
    copyStudentGrades(studentCol);
  }
}

/* Returns 1-indexed student column */
function findStudentCol(student) {
  var sheet = SpreadsheetApp.openById(LIST_WITH_GRADES_ID);
  var studentsRange = SpreadsheetApp.openById(LIST_WITH_GRADES_ID).getRange(LIST_WITH_GRADES_STUDENTS_A1);
  for (var col=1; col<=studentsRange.getWidth(); col++) {
    if (studentsRange.getCell(1, col).getValue() == student) 
      return col + studentsRange.getColumn() - 1;
  }
}

function copyStudentGrades(studentCol) {
  var gradesSheet = SpreadsheetApp.openById(LIST_WITH_GRADES_ID).getSheetByName('Rapportoverzicht');
  var templateSheet = SpreadsheetApp.getActive().getSheetByName('Rapport');

  templateSheet.getRange(4, 4, 30, 1).setValues(
    gradesSheet.getRange(4, studentCol, 30, 1).getValues());

  templateSheet.getRange(4, 10, 25, 1).setValues(
    gradesSheet.getRange(36, studentCol, 25, 1).getValues());
}

You will have to put your grades sheet's ID on the first line, and add this script to the editor of your "template" sheet. You will also need to set onOpenSpreadsheet and onEditSpreadsheet as installable triggers in order to make it work (see managing triggers manually).

Edit

In order to use two different grade files and import the remarks as well, you can use the following edit of the previous code:

var LIST_WITH_GRADES_ID = 'YOUR_LIST_WITH_GRADES_1_ID';
var LIST_WITH_GRADES_2_ID = 'YOUR_LIST_WITH_GRADES_2_ID';
var LIST_WITH_GRADES_STUDENTS_A1 = 'Rapportoverzicht!B2:AG2';
var NSTUDENTS = 24; // Number of students

function onOpenSpreadsheet(e) {
  var students = SpreadsheetApp.openById(LIST_WITH_GRADES_ID).getRange(LIST_WITH_GRADES_STUDENTS_A1).getValues()[0];
  var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(students).setAllowInvalid(false);
  SpreadsheetApp.getActive().getRange('Rapport!E1').setDataValidation(rangeRule);
}

function onEditSpreadsheet(e) {
  var currentStudent = e.range.getValue();
  var rapportSheet = SpreadsheetApp.getActive().getSheetByName('Rapport');
  if (e.range.getA1Notation() == 'E1' && e.range.getSheet().getName() == 'Rapport') {
    var studentCol = findStudentCol(currentStudent);
    copyStudentGrades(studentCol);
    importRemarks(currentStudent);
  }
}

/* Returns 1-indexed student column */
function findStudentCol(student) {
  var sheet = SpreadsheetApp.openById(LIST_WITH_GRADES_ID);
  var studentsRange = SpreadsheetApp.openById(LIST_WITH_GRADES_ID).getRange(LIST_WITH_GRADES_STUDENTS_A1);
  for (var col=1; col<=studentsRange.getWidth(); col++) {
    if (studentsRange.getCell(1, col).getValue() == student) 
      return col + studentsRange.getColumn() - 1;
  }
}

function copyStudentGrades(studentCol) {
  var gradesSheet = SpreadsheetApp.openById(LIST_WITH_GRADES_ID).getSheetByName('Rapportoverzicht');
  var gradesSheet2 = SpreadsheetApp.openById(LIST_WITH_GRADES_2_ID).getSheetByName('Rapportoverzicht');
  var templateSheet = SpreadsheetApp.getActive().getSheetByName('Rapport');

  templateSheet.getRange(4, 4, 30, 1).setValues(
    gradesSheet.getRange(4, studentCol, 30, 1).getValues());

  templateSheet.getRange(4, 10, 25, 1).setValues(
    gradesSheet.getRange(36, studentCol, 25, 1).getValues());

  templateSheet.getRange(4, 6, 30, 1).setValues(
    gradesSheet2.getRange(4, studentCol, 30, 1).getValues());

  templateSheet.getRange(4, 12, 25, 1).setValues(
    gradesSheet2.getRange(36, studentCol, 25, 1).getValues());
}

function importRemarks(student) {
  var remarksSheet = SpreadsheetApp.openById(LIST_WITH_GRADES_ID).getSheetByName('opmerkingen rapport');
  var remarksSheet2 = SpreadsheetApp.openById(LIST_WITH_GRADES_2_ID).getSheetByName('opmerkingen rapport');
  var templateSheet = SpreadsheetApp.getActive().getSheetByName('Opmerkingen');
  var studentRow;

  for (var i=2; i<(2+NSTUDENTS); i++) {
    if (remarksSheet.getRange(i, 1).getValue() == student) {
      studentRow = i;
      break;
    }
  }

  templateSheet.getRange('B5').setValue(remarksSheet.getRange(studentRow, 2).getValue());
  templateSheet.getRange('B11').setValue(remarksSheet2.getRange(studentRow, 2).getValue());
}