Let's walk through what's broken in your script and fix it step by step—your core goal (send an email when a Form submission's E column value is under 3) is totally doable, we just need to fix a few key mistakes.

First, let's break down the error and key issues: #

1. Wrong sheet access method
   The error Cannot call method "getSheetName" of null pops up because you're using getSheetName() incorrectly—this method returns the name of a sheet, not the sheet object itself. You need getSheetByName('sheetname') to grab the actual sheet. Also, getActiveSpreadsheet() can return null if your script isn't properly bound to a spreadsheet, so we'll add checks for that.

2. Invalid value retrieval
   getLastRow().getValue() doesn't work because getLastRow() gives you a number (the row number), not a range. You need to use getRange() to target the specific cell in that row.

3. Incorrect event object usage
   In Google Form submit triggers, e.target isn't a valid property—you can't use it to grab form values. Instead, we'll either pull the value directly from the form response, or fetch the latest row from the linked spreadsheet (since you mentioned E column).

4. Unnecessary loop
   Your for (var keys in sheet) loop doesn't make sense here—sheet is a Sheet object, not an iterable list of values. We don't need a loop to check the single value we care about.

Fixed Script Option 1: Grab value directly from the Form response #

This is the cleaner approach if you don't need to rely on the spreadsheet data:

function SendGoogleForm(e) {
  // Get all responses from the submitted form
  const itemResponses = e.response.getItemResponses();
  
  // Target the 5th question (matches E column in your sheet)
  // Note: Indexes start at 0, so 4 = 5th item
  const targetResponse = itemResponses[4].getResponse();
  const targetValue = parseFloat(targetResponse);
  
  // Check if the value is less than 3 and send email
  if (targetValue < 3) {
    const email = "email@email.com";
    const subject = "Needs action";
    const message = "Hi, take action!";
    
    MailApp.sendEmail(email, subject, message);
  }
}

• Adjust the index 4 if your target question isn't the 5th one in the Form.

Fixed Script Option 2: Grab value from the linked spreadsheet #

Use this if you need to pull directly from the spreadsheet's E column:

function SendGoogleForm(e) {
  try {
    // Get the bound spreadsheet (make sure your script is linked to it!)
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    if (!spreadsheet) {
      throw new Error("No active spreadsheet found—ensure the script is bound to your Form's linked sheet.");
    }
    
    // Get your target sheet by name (replace 'sheetname' with your actual sheet name)
    const sheet = spreadsheet.getSheetByName('sheetname');
    if (!sheet) {
      throw new Error(`Sheet 'sheetname' not found—double-check the spelling.`);
    }
    
    // Get the value from E column (column 5) of the last submitted row
    const lastRow = sheet.getLastRow();
    const targetValue = parseFloat(sheet.getRange(lastRow, 5).getValue());
    
    // Send email if value is under 3
    if (targetValue < 3) {
      const email = "email@email.com";
      const subject = "Needs action";
      const message = "Hi, take action!";
      
      MailApp.sendEmail(email, subject, message);
    }
  } catch (error) {
    // Log errors for debugging
    Logger.log(`Script error: ${error.message}`);
    // Optional: Send yourself an error alert
    // MailApp.sendEmail("your-email@example.com", "Form Script Error", error.message);
  }
}

• Added error handling to catch missing spreadsheets/sheets, which makes debugging way easier.

Don't forget the trigger! #

For this to work, you need to set up an On form submit trigger:

1. Open your script editor
2. Click the clock icon (Triggers) in the left sidebar
3. Click "Add trigger"
4. Choose SendGoogleForm as the function, set event type to "On form submit", then save.

内容的提问来源于stack exchange，提问作者Artem