Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

187
Vistas
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 Respuestas
Responde la pregunta

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 Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda