Jul 9, 2020
Getting the following TypeError: Cannot read property 'getDataRange' of null (line 154, file "Code")
// 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");
};
Community content may not be verified or up-to-date. Learn more.
All Replies (2)