How to reject data value not with my date format using apps script?

Question

I have a Google Sheet which I gave Edit permission for few users. first raw is protected with the titles. Column A is a date that I want to have a format of dd/mm/yyyy only. I managed to force the format if the user manually enters a date with another format, but I have a problem if someone pastes a value to the cell, and then it can get any format they paste. How can I make sure it is always with format dd/yy/yyyy? I don't want to use an apps script that formats the date to my dd/MM/yyyy format, but I want to reject it if the format isn't dd/MM/yyyy.

I tried the solution from this old question: Custom date validation in google sheets with popup alert but this isn't what I am looking for as I don't want to popup also if this is a valid date but not with my format. I have a simple way to reject it if the user manually enters the date (I use for the format of the cell to dd/MM/yyyy). My problem is only with users who paste values into it.

function onEdit(e) {
  let range = e.range;
  let col = range.getColumn();
  if (col == 1) {
    let value = range.getValue();
    let newDate = new Date(value);
    let timeCheck = newDate.getTime();
    let isTime = (timeCheck === timeCheck);
    if (!isTime) {
      let message = "'" + value + "' is not a date";
      let ui = SpreadsheetApp.getUi();
      ui.alert(message);
      range.setValue("");
    } else {
      range.setValue(newDate).setNumberFormat("dd/MM/yyyy");
    }
  }
}

Answer 1

There is no built-in way to prevent that a cell editor change the cell format and if you don't want to use Google Apps Script to re-apply the desired cell format then your only option is to protect the cell to prevent editors edit it and use an indirect method to add the value to the cell i.e. by using a web-app or an installable trigger which might be overkilling.

Consider the following workaround: Ask editors that instead of entering the date directly into the cell to use a custom menu to open a prompt to enter the date. An onEdit function is used to reject changes done directly into the cell and re-apply the cell formatting. Note: This will be executed only when the editors doesn't follow the directions about how to enter the date.

// Cell to validate
const sheetName = 'Sheet1';
const rowStart = 1;
const columnStart = 1;

/**
 * Use a custom menu to indirectly enter data into the cell to validate
 */
function onOpen(e) {
  SpreadsheetApp.getUi().createMenu('Enter data')
    .addItem('Date', 'enterDate')
    .addToUi();
}

/**
 * Use onEdit to prevent that the cell to validate is directly edited
 */ 
function onEdit(e) {
  if (e.range.rowStart === rowStart && e.range.columnStart === columnStart) {
    if (e.oldValue) {
      e.range
        .setValue(e.oldValue);
    } else {
      e.range.getCell(1,1).clearContent();
    }
    /** Re-apply cell formatting as it might be lost when a user paste format */
    e.range
      .setNumberFormat('YYYY-MM-dd')
      .setBackgroundColor('#46bdc6');
    /** Provide feedback to the user */
    SpreadsheetApp.getUi().alert('No, no, no. This cell should only be edited by using the custom menu');
  }
}

/**
 * Use a prompt for data entering. 
 * If the input can't be converted into a date, then throw an error, 
 * otherwise write the date into the cell to validate.
 */
function enterDate() {
  const ui = SpreadsheetApp.getUi();
  const input = ui.prompt('Enter date in YYYY-MM-dd format');
  if (input.getSelectedButton() === ui.Button.CLOSE) return;
  const date = new Date(input.getResponseText());
  /**
   * The date will be displayed according to the cell number format
   * so we just need to validate the entered value was converted into 
   * a valid date 
   */
  if (isNaN(date)) throw new Error('Invalidad date');

  const offset = date.getTimezoneOffset() * 60 * 1000;
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.getSheetByName(sheetName)
    .getRange(rowStart, columnStart)
    .setValue(new Date(date.getTime() + offset).toLocaleDateString());
    
}

Related

• How can I prompt an abbreviate date with SpreadsheetApp.promp() in Google Sheets Scripts