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

Read best practices Even though your data isn't a contiguous range it is part of one so get the whole range with getValues() and use the appropriate indices to access the ones that you want. In the end if will be much faster. You may not want to use setValues to write the data because of other issues like messing up formulas. Avoid the use of setValue() and getValue() whenever possible

function submitStudentData() {
  const ss = SpreadsheetApp.getActive();
  const ssh = ss.getSheetByName('Students');
  const msh = ss.getSheetByName('Master');
  const nr = msh.getLastRow() + 1;
  const vs = ssh.getRange(nr, 1, ssh.getLastRow(), ssh.getLastColumn()).getValues();
  let oA1 = [[vs[0][25], vs[7][2], vs[5][2], vs[9][2], vs[9][4], vs[5][4], vs[7][4], vs[11][4]]];
  msh.getRange(msh.getLastRow() + 1, 1, oA1.length, oA[0].length).setValues(oA1);//This line replaces all of the below lines

  msh.getRange(nr, 1).setValue(vs[0][25]);//Concatenated Student Name
  msh.getRange(nr, 2).setValue(vs[7][2]); //Last Name
  msh.getRange(nr, 3).setValue(vs[5][2]); //First Name
  msh.getRange(nr, 4).setValue(vs[9][2]); //Goes By
  msh.getRange(nr, 5).setValue(vs[9][4]); //Student ID
  msh.getRange(nr, 6).setValue(vs[5][4]); //DOB
  msh.getRange(nr, 7).setValue(vs[7][4]); //Grade
  msh.getRange(nr, 8).setValue(vs[11][4]); //Last Annual Date[enter image description here][1]

You could also do a similar thing by using formulas to map all of the data into a single line or column making it much easier to run the scripts.

Answer 2

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
}

Answer 3

As Cooper said you want to avoid reading and writing to the sheet(s) as much as possible. (I had the same issue when I started with Google Script)

This means that you should read the whole range into a variable and then write your rows out to the master sheet.

Below is an example of what you could use to avoid the setValue() and getValue() slowness you are experiencing

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; //You will not need this


//First we will all the data from the students sheet. This will make and array of arrays [[row],[row],[row]]. 
studentData = enterStudents.getRange(1,1,enterStudents.getLastRow(),enterStudents.getLastColumn()).getValues()
Logger.log(studentData)
//We are going to build an array of arrays of the data that we want to write back to the master sheet. We will start by creating our first array
writeData = []
//Then we loop through all the student data
for (var i = 0; i < studentData.length; i++) { 
  Logger.log(studentData[i][0])
  //We are selecting data from each row to add to our array. in "studentData[i][0]" the [0] is the column number (remember we are starting with 0)
  rowData = []
  rowData.push(studentData[i][0])
  rowData.push(studentData[i][2])
  rowData.push(studentData[i][1])
  //Then we send the full row to the first array we made
  writeData.push(rowData)

}


Logger.log(writeData)
// Now to write out the data. Normally it would not be a good idea to loop a write like this but this as an atomic operation that google will automatically batch write to the sheet. 
for (var i = 0; i < writeData.length; i++) { 
  masterSheet.appendRow(writeData[i])
}

}

Hope this helps get you started.