Getting the following TypeError: Cannot read property 'getDataRange' of null (line 154, file "Code")

Question

I am getting the following error "cannot read property 'getDataRange' of null (line 154, file "Code"). Below is the entire script. The functions that are not working are function setInSheet and function setInSubSheetWithTime.

// Copies entries from the "Contact Us Form Responses" sheet to "Contact Us New Requests" sheet

function processFormResponse(){

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = ss.getSheetByName("Contact Us Form Responses");

var r = s.getDataRange();

var v = r.getValues();

var values;

var next_sheet = "";

var request_type = "";

var record_copied = "";

var error_occurred = false;

Logger.log("Found: " + v.length + " records in Contact Us Form Responses");

for(var i=v.length-1; i>=1; i--)

{

// 2nd column of that row should be the request type

// 9th column is if the record was copied to a new sheet (based upon column A=0)

request_type = v[i][0];

record_copied = v[i][10];

next_sheet = "";

error_occurred = false;

values=s.getRange(i+1, 1, 1, 13).getValues(); //replace with v[i]??

if(request_type == "")

{

next_sheet = "Contact Us New Requests";

}

else if(request_type != "")

{

next_sheet = "Contact Us New Requests";

}

// step 3 - copy data

if(error_occurred == false && record_copied == "")

{

try

{

setInSheet(next_sheet, values);

}

catch(e)

{

Logger.log("Failed to copy data to next sheet: " + e.message);

error_occurred = true;

}

// step 4 - set copied data to yes

if(error_occurred == false && record_copied == "")

{

try

{

// 10th column indicates the record was copied to a new sheet (based upon column A=1)

s.getRange(i+1, 11).setValue("yes");

}

catch(e)

{

Logger.log("Failed to set record copied to 'yes': " + e.message);

error_occurred = true;

}

// step 5 - remove record

if(error_occurred == false)

{

try

{

//s.deleteRow(i+1);

}

catch(e)

{

// nothing to do here as its ok if this errors - will remove it next time

Logger.log("Failed to delete record: " + e.message);

}

};

//checks to see if the edit completes a new request then

function moveToRespectiveSheet(e){

//var ss = SpreadsheetApp.openById("221160555");

//var s = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[1]);

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = ss.getActiveSheet();

var sheet_name = s.getName();

var ac = s.getActiveCell();

var col = ac.getColumn();

var row = ac.getRow();

var next_sheet = ""; //sheet to move it to

var max_col = col; //the maximum rightmost column to copy data from

var values;

var error_occurred = false;

//we have to ignore the header row - just in case someone tweaks that

if(row > 1)

{

if(sheet_name == "Contact Us New Requests" && col == 10 && ac.getValue() == "Completed")

{

next_sheet = "Contact Us Completed Requests";

}

max_col = max_col + 1; //progress timestamp is after status

}

if(next_sheet != "")

{

//theoretically try/catch shouldn't need to be done conditionally like this

//but I was able to throw an error and watch it continue so...

//also not sure how logger will work if its running on a client..???

if(error_occurred == false)

{

try

{

values = s.getRange(row, 1, 1, max_col).getValues();

}

catch(e)

{

Logger.log("Failed to get values for move: " + e.message);

error_occurred = true;

}

if(error_occurred == false)

{

try

{

setInSubSheetWithTime(next_sheet, values);

}

catch(e)

{

Logger.log("Failed to copy to new sheet for move: " + e.message);

error_occurred = true;

}

if(error_occurred == false)

{

try

{

s.deleteRow(row);

}

catch(e)

{

Logger.log("Failed to delete row after move: " + e.message);

error_occurred = true;

}

};

function setInSheet(sheetname, values){

//var ss = SpreadsheetApp.openById("221160555");

//var s = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[1]);

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = ss.getSheetByName(sheetname);

var r = s.getDataRange();

var v = r.getValues();

//finds first row with a blank date - then breaks leaving the current row as the one to enter the new data into... in theory

//probably a faster way to do this like i = v.length+1 or something?

for(var i=1; i<v.length; i++)

{

if(v[i][0]=="")

{

break;

}

s.getRange(i+1,1,1,values[0].length).setValues(values);

};

function setInSubSheetWithTime(sheetname, values){

//var ss = SpreadsheetApp.openById("221160555");

//var s = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[1]);

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = ss.getSheetByName(sheetname);

var r = s.getDataRange();

var v = r.getValues();

//finds first row with a blank date - then breaks leaving the current row as the one to enter the new data into... in theory

//probably a faster way to do this like i = v.length+1 or something?

for(var i=1; i<v.length; i++)

{

if(v[i][0]=="")

{

break;

}

s.getRange(i+1,1,1,values[0].length).setValues(values);

s.getRange(i+1,values[0].length+1).setValue(date());

};

function date() {

return Utilities.formatDate(new Date(), "GMT-0700", "MM/dd/yyyy HH:mm:ss");

};

--Hyde · Accepted Answer

cannot read property 'getDataRange' of null
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName(sheetname);
  var r = s.getDataRange();
 
Hi Rachael,
 
The error message indicates that the value of the variable s is null, which in turn indicates that there is no sheet by the name contained in sheetname in the active spreadsheet.
 
Make sure that the sheet names used by your code match the actual names of the sheets, including leading and trailing spaces and exact letter case. Use console.log or Logger.log to track the contents of the relevant variables in the parts of the code that set and use sheet names.
 
To learn scripting, go to the Beginner's Guide, the Extending Google Sheets page, javascript.info and Mozilla Developer Network.
 
If you need more help, please share a sample spreadsheet.
Cheers --Hyde

Rachael Thomas 6809 · Accepted Answer

Thank you!