At the moment i have the following script that runs automatically when cell D2 is edited

My issue is that it takes 6-8 seconds to run but staff do not know if is running

am i able to have a popup come up when it starts saying "running script" like it does if you assign the script to a button?

function onEdit(e) { var sheet = e.source.getSheetByName("Input1"); if (sheet && e.range.getA1Notation() === "D2") { searchData(); } }

function searchData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var inputSheet = ss.getSheetByName("Input1");
  var dataSheet = ss.getSheetByName("Job Sheet");

  var jobnumber = inputSheet.getRange("D2").getValue();

  // Check if Job number field is empty
  if (!jobnumber) {
    SpreadsheetApp.getUi().alert('Enter a Job number.');
    return;
  }

  var dataRange = dataSheet.getDataRange().getValues();
  var found = false;

  for (var i = 0; i < dataRange.length; i++) {
    if (dataRange[i][0] == jobnumber) {
      found = true;
      inputSheet.getRange("D4").setValue(dataRange[i][1]);
      inputSheet.getRange("D6").setValue(dataRange[i][2]);
      inputSheet.getRange("D8").setValue(dataRange[i][3]);
      inputSheet.getRange("D10").setValue(dataRange[i][4]);
      inputSheet.getRange("D12").setValue(dataRange[i][5]);
      inputSheet.getRange("D14").setValue(dataRange[i][6]);
      inputSheet.getRange("D16").setValue(dataRange[i][7]);
      inputSheet.getRange("D18").setValue(dataRange[i][8]);
      inputSheet.getRange("D20").setValue(dataRange[i][9]);
      inputSheet.getRange("D22").setValue(dataRange[i][10]);
      inputSheet.getRange("D24").setValue(dataRange[i][11]);
      inputSheet.getRange("D26").setValue(dataRange[i][13]);
      inputSheet.getRange("D28").setValue(dataRange[i][14]);
      inputSheet.getRange("D30").setValue(dataRange[i][15]);
      inputSheet.getRange("A34").setValue(jobnumber);
      break;
    }
  }

  if (!found) {
    SpreadsheetApp.getUi().alert('Job number not found.');
  }
}

At the moment i have the following script that runs automatically when cell D2 is edited

My issue is that it takes 6-8 seconds to run but staff do not know if is running

am i able to have a popup come up when it starts saying "running script" like it does if you assign the script to a button?

function onEdit(e) { var sheet = e.source.getSheetByName("Input1"); if (sheet && e.range.getA1Notation() === "D2") { searchData(); } }

function searchData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var inputSheet = ss.getSheetByName("Input1");
  var dataSheet = ss.getSheetByName("Job Sheet");

  var jobnumber = inputSheet.getRange("D2").getValue();

  // Check if Job number field is empty
  if (!jobnumber) {
    SpreadsheetApp.getUi().alert('Enter a Job number.');
    return;
  }

  var dataRange = dataSheet.getDataRange().getValues();
  var found = false;

  for (var i = 0; i < dataRange.length; i++) {
    if (dataRange[i][0] == jobnumber) {
      found = true;
      inputSheet.getRange("D4").setValue(dataRange[i][1]);
      inputSheet.getRange("D6").setValue(dataRange[i][2]);
      inputSheet.getRange("D8").setValue(dataRange[i][3]);
      inputSheet.getRange("D10").setValue(dataRange[i][4]);
      inputSheet.getRange("D12").setValue(dataRange[i][5]);
      inputSheet.getRange("D14").setValue(dataRange[i][6]);
      inputSheet.getRange("D16").setValue(dataRange[i][7]);
      inputSheet.getRange("D18").setValue(dataRange[i][8]);
      inputSheet.getRange("D20").setValue(dataRange[i][9]);
      inputSheet.getRange("D22").setValue(dataRange[i][10]);
      inputSheet.getRange("D24").setValue(dataRange[i][11]);
      inputSheet.getRange("D26").setValue(dataRange[i][13]);
      inputSheet.getRange("D28").setValue(dataRange[i][14]);
      inputSheet.getRange("D30").setValue(dataRange[i][15]);
      inputSheet.getRange("A34").setValue(jobnumber);
      break;
    }
  }

  if (!found) {
    SpreadsheetApp.getUi().alert('Job number not found.');
  }
}

• google-sheets
• google-apps-script

• Maybe you could use just after: if (sheet && e.range.getA1Notation() === "D2") { the following: SpreadsheetApp.getActiveSpreadsheet().toast("Running script...", "Status", 3);, then implement it as well inside searchData() – Nabnub Commented Mar 6 at 11:39

2 Answers 2

Maybe you can use "toast" that runs a small box in the bottom right corner. You can set these parameters:

toast(msg, title, timeoutSeconds) 

Like this:

SpreadsheetApp.getActive().toast("Please wait 10 seconds","The process is running",10)

You can't set it to wait until the process is finished, but the user will know it's done.

Other option is to use a cell as a reference for the user. In the beginning of the code, you can write something like:

inputSheet.getRange("H1").setValue("Process Running").setBackground("yellow")

And at the end of searchData:

inputSheet.getRange("H1").setValue("Process Finished").setBackground("green")

Custom Dialog for Status of onEdit() Run Code

Your current goal has no direct way with Google Apps Script. However, there are workarounds to make this happen.This can actually be achieved by playing around with a Custom Dialog, OnSuccessHandlers for Web Apps and Installable Triggers.

Note: I do think that it could be done with Simple Trigger onEdit(e) however there is an ongoing bug with combining ShowModalDialog and onEdit(e) as of the moment it still produces authorization scope error

Sample Code

Code.gs

function sampleTask() { // This is just a synthetic function I created to mimic a long running Task
  Utilities.sleep(3000);
  return "Done";
}

function showLoadingDialog(e) {
if(e.range.getA1Notation() == "D2"){
  var ui = SpreadsheetApp.getUi();
  var html = HtmlService.createHtmlOutputFromFile('index')
    .setWidth(300)
    .setHeight(150);
  ui.showModalDialog(html, "Notification");
}}

index.html

<!DOCTYPE html>
<html>
  <head>
    <style>
      body {
        text-align: center;
        font-family: Arial, sans-serif;
      }
      #loading {
        font-size: 16px;
        color: #000;
      }
    </style>
  </head>
  <body>
    <div id="loading">Please wait, the script is still running...</div>

    


     function onSuccess(status) {
        var div = document.getElementById('loading');
        div.innerHTML = "Your process is "+ status;
      }



google.script.run.withSuccessHandler(onSuccess)
          .sampleTask();
      
  </body>
</html>

Readme

To implement this you need to replace the function sampleTask() to your desired function to be waited by the notification in your case it is searchData(). Please do change the HTML and change the name based on your desired function to run. The onSuccessHandler waits for the returned data that means that your function needs to return something so it can run the changes on the HTML. In this case I am just changing the notification to process completed once the function run is completed.

Installable Triggers - This is almost the same with your onEdit(e) the only difference is that you have to install it and you can have more control about authorization with this one by modifying the manifest. You can read more about Installable Triggers here.

Steps on Managing Triggers Manually

1. Open your Apps Script project.

2. On the left, click Triggers.

3. At the bottom right, click Add Trigger.

4. Select and configure the type of trigger you want to create.

5. Click Save.

The function that should be run is the showLoadingDialog event type should be onEdit.

References:

Dialogs - Apps Script

Installable Triggers - Apps Script

onSuccess Handlers - Apps Script