Home > Software design >  Join two functions on google sheets
Join two functions on google sheets

Time:08-26

I have this function which auto puts in the date in the second colum, but i need it to work on a second sheet. It now doesnt work.. How do i join them on the one function to work

function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Contracts Log" ) { //checks that we're on Sheet1 or not
var r = s.getActiveCell();
if( r.getColumn() == 1 ) { //checks that the cell being edited is in column A
var nextCell = r.offset(0, 1);
if( nextCell.getValue() === '' ) //checks if the adjacent cell is empty or not?
nextCell.setValue(new Date());
}
}
}

function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Contracts Log" ) { //checks that we're on Sheet1 or not
var r = s.getActiveCell();
if( r.getColumn() == 1 ) { //checks that the cell being edited is in column A
var nextCell = r.offset(0, 1);
if( nextCell.getValue() === '' ) //checks if the adjacent cell is empty or not?
nextCell.setValue(new Date());
}
}
}

CodePudding user response:

Both functions are the same so

function onEdit() {
  const sh = e.range.getSheet();
  if (sh.getName() == "Contracts Log" && e.range.columnStart == 1 && e.range.offset(0, 1).getValue() === '') {
    e.range.offset(0, 1).setValue(new Date());
  }
}

This works on more than one sheet you can easily add more

function onEdit() {
  const sh = e.range.getSheet();
  const shts = ["Contracts Log","Sheet2"];
  if (~shts.indexOf(sh.getName()) && e.range.columnStart == 1 && e.range.offset(0, 1).getValue() === '') {
    e.range.offset(0, 1).setValue(new Date());
  }
}

CodePudding user response:

This might be done by applying a small change to the first onEdit function: use a second comparison expression and the OR operator (||) on the first if statement. Discard the second onEdit function because functions names should be unique otherwise only the last one to be loaded by the runtime engine will be effective.

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() == "Contracts Log" || s.getName() == "Name of the other sheet") { //checks that we're on Sheet1 , Sheet2 or not
    var r = s.getActiveCell();
    if (r.getColumn() == 1) { //checks that the cell being edited is in column A
      var nextCell = r.offset(0, 1);
      if (nextCell.getValue() === '') //checks if the adjacent cell is empty or not?
        nextCell.setValue(new Date());
    }
  }
}
  • Related