google-apps-scriptgoogle-sheetssetvaluedata-entry

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


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


Solution

  • 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
    }