CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Andy K
Andy K

Reputation: 109

getUi() alert not executing response

This script moves a row in Google Sheets from one sheet to another. It works fine without the alert box. I'm trying to add in a box for confirmation before moving. Box shows, but nothing happens when I click "Yes". I tried with a prompt as well, and there was no result.

What can I do to have this function properly?

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  var sourceSheet = "Source"; // Name of sheet copy from
  var targetSheet = "Destination"; // Name of sheet copy to
  var targetSheet = ss.getSheetByName(targetSheet);
  var ui = SpreadsheetApp.getUi();

function CountColB(){  // Function to get first filled column B, starting from bottom.
    var data = targetSheet.getDataRange().getValues();
  for(var i = data.length-1 ; i >=0 ; i--){
    if (data[i][1] != null && data[i][1] != ''){
      return i+1 ;
    }}}
var lastRow = CountColB();

  if(s.getName() == sourceSheet && r.getColumn() == 1 && r.getValue() == "X") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var target = targetSheet.getRange(lastRow + 1, 2);
    ui.alert('Move Row?', ui.ButtonSet.YES_NO);
    if (response == ui.Button.YES) {
    s.getRange(row, 2, 1, numColumns).moveTo(target);
    s.deleteRow(row);
 }

  }
}

Upvotes: 2

Views: 152

Answers (1)

Mennyg
Mennyg

Reputation: 330

You are not setting the 'response' variable to be the prompt response. Try this as your if statement:

  if(s.getName() == sourceSheet && r.getColumn() == 1 && r.getValue() == "X") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var target = targetSheet.getRange(lastRow + 1, 2);
    var response = ui.alert('Move Row?', ui.ButtonSet.YES_NO);
    if (response == ui.Button.YES) {
    s.getRange(row, 2, 1, numColumns).moveTo(target);
    s.deleteRow(row);
 }

Upvotes: 1

Related Questions