TypeError: Cannot read property 'getValues' of undefined

I’m struggling to automate a time off request sheet to google calendar. When I hit run this is one of my code errors:

function checkRowData(currRowRange, ss, ui) {
  let data = {};
  
  const row = currRowRange.getValues()[0];   
  // Check if ANY of the required inputs is empty. If empty, return false
  if (row[0] === "" || row[1] === "" || row[2] === "" || row[3] === "" || row[7] === "" || row[8] === "") {
    return false;
  }

What is the exact error message?

Also, what is the function supposed to return if it does not return false based on the conditions of the if statement.

In general, we need a lot more context here. What data type is currRowRange? Can you show an example of what gets passed to this parameter?

Hi, this might be a long shot but I think it is worth trying.

It seems that you are trying to access something that is not defined or doesn’t make sense.
For example, when you get the row, does it make sense to use the [0]? I mean, is the return indexed?

Full error message:
11:34:30 AM

Notice

Execution started

11:34:30 AM

Error

TypeError: Cannot read property ‘range’ of undefined

onEditTrigger

@ Code.gs:3

We really need to see your full code to give more advice. Also, you did not answer all the questions I asked.

1 Like
function onEditTrigger(e) {
  // Get current sheet
  const sheet = e.range.getSheet();

  Logger.log(sheet)

  // Exit if not on the "Time off tracker" sheet
  if (sheet.getName() !== "Time off tracker") {
    return;
  } else {
    // Get the current cell's entire row
    const currRow = e.range.getRow();
    const lastColumn = sheet.getLastColumn();
    const currRowRange = sheet.getRange(currRow, 1, 1, lastColumn)

    // Get current spreadsheet (for toast messages)
    const ss = sheet.getParent();
    const ui = SpreadsheetApp.getUi();

    // Check if all the data is filled in and filled in correctly (i.e. date is proper date format)
    const completeForm = checkRowData(currRowRange, ss, ui);

    // If all the data is filled in correctly, send one of 3 different types of emails
    if (completeForm || completeForm?.length === 0) {
      sendRowEmail(completeForm, currRowRange, e, ss, ui);
    }
  }
}

function checkRowData(currRowRange, ss, ui) {
  let data = {};

  const row = currRowRange.getValues()[0];

  // Check if ANY of the required inputs is empty. If empty, return false
  if (row[0] === "" || row[1] === "" || row[2] === "" || row[3] === "" || row[7] === "" || row[8] === "") {
    return false;
  } else {
    data.requesterName = {
      val: row[0],
      index: 1
    };
    data.requesterEmail = {
      val: row[1],
      index: 2
    };
    data.startDate = {
      val: row[2].toDateString(),
      index: 3
    };
    data.endDate = {
      val: row[3].toDateString(),
      index: 4
    };
    data.leaderName = {
      val: row[7],
      index: 8
    };
    data.leaderEmail = {
      val: row[8],
      index: 9
    };
    data.leaderApproved = {
      val: row[9],
      index: 10
    };
    data.requesterConfirmEmailSent = {
      val: row[12],
      index: 13
    };
    data.leaderEmailSent = {
      val: row[13],
      index: 14
    };
    data.requesterEmailSent = {
      val: row[14],
      index: 15
    };
  }
  return data;
}

function sendRowEmail(data, currRowRange, e, ss, ui) {

  const {
    requesterName,
    requesterEmail,
    startDate,
    endDate,
    leaderName,
    leaderEmail,
    leaderApproved,
    requesterConfirmEmailSent,
    leaderEmailSent,
    requesterEmailSent,
  } = data;

  /* Each row can be in 1 of 4 states (and each state is implemented below this comment block):
      State 1: Form was just filled in properly, and no email has been sent. ReqEmailSent and LdrEmailSent are both empty.
          Action 1: Send an email to the requester who filled in the sheet, confirming to them that the request was properly filled in.
          Action 2: Send an email to the leader, informing them that a request has been filled and they need to approve it.
          Action 3: Mark column "LdrEmailSent" as TRUE
    
      State 2: LdrEmailSent is TRUE, ReqEmailSent is empty, and leader has not approved yet ("Leader Approved" is empty)
          No action required - This request is still waiting leader approval.
    
      State 3: Leader has approved by marking "Leader Approved" to TRUE. LdrEmailSent is TRUE, ReqEmailSent is empty.
          Action 1: Make a note on the checkmark to mark who it was that pressed the checkbox.
          Action 2: Send an email to the requester, informing them that their request was approved and they need to fill it in the team calendar
          Action 3: Mark column "ReqEmailSent" as TRUE
      
      State 4: Both ReqEmailSent and LdrEmailSent are TRUE
          No action required - This request is completed.
  */

  // State 4
  if (requesterEmailSent.val && leaderEmailSent.val) {
    return;
  }
  // State 2
  else if (leaderEmailSent.val && leaderApproved.val == "") {
    return;
  }
  // State 1
  else if (requesterEmailSent.val == "" && leaderEmailSent.val == "") {
    ui.alert('Thank you for filling in the form! You will receive a confirmation email shortly.');

    sendLeaderEmail(leaderEmail.val, requesterName.val, startDate.val, endDate.val);
    const leaderEmailSentRange = currRowRange.getCell(1, leaderEmailSent.index);
    leaderEmailSentRange.setValue(true);

    sendRequesterConfirmEmail(requesterEmail.val);
    const requesterEmailConfirmSentRange = currRowRange.getCell(1, requesterConfirmEmailSent.index);
    requesterEmailConfirmSentRange.setValue(true);
  }
  // State 3
  else if (leaderEmailSent.val && leaderApproved.val && requesterEmailSent.val == "") {
    ui.alert('Thank you for approving the request! The requester will receive an email shortly.');
    const leaderApprovedRange = currRowRange.getCell(1, leaderApproved.index);
    leaderApprovedRange.setNote(" Approved by " + e.user.getEmail() + " on " + new Date());

    sendRequesterEmail(requesterEmail.val);
    const requesterEmailSentRange = currRowRange.getCell(1, requesterEmailSent.index);
    requesterEmailSentRange.setValue(true);
  }
}

function sendRequesterConfirmEmail(requesterEmail) {
  //insert the team calendar email within the quotation marks in href
  const email = {
    to: requesterEmail,
    subject: `Your vacation request was received`,
    htmlBody: `This email is to confirm that your vacation request was received. An email has been sent to your leader to approve your request.<br />Once your leader confirms your request, you'll receive a second confirmation email.<br /><br />Thank you.`,
  }
  MailApp.sendEmail(email);
}

function sendRequesterEmail(requesterEmail) {
  //TODO: Insert the team calendar email within the quotation marks in href
  const email = {
    to: requesterEmail,
    subject: `Your vacation request was approved!`,
    htmlBody: `This email is to confirm that your leader has approved your vacation request. <br /><br />Thank you.`,
  }
  MailApp.sendEmail(email);
}

function sendLeaderEmail(leaderEmail, requesterName, startDate, endDate) {
  const email = {
    to: leaderEmail,
    subject: `Team member submitted vacation request [${requesterName}]`,
    htmlBody: `${requesterName} submitted a vacation request for the dates of ${startDate} to ${endDate}. <br />Please review the request on the <a href="link">Request Tracker</a> and approve the vacation.<br /><br />Thank you.`,
  }
  MailApp.sendEmail(email);
}

function createCalendarEvent() {
  let communityCalendar = CalendarApp.getCalendarById("calendar ID");
  let sheet = SpreadsheetApp.getActiveSheet();

  let schedule = sheet.getDataRange().getValues();
  schedule.splice(0);

  schedule.forEach(function(entry) {
    communityCalendar.createEvent(entry[0], entry[2], entry[3]);
  });
}

Thanks for posting. Can you either share a redacted Google sheet containing a sample of the data or at last show a sampling of the sheet (redacted of course). We can then test out a few things on our own Google sheets.

This line appears to be the source of the error. Thre is no range property on e. What should e be? Currently, it has the value undefined. What calls onEditTrigger? Should it be onEdit instead or is onEditTrigger a custom trigger?

@elizabeth.lakegillis
I had never used Google’s script feature. I learned something new today.

You can not just click Run and the script will not work as expected. The function should actually be named onEdit and not oneEditTrigger. If named correctly it will work any cell a sheet is edited. I commented out:

if (completeForm || completeForm?.length === 0) {
  sendRowEmail(completeForm, currRowRange, e, ss, ui);
}

as I did not want to be sending emails. The rest of the script works though during my tests of changing certain fields and not others.

I refactored the checkRowData to make it more readable and easier to make changes in case columns of the sheet are added or deleted. I would normally changed the property index to column since that is technically what it represents, but I did not want to rewrite the other functions that reference this property.

  function checkRowData(currRowRange, ss, ui) {
    const fields = [{
        name: 'requesterName',
        index: 1,
        required: true
      },
      {
        name: 'requesterEmail',
        index: 2,
        required: true
      },
      {
        name: 'startDate',
        index: 3,
        isDate: true,
        required: true
      },
      {
        name: 'endDate',
        index: 4,
        isDate: true,
        required: true
      },
      {
        name: 'leaderName',
        index: 8,
        required: true
      },
      {
        name: 'leaderEmail',
        index: 9,
        required: true
      },
      {
        name: 'leaderApproved',
        index: 10
      },
      {
        name: 'requesterConfirmEmailSent',
        index: 13
      },
      {
        name: 'leaderEmailSent',
        index: 14
      },
      {
        name: 'requesterEmailSent',
        index: 15
      }
    ];

    function hasEmptyRequiredField(fields, row) {
      return fields.some(function({
        required,
        index
      }) {
        if (required && row[index - 1] === '') {
          return true;
        }
        return false;
      });
    }

    const row = currRowRange.getValues()[0];

    if (hasEmptyRequiredField(fields, row)) {
      return false;
    } else {
      const data = fields.reduce(function(fieldsObj, {
        name,
        index,
        isDate
      }) {
        let val = row[index - 1];
        if (isDate) {
          val = val.toDateString();
        }
        fieldsObj[name] = {
          val,
          index
        };
        return fieldsObj;
      }, {});

      return data;
    }
  }

This is really helpful - thank you. I’m going to review the changes with my coach today and will respond with the outcome later today!! thank you