getRange fails on template function

Question

I'm writing a project management tool in Google Sheets. I have a template for a Project that gets copied and renamed every time someone starts a new project. I'm having a very peculiar bug with the following function.

Firstly, I apologize for the formatting below; No matter how I tried, I simply could not get one line in the middle to go into the code format, so the code is split in half with one weird line in the middle. I assure you, this is all one function that runs in Google Apps.

The error is occurring in the line just before the break: "empArchive,getRange(archiveCurrRow, 1, 1, 6).setValues(masterListTop.getRange(i,1,1,6).getValues()) //archive the project". When I run this for the first time in a new file, it stops at this line and displays an error, saying that "getRange" is not defined. I added some calls in the lines immediately preceding to check that getRange is, in fact defined for both masterListTop and empArchive and those calls work fine.

I tried commenting out the line in question, since the following line is nearly identical, and the script ran fine. I uncommented the line, and the script ran fine again. Yet, every time I create a new file from this template, I get this error. If I comment out and then uncomment the problem line, it runs. Obviously, that is not a practical work-around. Does anyone have any idea why this is happening?

Thank you in advance for your help.

 function onEdit(){
   var projectSheet = SpreadsheetApp.getActive().getSheets()[0];
   if((projectSheet.getRange(6,2).getValue()=="Completed")&&( projectSheet.getRange(9,9).getValue()!= "Completed")){     //find if project has been marked Completed
     var empList = SpreadsheetApp.openById("XXXXXXXXXXX").getSheets()[0];     //open list of employees
     var masterList = SpreadsheetApp.openById("XXXXXXXXXXX")     //open the master list
     var masterListTop = masterList.getSheets()[0]
     var i = 2;
     while(masterListTop.getRange(i,1).getValue() != projectSheet.getRange(1,2).getValue() && masterListTop.getRange(i,1).getValue() != "ZZZ"){     //find this project on the master list
       var currValue = masterListTop.getRange(i,1).getValue()
       i++
       }
     if(masterListTop.getRange(i,1).getValue() != "ZZZ"  && masterListTop.getRange(i,11).getValue() == "No"){     //if the project has not been archived, do the following
       var j = 2
       while(empList.getRange(j,3).getValue() != projectSheet.getRange(2,2).getValue()){     //find the project owner on the employee list
         j++
         }
       var empSheet = SpreadsheetApp.openByUrl(empList.getRange(j,5).getValue())     //open that employee's page
       var empArchive = empSheet.getSheets()[2];
       var empName = empArchive.getRange(1,1,1,1).getValue()
       var archiveCurrRow = empArchive.getLastRow()+1
       var projectName = masterListTop.getRange(i,1,1,1).getValue()
       empArchive,getRange(archiveCurrRow, 1, 1, 6).setValues(masterListTop.getRange(i,1,1,6).getValues())     //archive the project
 //above is the problem line
empArchive.getRange(archiveCurrRow,7,1,1).setValue(masterListTop.getRange(i,10,1,1).getValue())

       masterListTop.getRange(i,11).setValue("Yes")     //mark the project Archived
  }
     var m = 11
while(projectSheet.getRange(m,1).getValue() != ""){
  if(projectSheet.getRange(m,9).getValue() == "No"){     //find unarchived tasks in this project
     var n = 2
     while(empList.getRange(n,3).getValue() != projectSheet.getRange(m,2).getValue()){     //find the task owner on the employee list
       n++
       }
     empSheet = SpreadsheetApp.openByUrl(empList.getRange(n,5).getValue())     //open the task owner's page
     var empTaskArchive = empSheet.getSheets()[3]
     empTaskArchive.getRange(empTaskArchive.getLastRow()+1, 1, 1, 1).setValue(projectSheet.getRange(m,1).getValue())     //archive the task
     empTaskArchive.getRange(empTaskArchive.getLastRow(), 2, 1, 4).setValues(projectSheet.getRange(m, 3, 1, 4).getValues())
     empTaskArchive.getRange(empTaskArchive.getLastRow(), 6, 1, 1).setValue(masterListTop.getRange(i, 1).getValue())
     empTaskArchive.getRange(empTaskArchive.getLastRow(), 7, 1, 1).setValue(projectSheet.getRange(7, 2).getValue())
     empTaskArchive.getRange(empTaskArchive.getLastRow(), 8, 1, 1).setValue(masterListTop.getRange(i,10).getValue())
     projectSheet.getRange(m,9).setValue("Yes")     //mark the task Archived
       }
     m++
     }
   projectSheet.getRange(9,9).setValue("Completed")
   }
}

google-spreadsheet-api is not the same as the SpreadsheetApp service of Google Apps Script. To "fix" the line that was not shown in code formatting, using markdown, the indentation was removed. Other things that you could try is to use <code></code>. I think that this formatting problem should be reported on Meta Stack Overflow. — Rubén
Regarding the main problem, try to reproduce the problem using the minimal lines of code. For further details see minimal reproducible example. — Rubén
Not sure if this is source of error or copying error. But there is a comma here empArchive,getRange(archiveCurrRow, 1, 1, 6) in should be a "." after empArchive, like so empArchive.getRange(archiveCurrRow, 1, 1, 6) — Jack Brown
And you might want to consider using append to add a row to your document. — Casper
@JackBrown, that was the error! I can't believe I didn't see it. That's the trouble with working alone, I guess; sometimes you just need a second set of eyes. Thanks everyone for the help! — IAntoniazzi

Jack Brown Jack Brown · Accepted Answer · 2017-04-18T17:15:49

The error was caused due to a typo in the following code:

empArchive,getRange(archiveCurrRow, 1, 1, 6)

It there should be a dot(".") instead of a comma(","), like so

empArchive.getRange(archiveCurrRow, 1, 1, 6)

getRange fails on template function

1 Answers