TypeError: Cannot read property 'getValues' of undefined

[elizabeth.lakegillis]

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;
  }

[txt88]

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?

[elizabeth.lakegillis]

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

[elizabeth.lakegillis]

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]);
  });
}

[elizabeth.lakegillis]

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