CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jeremy Hunter
Jeremy Hunter

Reputation: 13

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

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

Upvotes: 0

Views: 219

Answers (1)

Chris
Chris

Reputation: 2107

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);

Upvotes: 1

Related Questions