CopyDown Alternative?

[Eduardo Lozano Sierra]

Hello,

As many know, CopyDown is not working on those forms created with institutional emails, only with normal Gmail.

Does anyone know if there is another extension that does the same thing as copydown?

[Redgel Golez]

Following this. I'm using a personal gmail and have used copydown for years. Just noticing it stopped working today.

[Eduardo Lozano Sierra]

Me funcionó el siguiente Script a modo de ejemplo para hacer lo mismo que CopyDown.. con App Script y los Triggers..

function onFormSubmit(e) {

  // Obtiene la hoja de cálculo y la hoja activa

  var sheet = SpreadsheetApp.getActiveSheet();

  // Obtiene la última fila con datos

  var LastRow = sheet.getLastRow();

  // Recupera la fórmula de la celda A2 (suponiendo que las fórmulas están en la columna A)

  var formula = sheet.getRange("A2").getFormula(); // Obtiene el valor, no la cadena de fórmula (corregido)

  // Ajusta la referencia de la celda en la fórmula (si es necesario)

  if (formula.indexOf("B2") !== -1) { // Comprueba si hay una referencia a B2

    var adjustedFormula = formula.replace(/B2/g, "B" + LastRow);

  } else {

    // No se necesita ajuste si la fórmula no hace referencia a B2

    var adjustedFormula = formula;

  }

  // Aplica la fórmula ajustada a la celda correspondiente en la nueva fila

  var cell = sheet.getRange(LastRow, 1); // Suponiendo que las fórmulas están en la columna A

  cell.setValue(adjustedFormula); // Establece el valor (fórmula como cadena)

  //PEGAR COMO VALORES

  var valorf = cell.getValue();

  cell.setValue(valorf)

 

}

Modifíquenlo a necesidad.

[Joseph Schmidt]

Am I correct that this only does one column?

Joe Schmidt

https://www.youtube.com/c/JoeSchmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

--
You received this message because you are subscribed to the Google Groups "NV Copy Down Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-copy-down-ad...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-copy-down-add-on/c345c2bb-7afd-4546-a905-c615b8cb994bn%40googlegroups.com.

[M Psych Services]

Hi,

since copydown is down and it really disrupted my work, i decided to create a code with the help of chatgpt that works like copydown function, you may want to try it

function onFormSubmit(e) {

var sheetName = 'Form Responses 1';

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

var lastRow = sheet.getLastRow();

var formulaRow = 2; // Row that contains the formulas

var newRow = lastRow; // The new row where the formulas will be copied

// Get the range of the formulas in the second row

var formulaRange = sheet.getRange(formulaRow, 1, 1, sheet.getLastColumn());

var formulas = formulaRange.getFormulas()[0]; // Get formulas as strings

// Get the range of the new row

var newRowRange = sheet.getRange(newRow, 1, 1, sheet.getLastColumn());

// Loop through each formula

formulas.forEach(function(formula, columnIndex) {

if (formula) {

// Adjust the formula to refer to the new row

var adjustedFormula = formula.replace(/\$?([A-Z]+\d+)/g, function(match) {

if (match.startsWith('$')) {

// Preserve absolute reference

return match;

} else {

// Extract the column and row parts

var columnPart = match.match(/[A-Z]+/)[0];

var rowPart = match.match(/\d+/)[0];

// Adjust row reference to new row

var newRowIndex = parseInt(rowPart) + (newRow - formulaRow);

return columnPart + newRowIndex;

}

});

// Set the adjusted formula in the new row only if there was a formula in the original cell

if (adjustedFormula !== "") {

newRowRange.getCell(1, columnIndex + 1).setFormula(adjustedFormula);

} else {

// Clear the cell in the new row if there was no formula in the original cell

newRowRange.getCell(1, columnIndex + 1).clear({contentsOnly: true});

}

}

});

}

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-copy-down-add-on/CAPWPTOSFG7%2BKY6bW4zu5ph%2BXgxxR%3D1bZo%2BhMZi1EDzsGb%2BVmfw%40mail.gmail.com.

[Eduardo Lozano Sierra]

Yes, I did it just as a test to verify its functionality.

All code:

function onFormSubmit(e) {

  // Obtiene la hoja de cálculo y la hoja activa

  var sheet = SpreadsheetApp.getActiveSheet();

  // Obtiene la última fila con datos

  var lastRow = sheet.getLastRow() - 1;

  // Define las celdas de origen y destino para cada fórmula

  var sourceCells = {

    "Formula_Estado": "A2",

    "Formula_Placa": "B2",

    "Formula_Solicitud": "C2",

    "Formula_Archivos": "D2",

    "Formula_Solicitante": "E2",

    "Formula_Tiempos": "DI2"

  };

  var targetCells = {

    "Formula_Estado": sheet.getRange(lastRow, 1),

    "Formula_Placa": sheet.getRange(lastRow, 2),

    "Formula_Solicitud": sheet.getRange(lastRow, 3),

    "Formula_Archivos": sheet.getRange(lastRow, 4),

    "Formula_Solicitante": sheet.getRange(lastRow, 5),

    "Formula_Tiempos": sheet.getRange(lastRow, 38)

  };

  // Recorre las celdas de origen y destino

  for (var formulaName in sourceCells) {

    var sourceCell = sheet.getRange(sourceCells[formulaName]);

    var targetCell = targetCells[formulaName];

    // Obtiene la fórmula de la celda de origen

    var formula = sourceCell.getFormula();

    // Ajusta la referencia de la celda en la fórmula (si es necesario)

    var adjustedFormula = formula.replace(/2/g, lastRow);

    // Aplica la fórmula ajustada a la celda de destino

    targetCell.setValue(adjustedFormula);

    //PEGAR COMO VALORES

    var valorf = targetCell.getValue();

    targetCell.setValue(valorf)

  }

}

[Eduardo Lozano Sierra]

Thanks for sharing, I'm going to try it

[Simon Reynolds]

Yes, I have also been using copy down for response sheet for Forms on a personal

email - so it's not just institutional emails.

sr

--

You received this message because you are subscribed to the Google Groups "NV Copy Down Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-copy-down-ad...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-copy-down-add-on/6e65ec08-31d6-4efb-b881-76ce0e806898n%40googlegroups.com.

[M]

This seems to only work when it's manually triggered, any idea how to get it to run on each new row?