Sheets API Method spreadsheets.values.clear is clearing data validation when it shouldn't?

Question

I'm trying to clear all the values from a Google Sheets sheet, while keeping the formatting and data validation (e.g. dropdowns and checkboxes). The documentation for the spreadsheets.values.clear API method says,

Only values are cleared -- all other properties of the cell (such as formatting, data validation, etc..) are kept.

However, when I run this method, the data validations I have set up on the sheet are deleted.

I suppose I'm both trying to report a bug (the support website directs me to StackOverflow), and also soliciting any suggestions for how I might accomplish the "clear all values but leave formatting and data validation" task.

Same here unfortunately. It seems the docs are just incorrect. It clears everything. — dusoft
– dusoft, Commented Jun 27 at 11:18

Dan · Accepted Answer · 2025-01-24 00:02:56Z

0

I just tried it myself on bound script against a basic sheet with data in a few columns and rows and it worked fine. It cleared only the data and left the formatting and data validation. The function was as follows :

function myFunction() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  let range = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());
  range.clear();
}

Are you running this bound, or unbound? How are you selecting the range?

answered Jan 24 at 0:02

Dan

11 bronze badge

Sign up to request clarification or add additional context in comments.

2 Comments

Sam Jan 24 at 0:13

Hi Dan, thanks for this suggestion. I'm not running this using Google Apps Script - I'm using the Sheets v4 API, which has different behavior

Dan Jan 24 at 0:25

Whoops. Sorry I missed that. Does the behavior change whether you use A1 or R1C1 notation when specifying the range? Or is the result the same in either case?

Collectives™ on Stack Overflow

Sheets API Method spreadsheets.values.clear is clearing data validation when it shouldn't?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related