Better/faster way to pass 50+ values from one Google sheet to another

Question

I'm brand new to App Script, so please forgive my ignorance.

The Google sheet I use to hold student data is so long and unwieldy (50+ columns) that I decided to create another sheet to act as a front-end for data entry. Through hours of tutorial videos + bumbling trial and error, I've come up with a working script that takes values from my data entry form-like sheet ('Students') and passes those values to the first empty row in my destination/container sheet ('Master').

I'm really pleased with how the script working - except for the fact that it is ridiculously slow. Based on what I've read, I think I'm making too many calls to the Sheets API, and I need to figure out how to pass all the values from 'Students' to 'Master' en masse rather than one-by-one, but I don't have the skills to do that, and I can't seem to find an example.

I'm sure there's a really simple, elegant solution. Can anyone help?

Here's a little piece of my code (hopefully it's enough to see the inefficient strategy I'm using):

function submitStudentData(){
var caseloadManager = SpreadsheetApp.getActiveSpreadsheet();
var enterStudents = caseloadManager.getSheetByName('Students');
var masterSheet = caseloadManager.getSheetByName('Master');
var clearFields = enterStudents.getRangeList(['C6:C18', 'C22', 'E6:E18','G6:G14','G20','I6:I14','K6:K16', 'M6:M18']);
var blankRow = masterSheet.getLastRow()+1;

  masterSheet.getRange(blankRow,1).setValue(enterStudents.getRange("Z1").getValue()); //Concatenated Student Name
  masterSheet.getRange(blankRow,3).setValue(enterStudents.getRange("C6").getValue()); //First Name
  masterSheet.getRange(blankRow,2).setValue(enterStudents.getRange("C8").getValue()); //Last Name
  masterSheet.getRange(blankRow,4).setValue(enterStudents.getRange("C10").getValue()); //Goes By
  masterSheet.getRange(blankRow,6).setValue(enterStudents.getRange("E6").getValue()); //DOB
  masterSheet.getRange(blankRow,7).setValue(enterStudents.getRange("E8").getValue()); //Grade
  masterSheet.getRange(blankRow,5).setValue(enterStudents.getRange("E10").getValue()); //Student ID
  masterSheet.getRange(blankRow,10).setValue(enterStudents.getRange("E14").getValue()); //Last FIE
  masterSheet.getRange(blankRow,11).setValue(enterStudents.getRange("Z2").getValue()); //Calculated FIE Due Date
  masterSheet.getRange(blankRow,8).setValue(enterStudents.getRange("E12").getValue()); //Last Annual Date[enter image description here][1]
  masterSheet.getRange(blankRow,13).setValue(enterStudents.getRange("G6").getValue()); //PD
  masterSheet.getRange(blankRow,14).setValue(enterStudents.getRange("G8").getValue()); //SD
  masterSheet.getRange(blankRow,15).setValue(enterStudents.getRange("G10").getValue()); //TD
  masterSheet.getRange(blankRow,16).setValue(enterStudents.getRange("G3").getValue()); //Concatenated Disabilities
  masterSheet.getRange(blankRow,18).setValue(enterStudents.getRange("G12").getValue()); //Program Type
  masterSheet.getRange(blankRow,12).setValue(enterStudents.getRange("G14").getValue()); //Evaluation Status
  masterSheet.getRange(blankRow,20).setValue(enterStudents.getRange("I6").getValue()); //DYS
  masterSheet.getRange(blankRow,21).setValue(enterStudents.getRange("I8").getValue()); //GT
   masterSheet.getRange(blankRow,19).setValue(enterStudents.getRange("I10").getValue()); //EB
  masterSheet.getRange(blankRow,24).setValue(enterStudents.getRange("I12").getValue()); //ESY
  masterSheet.getRange(blankRow,22).setValue(enterStudents.getRange("I14").getValue()); //BIP
  masterSheet.getRange(blankRow,29).setValue(enterStudents.getRange("K6").getValue()); //TR
  masterSheet.getRange(blankRow,30).setValue(enterStudents.getRange("K8").getValue()); //OT

It goes on and one like this for 52 values before clearing all the fields in 'Students.' It works, but it takes well over a minute to run.

I'm trying to attach a picture of my 'Students' form-like sheet in case my description isn't clear.

Thanks so much for helping a humble special educator who knows not what she's doing. :)

Image of 'Students' form/sheet

Answer 1

Here is the working example. Just complete the mapping array as desrbied in the code. The runtime is below 1 second.

const mapping= [
  // enter the array [ sourceRange, destinationRow ] for each cell you want to copy form Students to Master
  ['Z1',1],
  ['C6',3],
  ['C8',2],
  ['C10',4],
  ['E6',6]
  // ... and so on
]

function submitStudentData() {
  console.time('submitStudentData')
  const caseloadManager = SpreadsheetApp.getActive();
  const enterStudents = caseloadManager.getSheetByName('Students');
  const masterSheet = caseloadManager.getSheetByName('Master');
  const data = enterStudents.getDataRange().getValues()
  const destRow = []

  mapping.forEach((m,i)=>{
    [rowi,coli] = rangeToRCindex(m[0])
    const destRowIndex = m[1] - 1
    destRow[destRowIndex] = data[rowi][coli]
  })

  masterSheet.appendRow(destRow)
  console.timeEnd('submitStudentData')
}

function rangeToRCindex(range){
  const match = range.match(/^([A-Z]+)(\d+)$/)
  if (!match) {
    throw new Error(`invalid range ${range}`)
  }
  const col = letterToColumn(match[1])
  const row = match[2]
  return [row-1,col-1]
}

function letterToColumn(columnLetters) {
  let cl = columnLetters.toUpperCase()
  let col = 0
  for (let i = 0; i < cl.length; i++) {
    col *= 26
    col += cl.charCodeAt(i) - 65 + 1
  }
  return col
}