Unable to run two independent functions in google apps scripts for sheets-CodePudding

So I am creating a sheet to track some data, and I have it fed into a chart via a ratio and a date, so for instance, I had 10 and 5 equals a ratio of 2, at this particular time. I was able to find a script to automate the date, but I also wanted to automate the ratio, as every time I have to put in cell reference/ cell reference. Sure its just shift click and drag but I would like it to be automated just like the date. I have been teaching myself javascript but I am still VERY new. Basically I came up with this.

function onEdit() { 
var s = SpreadsheetApp.getActiveSheet(); 
if( s.getName() == "Overall", "M&K", "Con" ) {  
var r = s.getActiveCell();  
if( r.getColumn() == 2 ) { //checks that the cell being edited is in column B  
var nextCell = r.offset(0, -2);  
if( nextCell.getValue() === '' ) //checks if the adjacent cell is empty or not?  
nextCell.setValue(new Date()).setNumberFormat("dd/MM/yyyy, hh:mm");  
}  
}  
}  
function onEdit() {  
var s1 = SpreadsheetApp.getActiveSheet();  
if( s1.getName() == "Overall", "M&K", "Con" ) {  
var r1 = s1.getActiveCell();  
if( r1.getColumn() == 3 ) { //checks that the cell being edited is in column C  
var a = r1.offset(0, 1);  
var b = r1.offset(0, -1);  
var c = r1.offset(0, 0);  
if( a.getValue() === '' ) //checks if the adjacent cell is empty or not?  
a.setValue(b.getValue()/c.getValue());  
}  
}  
}

And when I put this into my one sheet, only one will work, not both, and its always the bottom one, and nothing will work if I try and put it first. I'm sure it has something to do with how the script is structured but I honestly have no idea.

I tried changing around the values and restructuring it to the best of my knowledge and ability, but I could never get two to run simultaneously, its always either one or the other.

CodePudding user response：

You have two functions with the same name, which is generally not allowed. Google Apps Script doesn't know which one to run, so it grabs the first one it sees.

What you'll want to do is give each of these functions unique names and then set them up as Installable Triggers.

In the Triggers section of your project (click the hourglass on the left side of the screen), select one of your functions and set it to run "From spreadsheet" and "On edit". Do this for each of your functions so that you have two unique On Edit triggers.

CodePudding user response：

Every function in a project must have a unique name.

This doesn't work:

s.getName() == "Overall", "M&K", "Con"

Just for kicks I played around with it and this does seem to work. Although, I'm not sure why.

s.getName() == "Overall" || "M&K" || "Con"

Perhaps someone else can explain it.

This would be another way of getting the functionality without having to use an installable trigger. Note: I didn't look closely at most of your logic in the second part. Just assumed it was okay

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Overall" || "M&K" || "Con" && e.range.columnStart == 2) {
    const nextCell = e.range.offset(0, -2);
    if (nextCell.getValue() === '') //checks if the adjacent cell is empty or not?  
      nextCell.setValue(new Date()).setNumberFormat("dd/MM/yyyy, hh:mm");
  }
  if (sh.getName() == "Overall", "M&K", "Con" && e.range.columnStart == 3) {
    var r1 = sh.getActiveCell();
    var a = r1.offset(0, 1);
    var b = r1.offset(0, -1);
    var c = r1.offset(0, 0);
    if (a.getValue() === '') {
      a.setValue(b.getValue() / c.getValue());
    }
  }
}

I would normally write it like this:

function onEdit(e) {
  const sh = e.range.getSheet();
  const shts = ["Overall", "M&K", "Con"];
  const idx = shts.indexOf(sh.getName());
  if (~idx) {
    if (e.range.columnStart == 2) {
      const nextCell = e.range.offset(0, -2);
      if (nextCell.getValue() === '') 
        nextCell.setValue(new Date()).setNumberFormat("dd/MM/yyyy, hh:mm");
    }
    if (e.range.columnStart == 3) {
      var a = e.range.offset(0, 1);
      var b = e.range.offset(0, -1);
      var c = e.range.offset(0, 0);
      if (a.getValue() === '') {
        a.setValue(b.getValue() / c.getValue());
      }
    }
  }
}