CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsnamed-ranges
Adam Shapira
Adam Shapira

Reputation: 23

Google Apps Script - Loop to Update Multiple Named Ranges Based on Reference Table

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:

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();
}

Upvotes: 2

Views: 524

Answers (1)

Yuri Khristich
Yuri Khristich

Reputation: 14537

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();
}

Upvotes: 1

Related Questions