CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
flipnija513
flipnija513

Reputation: 23

Need a script that will populate datepicker in a google spreadsheet, when clicking an empty cell

From the research I've done, there doesn't seem to be a way by default to have the datepicker populated in an empty cell in a google spreadsheet. The cell has to already have a date in it for the datepicker to pop up. I'm very green when it comes to scripting. I understand most of the terminology but I'm unsure as to where to begin to create this. Could someone help me out? I would like the datepicker to populate in every cell in a specific column.

Upvotes: 0

Views: 1132

Answers (2)

pnuts
pnuts

Reputation: 59440

As (after an earlier version of this A, I now notice!) OP had found in Web Applications:

Apply validation to a chosen range with Criteria: Date and is a valid date then just double-clicking a cell in that range will call up a date picker.

Upvotes: 0

Mogsdad
Mogsdad

Reputation: 45710

I was going to point out Adding a datepicker in Google Spreadsheet on WebApps, but you found it.

As you have noted, a datepicker will only be present if there is a valid date in a cell. There is also no programmatic control over the datepicker widget.

This function will fill every cell in any range with the same default value, and that value can be any type supported by the spreadsheet.

/**
 * Fill a range with a default value.
 *
 * @param {Range} rangeFill A spreadsheet range to be filled
 * @param {Object} value The default value to fill the range with
 */
function rangeDefault( rangeFill, value ) {
  // Fill a two-dimensional array with default values
  var defaults = [[]];
  // Dimension the array to match the input range
  var height = rangeFill.getLastRow() - rangeFill.getRow() + 1;
  var width = rangeFill.getLastColumn() - rangeFill.getColumn() + 1;
  for (var row=0; row < height; row++ ) {
    defaults[row]=[];
    for (var col=0; col < width; col++ ) {
      defaults[row][col] = value;
    }
  }
  // Fill the range with the array contents.
  rangeFill.setValues(defaults);
}

To make it easy to use in a spreadsheet, here's a menu function. Just select the range you want filled, then pick "Date Fill" from the "Custom Menu", and the selection will be filled with today's date.

/**
 * Menu function - fill the currently selected range with today's date.
 */
function dateFill() {
  var today = new Date();
  var defaultDate = new Date( today.getFullYear(), today.getMonth()+1, today.getDate() );
  rangeDefault( SpreadsheetApp.getActiveSpreadsheet().getActiveRange(), defaultDate);
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Date Fill",
    functionName : "dateFill"
  }];
  sheet.addMenu("Custom Menu", entries);
};

Upvotes: 1

Related Questions