Cannot call method "getRange" of null. (line 20, file "Code")

Question

TypeError: Cannot call method "getRange" of null. (line 20, file "Code") My script is running as I want it to, but I continue to get the above error. The script has a trigger to run when a form is completed it runs moves the data, then then returns the above error. Here is the sheet

Line 20 is

    var data = sourceSpreadSheetSheetID.getRange(2, 1,sourceSpreadSheetSheetID.getLastRow()-1, sourceSpreadSheetSheetID.getLastColumn()).getValues();

I have checked sheet names and made adjustments, but seem to be missing something.

    function CopyDataToNewFile() {

// How Many Columns over to copy
var columsCopyCount = 4; // A=1 B=2 C=3 ....

// What Column to Monitor
var columnsToMonitor = 3; // A=1 B=2 C=3 ....MONITORS In or Out

//TARGET SPREAD SHEETS
var HallPass = "Hall_Pass"
var CheckOut = "Out";
var CheckIn = "In";

//SOURCE SPREAD SHEET
var ss = SpreadsheetApp.openById('1B93n7wQ8rosmcaqlpgD9FoL1KWfbXALrror7t6r5K2M'); //Need ID from Form response sheet
var sourceSpreadSheetSheetID = ss.getSheetByName(HallPass);
var sourceSpreadSheetSheetID1 = ss.getSheetByName(CheckOut);
var sourceSpreadSheetSheetID2 = ss.getSheetByName(CheckIn);

var data = sourceSpreadSheetSheetID.getRange(2, 1,sourceSpreadSheetSheetID.getLastRow()-1, sourceSpreadSheetSheetID.getLastColumn()).getValues();

var Out = [];
var In = [];


for (var i = 0; i < data.length; i++) {

    var rValue = data[i][2];

    if (rValue == CheckOut) {
        Out.push(data[i]);
    } else if (rValue == CheckIn) {
        In.push(data[i]);

    }
}

if(Out.length > 0){
  sourceSpreadSheetSheetID1.getRange(sourceSpreadSheetSheetID1.getLastRow() + 1, 1, Out.length, Out[0].length).setValues(Out);
  sourceSpreadSheetSheetID.deleteRows(2,sourceSpreadSheetSheetID.getLastRow() - 1);
}

if(In.length > 0){
   sourceSpreadSheetSheetID2.getRange(sourceSpreadSheetSheetID2.getLastRow() + 1, 1, In.length, In[0].length).setValues(In);
   sourceSpreadSheetSheetID.deleteRows(2,sourceSpreadSheetSheetID.getLastRow() -1);
}}

I would like this script to run without errors

Chris · Accepted Answer · 2019-02-11 16:26:52Z

1

So the code looks like it should run, as the error you are getting is about sourceSpreadSheetSheetID being a null value, so when I checked the spreadsheet you posted I noticed that the sheet you refer to as "HallPass" is actually called "Hall_Pass". In your code, instead of passing in the variable HallPass you are giving it a string "HallPass". This is causing the issue.

var ss = SpreadsheetApp.openById('1B93n7wQ8rosmcaqlpgD9FoL1KWfbXALrror7t6r5K2M'); //Need ID from Form response sheet
var sourceSpreadSheetSheetID = ss.getSheetByName(HallPass); //Sheet name was incorrect.
// Did you mean to leave out the Quotations to refer to the variable above?
var sourceSpreadSheetSheetID1 = ss.getSheetByName(CheckOut);
var sourceSpreadSheetSheetID2 = ss.getSheetByName(CheckIn);

answered Feb 11, 2019 at 16:26

Chris

2,1071 gold badge15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jeremy Hunter Over a year ago

I believe that I made the recommended changes correctly, but I am still getting the same error. I have made changes to original post to reflect the changes.

Chris Over a year ago

@JeremyHunter That is weird. Are you sure it's the same error and not referring to a different line?

Jeremy Hunter Over a year ago

This is the error sent to me. 2/11/19 10:00 AM CopyDataToNewFile The coordinates or dimensions of the range are invalid. (line 19, file "Code") formSubmit 2/11/19 10:00 AM

Chris Over a year ago

@JeremyHunter That is a different error about the same line. What does the sheet setup look like? The error is pretty clear here.

Jeremy Hunter Over a year ago

thank you was not paying attention saw the same line made the assumption. I have not changed anything with sheet not sure what would cause this error.

Collectives™ on Stack Overflow

Cannot call method "getRange" of null. (line 20, file "Code")

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related