Google Spreadsheet - how to add confirmation msg box before executing script

Question

I would like to add a confirmation message box to appear before the script runs.

Here is the script:

function moveTo(e) {
// moves a row from a sheet to another when a magic value is entered in a column
// adjust the following variables to fit your needs try {

var sheetNameToWatch = "Active Discs";
var columnNumberToWatch = 15; // column A = 1, B = 2, etc.
var valueToWatch = "Completed";
var sheetNameToMoveTheRowTo = "CompletedShipped Projects";


var ss = e.source;
var sheet = ss.getActiveSheet();
var range = e.range;

if (sheet.getName() == sheetNameToWatch && range.columnStart == columnNumberToWatch && e.value == valueToWatch) {

  var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
  var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
  sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
  sheet.deleteRow(range.getRow());
}
} catch (error) { Logger.log(error)
}

The message box would contain the following message set-up: The message should read "Do you confirm to move Ocean Gil to Completed Shipped Projects Sheet?"

var app = UiApp.createApplication().setHeight(150).setWidth(250);
var msg = "Do you confirm to move " + "Active row cells A + B" + " to Completed Shipped   Projects Sheet?";
app.setTitle("Please Confirm this Action");
app.add(app.createVerticalPanel().add(app.createLabel(msg)));
var doc = SpreadsheetApp.getActive();
doc.show(app);
}

If the person choose yes, then the "moveTo" script executes. If no, then script does nothing and closes the prompt window.

Here is the shared Google Spreadsheet.

https://docs.google.com/spreadsheet/ccc?key=0Ag8NytPhOo00dER6WG1PQ3g3V1hCbnVUaTJUZDFZenc&usp=sharing

Any help with code would be much appreciated. Thanks in Advance.

Serge insas Serge insas · Accepted Answer · 2014-03-12T07:29:43

It would probably be more easy and compact to use the Browser class in this case, see doc here with example code

EDIT : in you code you can use it like this for example :

function moveTo(e) {
// moves a row from a sheet to another when a magic value is entered in a column
// adjust the following variables to fit your needs try {

var sheetNameToWatch = "Active Discs";
var columnNumberToWatch = 15; // column A = 1, B = 2, etc.
var valueToWatch = "Completed";
var sheetNameToMoveTheRowTo = "CompletedShipped Projects";

  try{
var ss = e.source;
var sheet = ss.getActiveSheet();
var range = e.range;

if (sheet.getName() == sheetNameToWatch && range.columnStart == columnNumberToWatch && e.value == valueToWatch) {
  var confirm = Browser.msgBox('Do you want to move data to the other sheet ?', Browser.Buttons.YES_NO); 
  if(confirm!='yes'){return};// if user click NO then exit the function, else move data
  var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
  var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
  sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
  sheet.deleteRow(range.getRow());
}
  } catch (error) { Logger.log(error)}
}

Google Spreadsheet - how to add confirmation msg box before executing script

1 Answers