First off, I realize that there are ways to do this without using named ranges and scripts. Sadly, Google Docs imported ranges strongly disagrees so I need to find a way to make this work.
I think I’m nearly there with this script, but I lack the coding knowledge for even basic loops in GAS. I highlighted the related section of the spreadsheet in blue that is involved with the script and shared an example at this link:
On edit of a particular cell, I would like the following to happen:
- Grab a list of named ranges from a range of cells in a particular spreadsheet
- Update the ranges for those named ranges from an adjacent range of cells in the same spreadsheet
I understand the concept of using setNamedRange() one by one but not in a loop based on spreadsheet ranges. I would like this to be expandable, so that I can easily expand the number of ranges that I can update with this script.
function updateRanges() { var ss = SpreadsheetApp.getActive(); //range1 var updateRangeName = ss.getRange("B14").getValue(); var updateRangeValue = ss.getRange("C14").getValue(); ss.setNamedRange(updateRangeName,ss.getRange(updateRangeValue)); //range2 var updateRangeName = ss.getRange("B15").getValue(); var updateRangeValue = ss.getRange("C15").getValue(); ss.setNamedRange(updateRangeName,ss.getRange(updateRangeValue)); //range3 var updateRangeName = ss.getRange("B16").getValue(); var updateRangeValue = ss.getRange("C16").getValue(); ss.setNamedRange(updateRangeName,ss.getRange(updateRangeValue)); //range4 var updateRangeName = ss.getRange("B17").getValue(); var updateRangeValue = ss.getRange("C17").getValue(); ss.setNamedRange(updateRangeName,ss.getRange(updateRangeValue)); //range5 var updateRangeName = ss.getRange("B18").getValue(); var updateRangeValue = ss.getRange("C18").getValue(); ss.setNamedRange(updateRangeName,ss.getRange(updateRangeValue)); } // function onEdit(e) { if (e.range.getA1Notation() === 'C2') updateRanges(); }
Update 1
Figured out a working solution thanks to Yuri! Here is my final code. I also added a tweak so that the onEdit trigger is tied to a specific sheet.
function updateRanges() { var ss = SpreadsheetApp.getActive(); sheet = ss.getSheetByName("Template") var ranges = sheet.getRange('B14:C18').getValues(); for (var r in ranges) { var updateRangeName = ranges[r][0]; var updateRangeValue = ranges[r][1]; ss.setNamedRange(updateRangeName,ss.getRange(updateRangeValue)); } } function onEdit(e) { if (e.range.getSheet().getName() != "onEditSheet") { return } if (e.range.getA1Notation() === 'B1') updateRanges(); }
Advertisement
Answer
Something like this?
function updateRanges() { var ss = SpreadsheetApp.getActiveSheet(); var ranges = ss.getRange('B14:C18').getValues(); for (var r in ranges) { var updateRangeName = ranges[r][0]; var updateRangeValue = ranges[r][1]; ss.setNamedRange(updateRangeName,ss.getRange(updateRangeValue)); } } function onEdit(e) { if (e.range.getA1Notation() === 'C2') updateRanges(); }