My setValue is not working in google sheets

Question

I am trying to set the value to the value that has been entered by the user in a prompt when the value in e11 is different than "MC ou CC"

I am able to get the value from the prompt and display it in the logger.log but somehow the value is not transferred to the D11 cell.

Function onEdit(e) { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Demande");
  if(s.getRange("E11").getValue() != "MC ou CC"){     
    var ui = SpreadsheetApp.getUi() ;
    var Bouton =   ui.prompt('Veuillez entrer le montant du paiement 
    mensuel', ui.ButtonSet.OK_CANCEL);
    var Reponse = Bouton.getResponseText();
    if (Bouton == ui.Button.OK) {
      Logger.log('The user clicked "OK" or the close button in the d 
      ialog\'s title  bar.'+Reponse);
      s.getRange("D11").setValue(Reponse);
    } else {
      Logger.log('The user clicked "No" or the close button in the 
      dialog\'s title  bar.'+Reponse);
    }
  }
 }

soMario soMario · Accepted Answer · 2020-12-30T03:56:26

Issue:

You need to get the getSelectedButton() in order to find out which button the user clicked.

Solution based on your answer:

Replace if(Bouton == ui.Button.OK) with if(Bouton.getSelectedButton() == ui.Button.OK):

function myFunction() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Demande");
  if(s.getRange("E11").getValue() != "MC ou CC"){     
    var ui = SpreadsheetApp.getUi() ;
    var Bouton =   ui.prompt('Veuillez entrer le montant du paiement mensuel', ui.ButtonSet.OK_CANCEL);
    var Reponse = Bouton.getResponseText();
    if (Bouton.getSelectedButton() == ui.Button.OK) {
      Logger.log('The user clicked "OK" or the close button in the dialog\'s title  bar.'+Reponse);
      s.getRange("D11").setValue(Reponse);
    } else {
      Logger.log('The user clicked "No" or the close button in the dialog\'s title  bar.'+Reponse);
    }
  }
 }

since you execute this function manually, there is no need to use an onEdit trigger. Instead you can use a regular function and hence I changed the name. If you want to use the onEdit trigger properly, then see the recommended answer below.

Recommended Solution:

The onEdit trigger and generally trigger functions are not supposed to be ran manually as you do right now.
The onEdit trigger is activated when the user changes the value of a cell.

Take advantage of the event object:

function onEdit(e) { 
  var ss = e.source;
  var s = ss.getActiveSheet();
  var rng = e.range;
  if(s.getName() == "Demande" && rng.getA1Notation()=='E11' && rng.getValue()!="MC ou CC"){    
    var ui = SpreadsheetApp.getUi();
    var Bouton =   ui.prompt('Veuillez entrer le montant du paiement mensuel', ui.ButtonSet.OK_CANCEL);
    var Reponse = Bouton.getResponseText();
    if (Bouton.getSelectedButton() == ui.Button.OK) {
      Logger.log('The user clicked "OK" or the close button in the dialog\'s title  bar.'+Reponse);
      s.getRange("D11").setValue(Reponse);
    } else {
      Logger.log('The user clicked "No" or the close button in the dialog\'s title  bar.'+Reponse);
    }
  }
 }

Be careful:

This solution uses the event object and it is the recommended way to use an onEdit trigger. This won't work if you manually execute the function. It will only work when the user makes changes to the value of a cell.
It assumes that you only want to execute the code inside the main if condition when you edit the value of cell 'E11' in the sheet "Demande". So it will only work when you edit the cell 'E11' in "Demande".

If you want a different behaviour, let me know and I will modify the code accordingly.

My setValue is not working in google sheets

1 Answers

Issue:

Solution based on your answer:

Recommended Solution: