How can I get it to wait until the user enters the date and then it closes the dialog box and writes the value to its destination?

Viewed 41

Here's the problem:

I got these codes running without error, but altogether, they won't work as expected.

The intent is once the user ticks a box in column A, the code running on onEdit basis is supposed to open a dialog box for the user to enter the date. Then the code changes a value in one of the row's column and the date is to be written right next to it. Once the user inputs the date and clicks on Add, the dialog box is supposed to close automatically.

I am not sure about the order of the functions and how to have the onEdit function wait until the user enters the date so that the dialog box disappears and it continues writing the date to its destination.

Thanks for your attention.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('DB');
  var dbData = sheet.getRange(2, 1, sheet.getLastRow(), 39).getValues();
  var ItemNumberSheet = ss.getSheetByName("To ORDER");
  var ItemNoRow = ItemNumberSheet.getActiveCell().getRow();//Getting row from active cell
  var ItemNoCol = 4;
  var ItemNo = ItemNumberSheet.getRange(ItemNoRow, ItemNoCol).getValue();
  var orderTicked = ItemNumberSheet.getRange(ItemNoRow, 1).getValue();
  var dbRow = 1;

  if (ss.getActiveSheet().getName() == 'To ORDER' && 
  ItemNumberSheet.getRange(ItemNoRow, 1).getValue() == true && ItemNoRow > 6 && ItemNo != '') {
    for (var a = 0; a < dbData.length; a++) {
      var row = dbData[a];
      if (dbData[a][0] != '') {
        if (orderTicked == true && dbData[a][2] == ItemNo) {
          dbRow = a + 1;
          break;
        }
      }
    }
    showDatePicker(); //This is calling the dialog box
    sheet.getRange(dbRow + 1, 31).setValue(AddRecord());//This is supposed to write the date to the destination
    sheet.getRange(dbRow + 1, 32).setValue("Yes");
    sheet.getRange(dbRow + 1, 38).setValue(new Date());
    sheet.getRange(dbRow + 1, 39).setValue(Session.getEffectiveUser().getEmail());
    SpreadsheetApp.flush();
    ItemNumberSheet.getRange(ItemNoRow, 1).setValue("false");
  }
}

This is the input/dialog box function:

function showDatePicker() {
  var template = HtmlService.createTemplateFromFile("DatePicker");

  var html = template.evaluate();
  html.setTitle("Enter an Order Date");
  //SpreadsheetApp.getUi().showSidebar(html);
  SpreadsheetApp.getUi().showModalDialog(html, "New Title for the window");
}

function AddRecord(orderdate){
  return orderdate;
}

Now this is the html + script:

<!--https://developers.google.com/speed/libraries-->
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">   
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
    <script>
    $( document ).ready(function() {
      $("#orderdate").datepicker();
    });
    function AddRecord(){
      var orderdate = document.getElementById("orderdate").value;
      google.script.run.AddRecord(orderdate);
      document.getElementById("orderdate").value = '';
    }
    </script>
  </head>
  <body>
    Order Date:<input type="text" id="orderdate" /> 
    <input type="button" value="Add" onclick="AddRecord()" />
  </body>
</html>
1 Answers

Modification points:

  • When showModalDialog is run, the process is released from the current function. I thought that this might be the reason of your issue.
  • And, when I saw your script, I thought that AddRecord() and "Yes", new Date() and Session.getEffectiveUser().getEmail() are put with setValues.
  • In this case, uncheck() can be used.
  • In order to send dbRow from onEdit to AddRecord executing by Javascript, I used PropertiesService.

When above points are reflected to your script, it becomes as follows.

Modified script:

Google Apps Script side:

Please modify onEdit and AddRecord as follows.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('DB');
  var dbData = sheet.getRange(2, 1, sheet.getLastRow(), 39).getValues();
  var ItemNumberSheet = ss.getSheetByName("To ORDER");
  var ItemNoRow = ItemNumberSheet.getActiveCell().getRow();//Getting row from active cell
  var ItemNoCol = 4;
  var ItemNo = ItemNumberSheet.getRange(ItemNoRow, ItemNoCol).getValue();
  var orderTicked = ItemNumberSheet.getRange(ItemNoRow, 1).getValue();
  var dbRow = 1;
  if (ss.getActiveSheet().getName() == 'To ORDER' &&
    ItemNumberSheet.getRange(ItemNoRow, 1).getValue() == true && ItemNoRow > 6 && ItemNo != '') {
    for (var a = 0; a < dbData.length; a++) {
      var row = dbData[a];
      if (dbData[a][0] != '') {
        if (orderTicked == true && dbData[a][2] == ItemNo) {
          dbRow = a + 1;
          break;
        }
      }
    }
    PropertiesService.getScriptProperties().setProperty("key", dbRow);
    showDatePicker();
  }
}

function AddRecord(orderdate) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('DB');
  var ItemNumberSheet = ss.getSheetByName("To ORDER");
  var ItemNoRow = ItemNumberSheet.getActiveCell().getRow();
  var v = PropertiesService.getScriptProperties().getProperty("key");
  var dbRow = v ? Number(v) : 1;
  sheet.getRange(dbRow + 1, 31, 1, 2).setValues([[orderdate, "Yes"]]);
  sheet.getRange(dbRow + 1, 38, 1, 2).setValues([[new Date(), Session.getEffectiveUser().getEmail()]]);
  ItemNumberSheet.getRange(ItemNoRow, 1).uncheck();
}

HTML & Javascript side:

From:
google.script.run.AddRecord(orderdate);
To:
google.script.run.withSuccessHandler(google.script.host.close).AddRecord(orderdate);
  • By this modification, when the button is clicked, the dialog can be closed.

Note:

  • From your question, I couldn't understand about the method for executing onEdit function. When you are using onEdit as the simple trigger, an error occurs because SpreadsheetApp.getUi().showModalDialog() cannot be run by the simple trigger. Please be careful this. And, when you might use onEdit with the installable OnEdit trigger, please rename the function name from onEdit to others and reinstall it. Because when onEdit function name is used as the installable trigger, the function is run 2 times. Also please be careful this.
  • In this modified script, it supposes that your dbRow created in your script is the correct value you expect. Please be careful this.

References:

Related