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.
Advertisement
Answer
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