CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
MorningSleeper
MorningSleeper

Reputation: 415

How to remove data validations?

I want to remove all data validations from a sheet, tried following but it does not get rid of them. Anyway to do this through apps script?

var ss = SpreadsheetApp.getActiveSpreadsheet();
var accountSheet = ss.getSheetByName("Account A");
accountSheet.getRange(1,1,274,61).clear();

Upvotes: 13

Views: 15574

Answers (2)

Juuso Nykänen
Juuso Nykänen

Reputation: 448

A more generic answer to remove all data validations from the whole sheet (remove from all cells in sheet):

mySheet.getRange(1,1, mySheet.getMaxRows(), mySheet.getMaxColumns()).setDataValidation(null);

You could in theory use this less verbose version (might be faster because it affects a smaller range of cells):

mySheet.getDataRange().setDataValidation(null);

However, there may be empty cells outside the DataRange that do have validations and those validations will not be removed (they are outside the scope of DataRange, won't be selected by DataRange). Hence, you should use the first version I mentioned for more robust code: it selects all the cells in the sheet and removes validations.

Upvotes: 0

msullivan
msullivan

Reputation: 296

accountSheet.getRange(1,1,274,61).setDataValidation(null)

That should do the job.

Upvotes: 28

Related Questions