Google Sheets - Merging two scripts together

[chrono2483]

Hello,

I was able to search and find to codes that independently was able to get to work/accomplish what I want it to do. However when I try to include them in the same sheet, it looks like code#2 cancels out the first one. Is there a way to combine the two so they both work?

Code 1: Adjusted the drop-down list based on user selection in Col B, C and D
Code 2: Date Stamps and edits on that row

Code 1:

function depDrop_(range, sourceRange){
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
range.setDataValidation(rule);
}
function onEdit (){
var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
var aColumn = aCell.getColumn();
if (aColumn == 2 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}
else if (aColumn == 3 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}
  else if (aColumn == 4 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}
}

Code 2:

    function onEdit(event) {  var eventRange = event.range;  if (eventRange.getColumn() == 4) { // 4 == column D     // getRange(row, column, numRows, numColumns)    var columnXRange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 57, eventRange.getNumRows(), 57);     var values = columnXRange.getValues();      for (var i = 0; i < values.length; i++) {      if(!values[i][0]){  // If cell isn't empty       values[i][0] = new Date();      }    }    columnXRange.setValues(values);   }}

Can anyone point in the right direction or help explain/understand how to merge the two together?

Thank you.

 

[Norie]

The 2 pieces of code are quite different, how would you want to merge them?

By the way why does the first piece of code use ActiveCell to find out which cell/range has been edited and the second used event.range?

 

[chrono2483]

The 2 pieces of code are quite different, how would you want to merge them?

By the way why does the first piece of code use ActiveCell to find out which cell/range has been edited and the second used event.range?

I believe that is where the challenge is. Both are codes I've found online to accomplish my goal, which is why they are different. Also the reason why they seem to cancel each other out when using both.

By merge, I mean I want to be able to use Code #1 , as well as be able to add a time stamp to each row (Col BE) when an edit/selection is made to that row's Col D.

 

[Norie]

Perhaps you could create a function to do the time stamp, something like this.

function timeStamp(rng)

{

if (rng.getColumn() == 4)
    { // 4 == column D
      // getRange(row, column, numRows, numColumns)
      var columnXRange = SpreadsheetApp.getActiveSheet().getRange(rng.getRow(), 57, rng.getNumRows(), 57);
      var values = columnXRange.getValues();
      for (var i = 0; i < values.length; i++)
        {
        if(!values[0]){// If cell isn't empty
        values[0] = new Date();
        }
    }
    columnXRange.setValues(values);
    }
}

You could call that from the first set of code like this,

timeStamp(aCell)

which would go in the last else if, i.e. the one that checks if column D has been changed.

 

[chrono2483]

Perhaps you could create a function to do the time stamp, something like this.

function timeStamp(rng)

{

if (rng.getColumn() == 4)
    { // 4 == column D
      // getRange(row, column, numRows, numColumns)
      var columnXRange = SpreadsheetApp.getActiveSheet().getRange(rng.getRow(), 57, rng.getNumRows(), 57);
      var values = columnXRange.getValues();
      for (var i = 0; i < values.length; i++)
        {
        if(!values[0]){// If cell isn't empty
        values[0] = new Date();
        }
    }
    columnXRange.setValues(values);
    }
}

You could call that from the first set of code like this,

timeStamp(aCell)

which would go in the last else if, i.e. the one that checks if column D has been changed.

Thank you Norie. I've attempted to use the suggested code, but does not apply the time stamp. I added timeStamp (aCell) to my original code at the end. I then added a new script and used the function timeStamp (rng) code.

Could I be missing a step?

 

[Norie]

Can you post the adjusted code?

 

[chrono2483]

Can you post the adjusted code?

Sure...

function depDrop_(range, sourceRange){
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
range.setDataValidation(rule);
}
function onEdit (){
var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
var aColumn = aCell.getColumn();
if (aColumn == 2 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}
else if (aColumn == 3 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}
  else if (aColumn == 4 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
  
call timeStamp(aCell)  
}
}

and for the second script:

function timeStamp(rng)

{

if (rng.getColumn() == 4)
    { // 4 == column D
      // getRange(row, column, numRows, numColumns)
      var columnXRange = SpreadsheetApp.getActiveSheet().getRange(rng.getRow(), 57, rng.getNumRows(), 57);
      var values = columnXRange.getValues();
      for (var i = 0; i < values.length; i++)
        {
        if(!values[i][0]){// If cell isn't empty
        values[i][0] = new Date();
        }
    }
    columnXRange.setValues(values);
    }
}

 

[Norie]

Can you post a link to an example Google sheet?