Creating multiple dropdown buttons that will trigger a code when eddited in google sheets and apps script

user_17001793 0
Viewed 31

I have been making a google sheets document with buttons to make it more easy to use. This sheet will need to be used on my mobile phone. This is why I am using the drop down button with a onEdit(e) function in apps script. The point of the button is as follows:

I can click it on my phone and then I will get to choose a set of pre defined numbers. In this case : invoer,-2,-1,1,2,3,4,5,6. When a selection is made, the chosen number will be added to the value in a specific cell. The code looks like this:

function onEdit(e) { *calls the function HVBBplus() when the cell J2 is edited
if (!e) {
}
if (e.range.getA1Notation() !== 'J2') {
return;
}
HVBBplus()

}

function HVBBplus(){
var spreadsheet = SpreadsheetApp.getActive();
var invoer = spreadsheet.getRange('J2').getValue();
var output = spreadsheet.getRange('K2').getValue();
spreadsheet.getRange('K2').setValue(invoer+output);
spreadsheet.getRange('J2').setValue("Invoer");
}

This works perfectly, but the problem is that I cannot figure out how to create multiple buttons with different codes. When I copy and paste the function onEdit, it will only run the second onEdit function and it will ignore the first one. Can someone please help me with this problem and maybe explain how this piece of code works exactly?

google-apps-script google-sheets button
1 Answers

Basically it could be something like this:

function onEdit(e) {
  if (e.range.getA1Notation() == 'J2') HVBBplus();
  if (e.range.getA1Notation() == 'J3') HVBBplus2();
  if (e.range.getA1Notation() == 'J4') HVBBplus3();
}

function HVBBplus(){
  var spreadsheet = SpreadsheetApp.getActive();
  var invoer = spreadsheet.getRange('J2').getValue();
  var output = spreadsheet.getRange('K2').getValue();
  spreadsheet.getRange('K2').setValue(invoer+output);
  spreadsheet.getRange('J2').setValue("Invoer");
}

function HVBBplus2(){
  // do something
}

function HVBBplus3(){
  // do something
}

The code can be improved if you describe what the 'buttons' you have and what exactly they supposed to do.

Just in case the same onEdit() function can be written with switch/case statement:

function onEdit(e) {
  switch (e.range.getA1Notation()) {
    case ('J2'): HVBBplus();  break;
    case ('J3'): HVBBplus2(); break;
    case ('J4'): HVBBplus3(); break;
  }
}

Update

The obvious optimization could be to add arguments into the function HVBBplus():

function onEdit(e) {
  switch (e.range.getA1Notation()) {
    case ('J2'): HVBBplus('J2','K2');  break;
    case ('J3'): HVBBplus('J3','K3');  break;
    case ('J4'): HVBBplus('J4','K4');  break;
  }
}

function HVBBplus(cell1, cell2){
  var spreadsheet = SpreadsheetApp.getActive();
  var invoer = spreadsheet.getRange(cell1).getValue();
  var output = spreadsheet.getRange(cell2).getValue();
  spreadsheet.getRange(cell2).setValue(invoer+output);
  spreadsheet.getRange(cell1).setValue("Invoer");
}

As a guess. I don't know your goal.

user_142654690
Related