CODEWITHSUNDEEP
google-sheetsgoogle-drive-api
user3858166
user3858166

Reputation: 61

Alert when code is running background in google sheet

Working with Google App script and Javascript, I managed to retrieve xls Gmail attachments, convert it to google sheets, send an warning email to some recipients, insert an event with the Google Drive folder link when a new attachment is added to it, and at the end I managed with a standalone script to import some of the data coming from such xls file converted to a main Google Sheet where one operator is working h24 ( updating some data to be forwarded later on and during the service itself). The standalone script run every 30 minutes to check incoming mail ( the schedule for the next day or days), and when it find it, it execute the code. The lock service is not available on standalone scripts up to now, so I cannot lock the document, so I wish to display a message to the user that the script is about to run and not edit anything, since other scripts bounded to the Spreadsheet are using the Lock service, and so the result could be quite disappointing. I searched info regarding this "alert", but I cannot find a clue about it. Could you address me somewhere? I could add a menu function instead of a standalone script, and the operator could select "Import data" and that's it, but I wish to automate the operation in background.

Upvotes: 3

Views: 5128

Answers (2)

James D
James D

Reputation: 3152

You could use the HTML Service. I don't know much about it myself but I think the following may do what you need:

At the top of you .gs file:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var htmlApp = HtmlService.createTemplateFromFile("html");     
var status = "Loading....."
 htmlApp.data = status;
 ss.show(htmlApp.evaluate()
 .setWidth(300)
 .setHeight(200));

At the end (just before the last brace):

var status = "Finished!"
 htmlApp.data = status;
 ss.show(htmlApp.evaluate()
 .setWidth(300)
 .setHeight(200));

In a new HTML script file named 'html':

<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h2>Updating..</h2>
<p id="status">(innerHTML).</p>
<div id="imageico"></div>
<script>
var imageContainer = document.getElementById("imageico");
if (<?= data ?> != "Finished!"){
document.getElementById("status").innerHTML = "Running script, please wait..";
} else {
document.getElementById("status").innerHTML = "Finished! You can now close the window.";
closeBtn();

}  
   function closeBtn(){
   var button = document.createElement("INPUT");
    button.setAttribute("type", "button");
    button.setAttribute("value", "Close");
    button.setAttribute("onclick", "closeWindow();");
    imageContainer.appendChild(button);
    }

function closeWindow(){
google.script.host.close();
}
</script>
</body>
</html>

EDITED Based on comments

In your .gs file:

 function test() {

    htmlApp("","");

/*
*
*  Put your code in here....
*
*/     

  Utilities.sleep(3000); // change this value to show the "Running script, please wait.." HTML window for longer time.

  htmlApp("Finished!",""); 

  Utilities.sleep(3000);  // change this value to show the "Finished! This window will close automatically. HTML window for longer time.

  htmlApp("","close"); // Automatically closes the HTML window.

}

 function htmlApp (status,close) {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var htmlApp = HtmlService.createTemplateFromFile("html");     
      htmlApp.data = status;
      htmlApp.close = close;
      ss.show(htmlApp.evaluate()
     .setWidth(300)
     .setHeight(200));
}

In the HTML script file named 'html':

<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h2>Updating..</h2>
<p id="status">(innerHTML).</p>
<div id="imageico"></div>
<script>
var imageContainer = document.getElementById("imageico");
if (<?= data ?> != "Finished!"){
document.getElementById("status").innerHTML = "Running script, please wait..";
} else {
document.getElementById("status").innerHTML = "Finished! This window will close automatically.";
}     
if (<?= close ?> == "close"){
google.script.host.close();
}
</script>
</body>
</html>

Have a look at this Sample Spreadsheet.

Upvotes: 5

abielita
abielita

Reputation: 13469

I don't see any methods or sample scripts regarding your concern. But this documentation about alert might help. It will open a dialog box in the user's editor with the given message and an "OK" button. However, this method suspends the server-side script while the dialog is open and the script will resume after the user dismisses the dialog.

You may also execute alert dialogs before running your script.

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('Custom Menu')
      .addItem('Show alert', 'showAlert')
      .addToUi();
}

function showAlert() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.alert(
     'Please confirm',
     'Are you sure you want to continue?',
      ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (result == ui.Button.YES) {
    // User clicked "Yes".
    ui.alert('Confirmation received.');
  } else {
    // User clicked "No" or X in the title bar.
    ui.alert('Permission denied.');
  }
}

Upvotes: 1

Related Questions