google-apps-scriptresetdatebox

Clear a DateBox or attach a DatePicker to a TextBox - Google Script


Is it possible to clear the value of a DateBox within a handler function? You can clear the value of a TextBox with just a simple app.getElementById('id').setValue(''); and you can set the value of a ListBox to a blank value by including listBox.addItem('') and then setting the ListBox to that item with app.getElementById('id').setItemSelected({item # for blank item}, true); but I have been unable to figure out a way to clear a DateBox. The goal is to have a simple form that allows users to fill it out and submit the form with a button then have the form auto-reset so the user can fill it out again for a new submission.

Alternatively, if, as I suspect, it is not yet possible to clear a DateBox (feature limitation), then I could simply use a TextBox. To make things look better (and to help users enter the date in the format the script expects), it would be nice if I could add a DatePicker to the TextBox but other than the old tutorial I found that generates the entire DatePicker function from scratch I haven't found a simple way to attach the new DatePicker GAS Class to a TextBox.

GAS Documentation: https://developers.google.com/apps-script/reference/ui/date-box

Date Picker 2 Tutorial - https://sites.google.com/site/appsscripttutorial/miscellaneous/date-picker-2

Much simplified sample code that hopefully provides enough information without forcing anyone to read 5 pages of code to get to what I'm trying to highlight. Note the comments especially in the submitHandler(e) function. This code can be copied into a blank (saved) spreadsheet for testing or you can replace the spreadsheet specific code to just return the app if you're more comfortable doing that.

//Opens the active spreadsheet and selects the first sheet.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var sheet = sheets[0];

function doGet() {
var app = UiApp.createApplication().setTitle('Test Form');
//Main panel with a 3x2 grid hosting labels and boxes.
var mainVP = app.createVerticalPanel();
  var mainCP = app.createCaptionPanel('Purchase Details');
    var mainG = app.createGrid(3, 2);
      var nameL = app.createLabel('Name');
      var nameT = app.createTextBox().setName('name').setId('name');
      var dateL = app.createLabel('Date');
      var dateD = app.createDateBox().setName('date').setId('date');
      var ownL = app.createLabel('Own/Lease');
      var ownC = app.createListBox().setName('ownLease').setId('ownLease');
//Submit button with auto-reset functionality.
    var subHP = app.createHorizontalPanel().setSize('100%', '40px');
      var subL = app.createLabel('Submitted').setId('subL').setVisible(false);
      var subB = app.createButton('Submit');
//Handler for button.
var subHandler = app.createServerHandler('submitHandler');
subHandler.addCallbackElement(mainVP);
subB.addClickHandler(subHandler);
//Add options to ListBox.
ownC.addItem('').addItem('Own').addItem('Lease');
//Place widgets on Grid.
mainG.setWidget(0, 0, nameL).setWidget(0, 1, nameT)
  .setWidget(1, 0, dateL).setWidget(1, 1, dateD)
  .setWidget(2, 0, ownL).setWidget(2, 1, ownC);
//Aligns the submit Button and Label to the right and adds them to their HPanel.
subHP
  .add(subL).setCellHorizontalAlignment(subL, UiApp.HorizontalAlignment.RIGHT)
  .add(subB).setCellHorizontalAlignment(subB, UiApp.HorizontalAlignment.RIGHT);
//Adds stuff to panels so it shows up.
mainCP.add(mainG);
mainVP.add(mainCP);
mainVP.add(subHP);
app.add(mainVP);
var doc = SpreadsheetApp.getActive();
doc.show(app);
}

function submitHandler(e) {
var app = UiApp.getActiveApplication();
//Set variables for each field in the Purchase Details CaptionPanel.
var name = e.parameter.name;
var date = e.parameter.date;
var ownLease = e.parameter.ownLease;
  //I use a sheet.appendRow() method here but for the purposes of this question-
  //we don't actually need the data, just need to clear it.
//Clears data from form to prepare for another submission.
app.getElementById('name').setValue('');
//app.getElementById('date') /* This is the field I need to clear somehow. */
app.getElementById('ownLease').setItemSelected(0, true);
//Displays the word "Submitted" when the Submit Button is clicked.
var label = app.getElementById('subL');
label.setVisible(true).setStyleAttribute('color', 'blue');
return app;
}

In summary:

  1. Is there a way to clear the value in a DateBox using a handler function and if so, how?
  2. If clearing a DateBox is impossible, how could I add a DatePicker to a TextBox and limit data entry to a specific format (so the TextBox wouldn't take "blahblahblah" as a valid date)?

Solution

  • Have you tried a third solution which would be to create a new datepicker widget and add it to your grid to replace the "filled one" ?

    here is a test where I do the "replace" in the submit handler but I guess it would be placed in a separate handler called 'submit again' for example...(but that is up to you) :

    I changed your submit confirmation to show the date returned by the new datePicker (just to test)

    //Opens the active spreadsheet and selects the first sheet.
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    var sheet = sheets[0];
    var popAttributes = {'padding':'10px','font-family':"Arial, sans-serif",'fontSize':'10pt','color':'#000099','background':'#ffffee','border-radius':'10px'}
    var btnAttributes = {'padding':'3px','font-family':"Arial, sans-serif",'fontSize':'10pt','border-radius':'6px'}
    
    function doGet() {
    var app = UiApp.createApplication().setTitle('Test Form');
    //Main panel with a 3x2 grid hosting labels and boxes.
    var mainVP = app.createVerticalPanel();
      var mainCP = app.createCaptionPanel('Purchase Details').setStyleAttributes(popAttributes);
        var mainG = app.createGrid(3, 2).setId('grid');
          var nameL = app.createLabel('Name');
          var nameT = app.createTextBox().setName('name').setId('name');
          var dateL = app.createLabel('Date');
          var dateD = app.createDateBox().setName('date').setId('date');
          var ownL = app.createLabel('Own/Lease');
          var ownC = app.createListBox().setName('ownLease').setId('ownLease');
    //Submit button with auto-reset functionality.
        var subHP = app.createHorizontalPanel().setSize('100%', '40px');
          var subL = app.createLabel('Submitted').setId('subL').setVisible(false);
          var subB = app.createButton('Submit').setStyleAttributes(btnAttributes);
    //Handler for button.
    var subHandler = app.createServerHandler('submitHandler');
    subHandler.addCallbackElement(mainVP);
    subB.addClickHandler(subHandler);
    //Add options to ListBox.
    ownC.addItem('').addItem('Own').addItem('Lease');
    //Place widgets on Grid.
    mainG.setWidget(0, 0, nameL).setWidget(0, 1, nameT)
      .setWidget(1, 0, dateL).setWidget(1, 1, dateD)
      .setWidget(2, 0, ownL).setWidget(2, 1, ownC);
    //Aligns the submit Button and Label to the right and adds them to their HPanel.
    subHP
      .add(subL).setCellHorizontalAlignment(subL, UiApp.HorizontalAlignment.RIGHT)
      .add(subB).setCellHorizontalAlignment(subB, UiApp.HorizontalAlignment.RIGHT);
    //Adds stuff to panels so it shows up.
    mainCP.add(mainG);
    mainVP.add(mainCP);
    mainVP.add(subHP);
    app.add(mainVP);
    var doc = SpreadsheetApp.getActive();
    doc.show(app);
    }
    
    function submitHandler(e) {
    var app = UiApp.getActiveApplication();
    //Set variables for each field in the Purchase Details CaptionPanel.
    var name = e.parameter.name;
    var date = e.parameter.date;
    var ownLease = e.parameter.ownLease;
      //I use a sheet.appendRow() method here but for the purposes of this question-
      //we don't actually need the data, just need to clear it.
    //Clears data from form to prepare for another submission.
    app.getElementById('grid').setWidget(1, 1, app.createDateBox().setId('date').setName('date'))
    app.getElementById('date') /* This is the field I need to clear somehow. */
    app.getElementById('ownLease').setItemSelected(0, true);
    //Displays the word "Submitted" when the Submit Button is clicked.
    var label = app.getElementById('subL');
    label.setVisible(true).setStyleAttribute('color', 'blue').setText(e.parameter.date);// to test the returned date value
    return app;
    }