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

163
Vistas
Adjust Apps Script Function to check entire Column instead of single Row

I have an onEdit function that creates an array and uses Range.setValues to "move" that row to another worksheet.

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  if (r.getColumn() == 2 && r.getRow() == 2 && r.getWidth() == 1 && r.getHeight() == 1) {
    var sheetName;
    switch (e.value) {
      case "Pending":
        sheetName = "Pending";
        break;
      case "In Hand":
        sheetName = "In Hand";
        break;
    }
    if (sheetName) {
      const dest = e.source.getSheetByName(sheetName);
      const srcRange = src.getRange(r.getRow(),1,1,18);
      const formulas = srcRange.getFormulas()[0];
      const values =  srcRange.getValues();
      formulas.forEach((formula,i) => {
        if(formula !== '') values[0][i] = formula;
      });
      dest.getRange(dest.getLastRow()+1,1,1,18)).setValues(values);
      src.deleteRow(r.getRow();
    }
  }
}

The function works but at the moment it only checks Column 2 - Row 2 shown here:

enter image description here

If I change the following Status nothing happens:

enter image description here

I know this is because the function is not checking the entire column, it's only checking the value change in Active Sheet - Column 2 - Row 2.

What I need help with is to adjust the range and ensure the whole of Column 2 is checked - and if a status is changed in Row 5, the Row 5 data is moved - and so forth.

about 4 years ago · Juan Pablo Isaza
2 Respuestas
Responde la pregunta

0

Replace r.getRow() == 2 by r.getRow() >= 2 in the following line

if (r.getColumn() == 2 && r.getRow() == 2 && r.getWidth() == 1 && r.getHeight() == 1) {

The above because the first row has the column headers and it's very unlikely that you want that the headers row be moved in case that column header of column B be edited.

Resources

  • https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators
about 4 years ago · Juan Pablo Isaza Denunciar

0

The if condition in your code will only works if the user edited a cell in Row 2 Column B of your sheet due to this condition r.getRow() == 2.

If you want to check the entire column starting from row 2, change your code

From:

if (r.getColumn() == 2 && r.getRow() == 2 && r.getWidth() == 1 && r.getHeight() == 1) {

To:

if (r.getColumn() == 2 && r.getRow() > 1 && r.getWidth() == 1 && r.getHeight() == 1) {

This will ensure that the script will only apply the "move" logic if a cell is edited in the column B starting from row 2.

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