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> 
没有评论:
发表评论