Jul 9, 2020

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

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");
};
Locked
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Recommended Answer
Jul 12, 2020
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.
 
 
If you need more help, please share a sample spreadsheet.

Cheers --Hyde
 
 
Last edited Jul 12, 2020
Original Poster Rachael Thomas 6809 marked this as an answer
Helpful?
Recommended Answer
Jul 13, 2020
Thank you!
Original Poster Rachael Thomas 6809 marked this as an answer
Helpful?
All Replies (2)
Recommended Answer
Jul 12, 2020
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.
 
 
If you need more help, please share a sample spreadsheet.

Cheers --Hyde
 
 
Last edited Jul 12, 2020
Original Poster Rachael Thomas 6809 marked this as an answer
Recommended Answer
Jul 13, 2020
Thank you!
Original Poster Rachael Thomas 6809 marked this as an answer
false
16997793278818625566
true
Search Help Center
true
true
true
true
true
35
false
Search
Clear search
Close search
Main menu