Google Apps Script/Google Sheet Bug? Copying/Re-Setup Data Validation refering to named range does not work on duplicated Sheets

0
votes

SEE THIS EXAMPLE HERE

Situation: Data validation on cells that refer to a range via direct reference (A1Notation) and Data Validation on other cells that refer to a named range.

Works both.

When duplicating the sheet, both still work. However, when resetting the data validations manually to the same ranges or via my script, the data validation of the cell where the data validation refers via named range will not work and there is no way to set it up again by referring via named range either manually or by copying (.getDataValidation / .setDataValidation) via script.

Check out the example - run script function cp_dataValidation() either on Sheet1 and Copy of Sheet1 as an active sheet. Then click the data validation dropdowns in the cells.

enter image description here

Eventually, my goal is to perform a copy of the data validation (referring to a named range) from one range to another on a duplicated sheet via script.

function cp_dataValidation() {
  var sheet = SpreadsheetApp.getActiveSheet()

  //cell with data validation referring to a named range
  var named_range_tmpl = sheet.getRange("B2");
  //cell with data validation referring to a range via A1Notation
  var range_tmpl = sheet.getRange("C2");

  //target cells to copy dataValidation to
  var nr_target = sheet.getRange("D2");
  var r_target = sheet.getRange("E2");


  nr_target.setDataValidation(named_range_tmpl.getDataValidation());
  r_target.setDataValidation(range_tmpl.getDataValidation());


  Logger.log(JSON.stringify(named_range_tmpl.getDataValidation(), null, 2));
  Logger.log(JSON.stringify(range_tmpl.getDataValidation(), null, 2));
}
1
google-apps-scriptgoogle-sheetsgoogle-sheets-macros
About the data validation of the cell where the data validation refers via named range will not work, when I used your sample Spreadsheet and your script (run cp_dataValidation()), unfortunately, I couldn't replicate your situation. Can I ask you about the method for replicating your issue? By the way, can you add your script in your question? – Tanaike
Did you try the data validation drop downs on both sheets? – haemse
Thank you for replying and updating your question. I could replicate your situation. So I proposed a workaround as an answer. Could you please confirm it? If that is not the result you want, I apologize. – Tanaike

1 Answers

1
votes
  • You want to copy the Data Validation including the named range as the values.

If my understanding is correct, how about this workaround? It seems that nr_target.setDataValidation(named_range_tmpl.getDataValidation()) cannot be used for the named range of other sheet. So as a workaround, how about using the method of copyTo()?

Modified script:

var sheet = SpreadsheetApp.getActiveSheet();
var source = sheet.getRange("B2:C2");
var target = sheet.getRange("D2:E2");
source.copyTo(target, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION);
target.clearContent(); // or target.clear({contentsOnly: true}); // Added

Note:

  • Please test the script for both sheets.
  • If you want to copy both the value and Data Validation, please use source.copyTo(target) instead of source.copyTo(target, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION).

Reference: