Multiple onEdit functions in one

Question

I have a Google spreadsheet that I've been working on. The attached link is a test sheet I work with before moving the script over to my sheet. Here's m problem, I don't fully understand the onEdit function. I'm wanting to include about five onEdit functions into one. However, when the second function runs the first function results are duplicated. Is there a way for the onEdit1 to run only once then for the onEdit2 function to run only once when onEdit3 runs? This is my function. Once I can get the first two functions to work, and not repeat the previous function each time, I'll add the additional functions.

function onEdit(e) {
  trayOne(e);
  trayTwo(e)
  trayThree(e)
  trayFour(e)
  trayLCT(e)
}
 function trayOne(e){
   var ss = SpreadsheetApp.getActiveSheet();

  var t1 = ss.getRange('H12').getValue();
  var u1 = ss.getRange('I12').getValue();
  var lim = '80000'
  var lip = '10'
  if(t1>lim&&u1>lip)
  SpreadsheetApp.getUi().alert("It is recommended to replace Tray 1 feed and seporator 
 rolls.");

}
function trayTwo(e){
  var sa = SpreadsheetApp.getActiveSheet();

  var t2 = sa.getRange('H13').getValue();
  var u2 = sa.getRange('I13').getValue();
  var lim2 = '80000'
  var lip2 = '10'
  if(t2>lim2&&u2>lip2){
  SpreadsheetApp.getUi().alert("It is recommended to replace Tray 2 feed and seporator 
rolls.");
}
}

I'm new to app scripts so please be patient and explain how to accomplish this. Thank you in advance. Here is the link to my spreadsheet: https://docs.google.com/spreadsheets/d/1wkoZyCihUMEoGP9XOFnGuz_YFimgzAT_AKZ3binq9WA/edit#gid=0

I cannot see your sample Spreadsheet. Can you confirm it again? And, I cannot understand `a way for the onEdit1 to run only once then for the onEdit2 function to run only once when onEdit3 runs`. I apologize for my poor English skill. Can I ask you about the detail of your goal? — Tanaike, Aug 10 '22 at 02:47
Thank you for your reply. When I run the above script, onEdit1 runs just fine. I enter the data for H12, click OK then after I enter the data for I12 and click ENTER, I get my alert if the conditions are met. When I enter the data for H13 and click ENTER, I get the alert for H12(if the coditions were met previously). After entering the data for I13 and clicking ENTER, I get an alert for onEdit1 (if the conditions were met) then another alert for onEdit2 if those conditions are met. Since I have 5 possible onEdits, I don't want each onEdit to show an alert each time I enter the data. — Burt, Aug 11 '22 at 11:35
I'm not sure how to share tmy document, try this link: https://docs.google.com/spreadsheets/d/1wkoZyCihUMEoGP9XOFnGuz_YFimgzAT_AKZ3binq9WA/edit#gid=share — Burt, Aug 11 '22 at 11:36
It is a very simple sheet. Three columns, six rows starting with the header on row 11. G11 is "Unit", H11 is "Life" and I11 is "Issues". In column G i have; G12 "Value1", G13 is "Value2", G14 is "Value3", G15 is "Value4", and G16 is "Value5". At this point I'm only testing my script for Values 1 and 2 to be sure my script works as intended. Is there an alternate method to achieving the same results by not using the onEdit function? — Burt, Aug 11 '22 at 11:44
My main spreadsheet is very 'busy' and complicated. My goal is to have a script to guide the user through the different cells for data input and alert the user if action needs to be taken. In the other sections of my spreadsheet, I created a menu for the purpose of guiding the user through those sections. As I said, the main spreadsheet is very 'busy' and I want to make data entry as easy as possible for the user. — Burt, Aug 11 '22 at 13:42
Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill. — Tanaike, Aug 11 '22 at 23:55

score 0 · Answer 1 · answered Aug 12 '22 at 23:52

0

You can use the e event parameter to check which cell was edited. With that information, we can also write one generic function which handles all rows.

// https://stackoverflow.com/a/21231012
function letterToColumn(letter) {
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++) {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}

function onEdit(e) {
  tray(e);
}

function tray(e) {
  const trayStartRow = 12;

  var ss = SpreadsheetApp.getActiveSheet();

  const col = e.range.getColumn();
  const row = e.range.getRow();
  if (letterToColumn('H') <= col && col <= letterToColumn('I')
      && trayStartRow <= row && row <= 16) {
    var t1 = ss.getRange(row, letterToColumn('H')).getValue();
    var u1 = ss.getRange(row, letterToColumn('I')).getValue();
    var lim = '80000'
    var lip = '10'
    if (t1 > lim && u1 > lip)
      SpreadsheetApp.getUi().alert(
        `It is recommended to replace Tray ${row - trayStartRow + 1} feed and seporator rolls.`);
  }
}

Sheet

answered Aug 12 '22 at 23:52

krisz

2,686
2
11
18

Thank you Krisz!!! It worked (even though I don't understand what it says). I attempted to add the following for another parimeter, but two things happened. – Burt Aug 13 '22 at 03:10
elseif (t1 < lim && u1 > lif) SpreadsheetApp.getUi().alert( 'It is recommended to replace Tray ${row - trayStartRow + 1} feed and seporator rolls.'); } – Burt Aug 13 '22 at 03:11
Two things happened; 1) the part of the .alert "${row - trayStartRow + 1}" remained RED rather than BLACK in line 30. Why would it not change colors? 2) The alert would appear after entering the data in the first cel (H12), then again after entering data in (I12) and so on. What did I do wrong? Finally I rewrote the 'IF' to show, "if (t1 > lim && u1 > lip || t1 < lim && u1 > lif) this works great. I don't understand the onEdit function. Whenever it is in my script files, andI edit ANY cell, the onEdit kicks in to action. I don't want that. I want it to react only when certain cells are edited. – Burt Aug 13 '22 at 03:16
@Burt 1) You have to use backtick `` ` `` around the string literal. 2) That's normal. 3) That's how `onEdit` works. You can restrict it by checking the range of the event like I did. – krisz Aug 13 '22 at 03:52

Multiple onEdit functions in one

1 Answers1