Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

185
Views
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:

  • 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();
}
about 4 years ago · Juan Pablo Isaza
1 answers
Answer question

0

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();
}
about 4 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!