Google-sheets – Create a custom DateTimePicker in Google Sheets

google sheetsgoogle-apps-script

In a sheet I have a column to enter a datetime value that would later be checked by a script to create a trigger.
I would like to set a picker in that column to ease selection and avoid errors.

I know Google Sheets offers an option in Data Validation for date but not for time neither datetime.

However, I found this class in the Google Apps Script API

https://developers.google.com/apps-script/reference/card-service/date-time-picker

But I just do not know how to implement it in a sheet (e.g. with OnEdit() or SetDataValidation()) so any time I press enter any cell in that column the picker is called to select datetime and set cell value to that selection.

Best Answer

The referred class belongs to the Card Service which is only available for G Suite add-ons.

To build a custom date picker for Google Sheets you could use the HTML Service. For further details see https://developers.google.com/apps-script/guides/html

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.

Screenshot

find
enter image description here

search enter image description here

result
enter image description here

Example

I've created an example file for you: get cell row and column index

Google-sheets – Google sheets recently changed American datetime format, how to change it back

In my experience, the cells remember any format they were given. Once a column is formatted M/d/yyyy it should stay that way.

There is no setting (other than the country setting) that controls the default date format. But entering the following script in the Script Editor (under Tools) will automatically format any dates you enter or edit from now on as M/d/yyyy.

function onEdit(e) {
  if (e.range.getNumberFormat() == 'm/d') {
    e.range.setNumberFormat('M/d/yyyy');
  }
}

Technical note: M/d/yyyy means 6/27/2016, while MM/dd/yyyy is 06/27/2016. If you prefer leading zeroes, change the setNumberFormat accordingly.

Another technical note: Google using m/d instead of M/d is inconsistent with SimpleDateFormat specification that they otherwise follow. This is something they decided to do a while back, to allow m instead of M in date formats.

Best Answer

Related Solutions

Google-sheets – Fetch cell row and column indices in Google Sheets

Code

Explained

Screenshot

Example

Google-sheets – Google sheets recently changed American datetime format, how to change it back

Related Topic