overwrite/edit specific records on an existing sheet with vlookup on save dialog

Question

I have a script that saves data from one sheet I call "forms" over to another sheet I call "records". The function runs when I click an image set as a button in the workbook.

function SaveEWSReport() {
//Save and clear the form
  var ss2 = SpreadsheetApp.getActive();
  var source = ss2.getSheetByName('Form');
  var records = ss2.getSheetByName(source.getRange('A1').getValues()); //get the tab to send the save data to.
  var val = source.getRange('A20:Q20').getValues(); //get the cells with information to copy (contain information concatenated form dropdown cells in B2 to P19) 
  // get values from zero indext cells and save to records. 
  // 0,0=StudentB3.0,1=GradeD2.0,2=Date.0,2=FlagC5.0,3=ReferedByD5.7,0=NotesB9:F15.3,0=TeirB5.5,0=TypeB7. Nulls leave spaces. 
  var write = [val[0][0], val[0][1],null, val[0][2], val[0][3], null, null, val[0][4], val[0][5], val[0][6], val[0][7],null];            
  records.appendRow(write);  

  //Clear the cells for the next use.
  source.getRange('D2').clearContent();
  source.getRange('B3').clearContent();
  source.getRange('B3:D3').mergeAcross(); // this merges the cell to self heal potential user error.
  source.getRange('B5').clearContent();
  source.getRange('D5').clearContent();
  source.getRange('B7').clearContent();
  source.getRange('B7:F7').mergeAcross(); // this merges the cell to self heal potential user error.
  source.getRange('B9').clearContent();
  source.getRange('B9:F15').merge(); // this merges the cell to self heal potential user error.

The trouble is I have the data append to a new row

 records.appendrow(write)

However I want to be able to write over an existing row. For Example If the records sheet says:

A3          B3        C3
Student 1 , Grade 6 , Note Set  
Student 2 , Grade 8 , Note set
Student 3 , Grade 8 , Note set
A7          B7        C7

and I add a new note for student 2 then save. I want to see the same thing but instead it appends a row so it looks like this:

A3          B3        C3
Student 1 , Grade 6 , Note Set 
Student 2 , Grade 8 , Note set
Student 3 , Grade 8 , Note set
Student 2 , Grade 8 , Note set
A8          B8        C8

So you see I need to find a way to have the script do a vlookup and find student 2 so it can write the new data over that same row and I don't know how to make the script write to that row rather than appending a new one.

I hope that makes sense. I don't program often so I'm not sure I'm wording this properly, I have searched for solutions but I may be searching in the wrong areas do to my limited knowledge and experience. If this requires clarification please don't hesitate to ask.

Here is a link to a dummy sheet with the full script. https://docs.google.com/spreadsheets/d/1J2rCtSmt_BM6CozO4EBdlj1YL2wtoW4D4gNCsbalO5M/edit?usp=sharing

Answer 1

Switch the append row statement for the below, it will see if it can find the name in the first column of the Records sheet, overwrite if it does, and append otherwise.

var recordData = records.getRange(3, 1, records.getLastRow(), 1).getValues();
var recordPosition = recordData.map(function(row) {return row[0];}).indexOf(val[0][0]); 

if (recordPosition === -1) {
  records.appendRow(write);  
} else {
  records.getRange(3 + recordPosition, 1, 1, write.length).setValues([write]);
}

If you don't want to append at all you can simplify this to:

var recordPosition = records
    .getRange(3, 1, records.getLastRow(), 1) 
    .getValues()
    .map(function(row) {return row[0];})
    .indexOf(val[0][0]); 

records.getRange(3 + recordPosition, 1, 1, write.length).setValues([write]);