Identify the cell type and replace entry with default text/value-CodePudding

I have a spreadsheet where the user enters loads of data in a column.

I want to run a script that resets the user-entered data to defaults.

However, they need a different default value depending on the type of data that can be entered:

Dropdown add "Choose" this is one of the options in all dropdowns
Text entry see below
Checkmark/tick box remove the tick/checkmark

Some cells in the column have formulas, and I want it to do nothing for these.

For the text entry, I have three text types, so ideally would like them to have different defaults

date - show "add date" in cell
text - clear the text
number - enter zero

Any help would be appreciated.

CodePudding user response：

Description

This may not be all inclusive for DataValidation but it will handle the items you mention above. Note that the check for Date has to be done before number.

I've created a small test sheet with each value type.

I leave it up to you to put the values back.

Code.gs

function test() {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
    let range = sheet.getDataRange();
    range.uncheck();
    let values = range.getValues();
    values.forEach( row => {
        let value = row[0];
        if( value instanceof Date ) {
          row[0] = "add date";
        }
        else if( !isNaN(value) ) {
          row[0] = 0;
        }
        else if( typeof value === "string" ) {
          row[0] = "";
        }
      }
    );
    let rules = range.getDataValidations();
    rules.forEach( (row,iRow) => { row.forEach( (cell,iCell) => { if( ( cell != null ) && ( cell.getCriteriaType() != SpreadsheetApp.DataValidationCriteria.CHECKBOX ) ) values[iRow][iCell] = "Choose" } ) } );
    let formulas = range.getFormulas();
    formulas.forEach((row,iRow) => { row.forEach( (cell,iCell) => { if( cell !== "" ) values[iRow][iCell] = cell } ) } );
    console.log(values);
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

11:42:43 AM Notice Execution started 11:42:44 AM Info [ [ '' ], [ 0 ], [ 'add date' ], [ 'Choose' ], [ '=A2' ], [ 0 ] ] 11:42:44 AM Notice Execution completed

References