-1

I'm trying to create a spreadsheet for my team, and using this script I am only getting one of the on edit if statements to function. How can I fix this? I'm not very versed in script writing.

function onEdit(e) {
  const sh=e.range.getSheet();
  if(sh.getName()=='Confirmation Check' && e.range.columnStart==8 && e.value=='TRUE') {
    var resp=SpreadsheetApp.getUi().alert('REMINDER: Did you update the P.5 with the initiation date?', SpreadsheetApp.getUi().ButtonSet.YES_NO);
    if(resp==SpreadsheetApp.getUi().Button.YES) {
      if(e.range.columnStart >8) return;
      e.source.getActiveSheet().getRange(e.range.rowStart,9).setValue(new Date());
      return;
      if(sh.getName()=='Confirmation Check' && e.range.columnStart==6 && e.value=='TRUE') {
        var resp=SpreadsheetApp.getUi().alert('REMINDER: Did you update the P.5 with the authoritzation date?', SpreadsheetApp.getUi().ButtonSet.YES_NO);
        if(resp==SpreadsheetApp.getUi().Button.YES) {
          if(e.range.columnStart >6) return;
          e.source.getActiveSheet().getRange(e.range.rowStart,7).setValue(new Date());
          return;
        }
      }
    }
  }
}
3
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Nov 12, 2022 at 9:31
  • Welcome to Web Applications Stack Exchange. Nov 12, 2022 at 22:49
  • Hi, welcome to WebApps. May I make a point for your future reference... your question should your explain EXACTLY what your script is supposed to do AND the specific problem that you are having. In this case it's something like..."onEdit should insert the date in the adjacent cell if Column 6 or 8 = true on sheet = "Confirmation Check" AND a response of "Yes" to an alert; the problem is that it's working for Column 8 but not for Column 6." Don't leave it so that a volunteer must read your script and try and make sense of it.
    – Tedinoz
    Nov 13, 2022 at 0:50

3 Answers 3

1

Use Range.offset() to get the next cell to the right, and observe onEdit(e) best practices, like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (e.value !== 'TRUE'
    || ![6, 8].includes(e.range.columnStart)
    || e.range.getSheet().getName() !== 'Confirmation Check') {
    return;
  }
  const ui = SpreadsheetApp.getUi();
  const response = ui.alert('REMINDER: Did you update the P.5 with the initiation date?', ui.ButtonSet.YES_NO)
  if (response !== ui.Button.YES) {
    return;
  }
  e.range.offset(0, 1).setValue(new Date());
}
0

You should check the openings and endings of the if and else cycles, that was why it wasn't working. Try this:

function onEdit(e) {
const sh=e.range.getSheet(); 
  if(sh.getName()=='Confirmation Check' && e.range.columnStart==8 && e.value=='TRUE') {
    var resp=SpreadsheetApp.getUi().alert('REMINDER: Did you update the P.5 with the initiation date?', SpreadsheetApp.getUi().ButtonSet.YES_NO)
    if(resp==SpreadsheetApp.getUi().Button.YES) {
        //if(e.range.columnStart>8) return --> You've already checked number of column
        //else {}
        e.source.getActiveSheet().getRange(e.range.rowStart,9).setValue(new Date()); return;} 
        //}
    else return }
  
  else if(sh.getName()=='Confirmation Check' && e.range.columnStart==6 && e.value=='TRUE') {
    var resp=SpreadsheetApp.getUi().alert('REMINDER: Did you update the P.5 with the authorization date?', SpreadsheetApp.getUi().ButtonSet.YES_NO)
    if(resp==SpreadsheetApp.getUi().Button.YES) {
        //if(e.range.columnStart>6) return --> You've already checked number of column
        //else {}
        e.source.getActiveSheet().getRange(e.range.rowStart,7).setValue(new Date()); return;} 
        //}
    else return }
}
0

I am offering this answer simply because it uses the syntax and style of your original script. This may help with the transition to a more sophisticated solution.


function onEdit(e) {
  const sh=e.range.getSheet()
  if(sh.getName()=='Confirmation Check' && e.value=='TRUE'&& (e.range.columnStart==8 || e.range.columnStart==6) ){
    var resp=SpreadsheetApp.getUi().alert('REMINDER: Did you update the P.5 with the initiation date?', SpreadsheetApp.getUi().ButtonSet.YES_NO);
    if(resp==SpreadsheetApp.getUi().Button.YES) {
      e.source.getActiveSheet().getRange(e.range.rowStart,e.range.columnStart+1).setValue(new Date());
    }
  }
}
2
  • FWIW, compared to this answer, the answer by @doubleunary demonstrates another level of sophistication in scripting. It is the better answer.
    – Tedinoz
    Nov 13, 2022 at 1:26
  • One might ask 'could you use a switch statement to evaluate the options for e.range.columnStart. The answer is "yes", but there is no advantage since setValue can be expressed in a single line of code.
    – Tedinoz
    Nov 13, 2022 at 1:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.