Google Script to Copy Cell to Another Cell-CodePudding

I am looking for a script that only runs when a cell in column 7 is edited. There are multiple sheets so I want to specify which sheet it runs on. When the user edits a cell in column 7, there is a popup asking a Yes/No questions. If they answer No, the script runs. I need the script to copy the data in that cell to column 9. If they answer Yes, the script does not run. Here is what I have so far...I can get the popup to work; however, I do not know how to get the "copyData" formula to work and I am hoping for help on that. Thanks!

function onEdit2(){
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Current Seniority" ) {
    var r = s.getActiveCell();
    if( r.getColumn() == 7) {
 var ui = SpreadsheetApp.getUi();
 var response = ui.alert('Is employee being placed on leave?', 
ui.ButtonSet.YES_NO);
 // Process the user's response.
 if (response == ui.Button.YES) {
   Logger.log('The user clicked "Yes."');
 } else {
      copyData();
   Logger.log('The user clicked "No" or the close button in the dialog\'s title bar.');
 }
    }
  }
}

CodePudding user response：

Try this:

function onMyEdit(e) {
  e.source.toast("Entry")
  const sh = e.range.getSheet();
  const ns = ["Current Seniority","Sheet0"];
  const idx = ns.indexOf(sh.getName());
  if (~idx && e.range.columnStart == 7 && e.range.rowStart > 1 && e.value) {
    e.source.toast("Gate");
    let resp = SpreadsheetApp.getUi().alert('Is employee being placed on leave?', SpreadsheetApp.getUi().ButtonSet.YES_NO);
    if (resp == SpreadsheetApp.getUi().Button.NO) {
      //copyData();
      Logger.log(`The user clicked "No" or the close button in the dialog's title bar.`);
    } else {
      Logger.log('The user clicked no')
    }
  }
}

CodePudding user response：

Try the following code:

function onEdit2(){
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Current Seniority" ) {
    var r = s.getActiveCell();
    if( r.getColumn() == 8) {
      var ui = SpreadsheetApp.getUi();
      var response = ui.alert('Is employee being placed on leave?', 
      ui.ButtonSet.YES_NO);
      // Process the user's response.
      if (response == ui.Button.YES) {
        Logger.log('The user clicked "Yes."');
      }
      else {
        copyData(r, s);
        Logger.log('The user clicked "No" or the close button in the dialog\'s title bar.');
      }
    }
  }
}

function copyData(data, ss)
{
  var val = data.getValues();
  var newColumn = ss.getRange(data.getRow(), data.getColumn() 3);
  newColumn.setValue(val);
}

Make sure to add the trigger to the function so it runs on an edit or a change.

Try the following code:

This is how it works:

References: