How to make data validation without $ in criteria range?

[CUBE Planning & Statistics]

Hi guys, I have a problem with GS code:

function myFunction() {

  var app = SpreadsheetApp;

  var spreadsheet = app.getActiveSpreadsheet();

  var sheet = spreadsheet.getActiveSheet();

  sheet.getRange('A1').setDataValidation(SpreadsheetApp.newDataValidation()

  .setAllowInvalid(false).requireValueInRange(sheet.getRange("B1:Z1"), true).build());

}

it gave me a criteria range =Sheet1!$B$1:$Z$1 and I need =Sheet1!B1:Z1.

I couldn't find how to change it by code, only manually (but my task is very massive and it's impossible to change manually this conditions in hundrets of sheets).

[Clark Lind]

Can you define it as a variable ahead of time? See if this helps at all:

function myFunction() {

  var app = SpreadsheetApp;

  var spreadsheet = app.getActiveSpreadsheet();

  var sheet = spreadsheet.getActiveSheet();

  var range = sheet.getRange("B1:Z1");

  sheet.getRange('A1').setDataValidation(SpreadsheetApp.newDataValidation()

  .setAllowInvalid(false).requireValueInRange(range, true).build());

}

[CUBE Planning & Statistics]

Yes, I can define it as a variable, but it doessn't solve the problem (

Result of this code:

I really can't understand why GS adds this signs $.. I think I should try to send message to support...

четверг, 30 сентября 2021 г. в 20:00:37 UTC+5, cwl...@gmail.com:

[Clark Lind]

I think because you are using a dropdown, it must refer to an absolute range. Also, notice the text between "List from a range" and the checkmark:  "Tip: use absolute ranges...", so maybe it is required.

[CUBE Planning & Statistics]

Funny thing is, that I can manually remove $ and it works perfectly. I can't understand how to do it by script. 

As a half-ass solution, I can manually change range  from =Sheet1!$B$1:$Z$1 to =B1:Z1 and than copy data validation property by script from this cell to all my sheets in the document, it works fine, I have relative links. 

Bad things that copying cell doesn't work between documents the same way.

But! Another half-ass solution is to copy one whole sheet with this manually healed relative data validation from one document to another ;-) so in my other document I will have data validation with relative links and can use it in my another doc.

суббота, 2 октября 2021 г. в 17:39:00 UTC+5, cwl...@gmail.com:

[Israel Zilberman]

I think it is an unexpected behavior, because it is something basic that a user can do manually - but the script can't...

Compared Excel VBA accuracy, it is surprising.

Google representative answered that it is an expected behavior.

https://issuetracker.google.com/issues/182575830?pli=1