Copied original script to modify and now getting "TypeError: Cannot read properties of null (reading 'getRange')" error

899 views
Skip to first unread message

Alysha McPeak

unread,
Oct 27, 2023, 1:55:21 PM10/27/23
to Google Apps Script Community
We currently use a google form to submit asset tag files for manufacturing shipments, and I am modifying the google form to include two new prompts: Qty and the SKU of the product.

I am making a new google form and copied the original (working) script, modified the rows to what rows I have, and now I am getting the following error:

TypeError: Cannot read properties of null (reading 'getRange') sendEmails2 @ Code.gs:13

My range is row 1 to 11 which is correct, so not sure what is wrong! It is saying that line 13 is where the error is coming from.

Script:

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';

/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3200; // First row of data to process aka START HERE
  var numRows = 500; // Number of rows to process
  // Fetch the range of cells A2:J11
  var dataRange = sheet.getRange(startRow, 1, numRows, 11);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var recipientsTO = row[1];
    // var recipientsCC = row[11];
    var recipientsBCC = "";
   
    var subject = "FB Asset Report - SO#"+ " " + row[2] + " - " + row[4];
    var message = "<BR>" + "SO:"+ " " + row[2] + " <br> " + "Status: " + "<b>" + row[4] + "</b>" + " <br><br> " + "Comments: " + " <span style='background-color:yellow';><b> " + row[6] + row[7] + " </b></span> " + " <BR> " + "OTS date: " + row[3] + "<BR><BR>" + "Thank you!"; // Second column
    var emailSent = row[9];
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
     
      var requester = row[1]
      var allEmails = getAllEmails(requester)
     
     
      MailApp.sendEmail({
       to: allEmails,
       subject: subject,
       htmlBody: message});
     
     
      sheet.getRange(startRow + i, 9).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
 }
}


function getAllEmails(requester) {
 
  var extraEmailMap = {
   
   
   
   
   
   
  }
 
 
  var extraEmails = extraEmailMap[requester]
 
  var allEmails
  if (extraEmails){
    allEmails = extraEmails.concat(requester)
  } else {
    allEmails = [requester]
  }
 
  return allEmails.join(",")
}


function sendFormByEmail(e)
{    


  var txt = "";
  for(var field in e.namedValues) {
    txt += field + ': ' + e.namedValues[field].toString() + "\n\n";    
  }

  MailApp.sendEmail(email, txt , txt);

 
}


// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';

/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3200; // First row of data to process aka START HERE
  var numRows = 500; // Number of rows to process
  // Fetch the range of cells A2:J11
  var dataRange = sheet.getRange(startRow, 1, numRows, 11);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var recipientsTO = row[1];
    // var recipientsCC = row[11];
    var recipientsBCC = "";
   
    var subject = "FB Asset Report - SO#"+ " " + row[2] + " - " + row[4];
    var message = "<BR>" + "SO:"+ " " + row[2] + " <br> " + "Status: " + "<b>" + row[4] + "</b>" + " <br><br> " + "Comments: " + " <span style='background-color:yellow';><b> " + row[6] + row[7] + " </b></span> " + " <BR> " + "OTS date: " + row[3] + "<BR><BR>" + "Thank you!"; // Second column
    var emailSent = row[9];
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
     
      var requester = row[1]
      var allEmails = getAllEmails(requester)
     
     
      MailApp.sendEmail({
       to: allEmails,
       subject: subject,
       htmlBody: message});
     
     
      sheet.getRange(startRow + i, 9).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
 }
}


function getAllEmails(requester) {
 
  var extraEmailMap = {
   
   
   
   
   
   
  }
 
 
  var extraEmails = extraEmailMap[requester]
 
  var allEmails
  if (extraEmails){
    allEmails = extraEmails.concat(requester)
  } else {
    allEmails = [requester]
  }
 
  return allEmails.join(",")
}


function sendFormByEmail(e)
{    


  var txt = "";
  for(var field in e.namedValues) {
    txt += field + ': ' + e.namedValues[field].toString() + "\n\n";    
  }

  MailApp.sendEmail(email, txt , txt);

 
}


Freelancer Techworld

unread,
Oct 28, 2023, 3:02:32 PM10/28/23
to Google Apps Script Community
The problem is here:
---
  var startRow = 3200; // First row of data to process aka START HERE
  var numRows = 500; // Number of rows to process
  // Fetch the range of cells A2:J11
  var dataRange = sheet.getRange(startRow, 1, numRows, 11);
---
Why is the startRow 3200? and why is the numRows 500? These code lines return the range of A3200:K3700

The getRange method should be used as getRange(row, column, numRows, numColumns)

What is the data range you would like to get?

Reply all
Reply to author
Forward
0 new messages