google apps script - sheets - Detectar el usuario que inserta una fila o columna en una hoja de cálculo de google y reaccionar en un script

google spreadsheet macro

Question

En Google Apps Script, una de las herramientas básicas es el desencadenador onEdit en una hoja de cálculo, que nos permite detectar cuándo un usuario edita una celda y reaccionar ante ella.

¿Qué tal si un usuario inserta una fila o columna? ¿Hay alguna forma de detectar eso?

¿Eso desencadenaría un onEdit? Si es así, supongo que mantener en el ScriptDb un recuento de la cantidad de filas o columnas, y luego consultar cada vez, sería muy costoso en el tiempo, ya que getMaxRows () ya es bastante lento, y llegar a ScriptDb es como bien.

Qué piensas ?

Answer 1

Estaba teniendo problemas con esto hasta que le di permisos al script. De lo contrario, la funcionalidad de PropertiesService no funcionará. Una vez que lo hice, pude detectar qué fila se había insertado con el siguiente código:

var props = PropertiesService.getUserProperties();

function onEdit(e) {
  props.setProperty("firstRow", e.range.getRow());
  props.setProperty("lastRow", e.range.getLastRow());
}


function onChange(e){
if(e.changeType=="INSERT_ROW")
    SpreadsheetApp.getUi().alert("Inserted Rows: " +
                                 props.getProperty("firstRow") + 
                                 " - " +
                                 props.getProperty("lastRow"));
}

Answer 2

Google agregó un evento "Al cambiar" que detecta la inserción / eliminación de filas / columnas junto con otros tipos de cambios, los tipos que puede ver aquí debajo de los valores permitidos para changeType . A continuación, se incluyen las instrucciones desde aquí que detallan cómo agregar un desencadenador a su proyecto, para que pueda llamar a su función cuando se produce el evento "Al cambiar".

Para crear manualmente un activador instalable a través de un diálogo en el editor de scripts, siga estos pasos:

1. Desde el editor de scripts, elija Edición> Activadores del proyecto actual .
2. Haga clic en el enlace que dice: No hay configuración de disparadores. Haga clic aquí para agregar uno ahora .
3. En Ejecutar , seleccione el nombre de la función que desea activar.
4. En Eventos , seleccione Accionado por tiempo o la Aplicación de Google a la que está vinculado el guión (por ejemplo, Desde hoja de cálculo ).
5. Seleccione y configure el tipo de desencadenador que desea crear (por ejemplo, un temporizador de hora que se ejecuta cada hora o un activador de apertura ).
6. Opcionalmente, haga clic en Notificaciones para configurar cómo y cuándo se lo contactaremos por correo electrónico si falla la función desencadenada.
7. Haga clic en Guardar .

En el paso 4 seleccionaría De la hoja de cálculo y en el paso 5 seleccionaría Al cambiar . Eso debería tener el efecto que estás buscando. También hay opciones para agregar desencadenadores mediante programación y solicitar autorización, si está intentando usar esto en un complemento que se distribuirá a los usuarios. Ambos se detallan en la documentación de los activadores instalados .

Answer 3

Hay otra forma que acabo de usar recientemente. Cada vez que se activa onEdit (), devuelve un objeto de evento (e) que le brinda información valiosa sobre lo que está sucediendo.

Por ejemplo, te da el rango, que puedes recuperar de e.range. Desde allí puede realizar transversales de muchas maneras diferentes y conocer, por ejemplo, qué fila se está editando. Pero también hay más datos útiles en el objeto e. Le da el "valor antiguo" ( e.oldValue ) de la celda que ha editado y el nuevo valor ( e.value ).

Una forma posible de combinar toda esta información sería obtener el rango correspondiente a la fila que está editando, luego verificar si todas las celdas están vacías (pero la que acaba de editar) y si no hay un valor anterior.

Eso no corresponde necesariamente a la última fila de la hoja de cálculo, sino a una fila vacía. Si es coherente con la forma en que llena sus datos, esto podría funcionar para usted:

//val = inserted value (e.value);
//old = old Value (e.oldValue);
//col = number of column being edited
//arr = array with the indexes of the columns that should be completed so as to make a new row [0,1,2...n]
function isInsert(old, val, col, arr){
   if((typeof val != "object")&&!old&&(arr.some(isNotEmpty, col)))
     return true;
   else
     return false;
}

function isNotEmpty(el){
   if(this == el)
     return true;
}

Answer 4

He estado jugando con onEdit y onChange. La respuesta onEdit le permite acceder a las filas que fueron editadas. Desafortunadamente, la respuesta onChange no te permite hacer esto. Entonces, para una solución robusta, parece que necesita apelar a ambos factores desencadenantes. Si su hoja no requiere filas / columnas vacías, la secuencia de comandos siguiente elimina las filas / columnas agregadas recientemente, borra todas las filas / columnas en blanco (en caso de que el usuario agregue más filas / columnas), luego advierte al usuario que no pueden agregar filas o columnas:

//////////////////////
// Global Variables //
//////////////////////

var SHEET = SpreadsheetApp.getActiveSheet();
var PROPERTIES = PropertiesService.getScriptProperties();

////////////////////
// Event Triggers //
////////////////////

/**
 * Track original sheet row/column count and register onChange trigger.
 */
function onOpen()
{
    // Set original dimensions
    PROPERTIES.setProperty(''rows'', SHEET.getMaxRows());
    PROPERTIES.setProperty(''columns'', SHEET.getMaxColumns());

    // Create onChange trigger
    ScriptApp
        .newTrigger(''deleteNewRowsAndColumns'')
        .forSpreadsheet(SpreadsheetApp.getActive())
        .onChange()
        .create();
}

/**
 * If new rows or columns were added to the sheet
 * warn the user that they cannot perform these
 * actions and delete empty (new) rows and columns.
 *
 * @param e
 */
function deleteNewRowsAndColumns(e)
{
    switch(e.changeType) {
        case ''INSERT_COLUMN'':
            removeEmptyColumns();
            warn();
            break;
        case ''INSERT_ROW'':
            removeEmptyRows();
            warn();
            break;
        default:
            return
    }
}

///////////////
// Utilities //
///////////////

/**
 * Remove empty columns.
 *
 * This function assumes you have a header row in which
 * all columns should have a value. Change headerRow value
 * if your headers are not in row 1.
 */
function removeEmptyColumns() {
    var maxColumns = SHEET.getMaxColumns();
    var lastColumn = SHEET.getLastColumn();
    if (maxColumns - lastColumn != 0) {
        // New column(s) were added to the end of the sheet.
        SHEET.deleteColumns(lastColumn + 1, maxColumns - lastColumn);
    } else {
        // New column was added in the middle of the sheet.
        // Start from last column and work backwards, delete
        // first column found with empty header cell.
        var headerRow = 1;
        var headers =  SHEET.getRange(headerRow, 1, 1, lastColumn).getValues()[0];
        for (var col = lastColumn; col >= 1; col--) {
            if (headers[col -1] == '''') {
                SHEET.deleteColumn(col);
                // Since can only insert one column to the left
                // or right at a time, can safely exit here;
                break;
            }
        }
    }
}

/**
 * Remove empty rows.
 *
 * This function assumes that all rows should
 * have data in the first cell.
 */
function removeEmptyRows() {
    var maxRows = SHEET.getMaxRows();
    var lastRow = SHEET.getLastRow();
    if (maxRows-lastRow != 0) {
        // New row(s) were added to the end of the sheet.
        SHEET.deleteRows(lastRow + 1, maxRows - lastRow);
    } else {
        // New row was added in the middle of the sheet.
        // Start from last column and work backwards, delete
        // first empty column found.
        var values = SHEET.getRange(''A:A'').getValues();
        var startIndex = values.length - 1;
        for (var i = startIndex; i >= 0; i--) {
            if (values[i] && values[i][0] == '''') {
                SHEET.deleteRow(i + 1);
                // User can bulk add rows to the bottom of the file
                // but can only add 1 above or below at a time in the
                // middle of the file, so it''s safe to exit here.
                break;
            }
        }
    }
}

/**
 * Return user warning message about adding new rows and columns
 */
function warn()
{
    SpreadsheetApp.getUi().alert(''You cannot add new rows or columns.'');
}

Answer 5

Hay una serie de acciones de edición que no se desencadenan en onEdit() , esta no es una lista completa, hay muchas más exclusiones informadas :

• Envío de formularios Número 4568 .
• Insertar o eliminar filas o columnas Problema 1363
• Cambios en la apariencia (Fuente, Formato, Alineación, Bordes) Problema 2476
• Rango de relleno (por ejemplo, comience con 1,2,3, seleccione esas celdas y arrastre el ''mango'' para llenar más celdas con 4,5,6 ...) Número 399
• Valores escritos por scripts. Número 338
• Buscar / reemplazar el problema 1754
• Número 1568 , Número 1119

Si desea saber cuántas filas hay en una hoja de cálculo, esto requiere aproximadamente 120ms para ejecutarse:

var numCols = SpreadsheetApp.getActiveSheet().getRange("1:1").getLastColumn();
var numRows = SpreadsheetApp.getActiveSheet().getRange("A:A").getLastRow();

Ya he demostrado que es más rápido escribir un valor en una hoja que usar ScriptDB. Puede esperar un tiempo insignificante para escribir un rango pequeño, alrededor de 1 ms.

Por lo tanto, si puede detectar una fila o columna que se agrega, le costaría menos de 2 décimas de segundo registrar el cambio. Este onEdit() muestra una técnica para medir la extensión de una hoja de cálculo e informa los cambios en las dimensiones de la hoja. (Para probar, agregar o eliminar filas o columnas, realice una edición que desencadene onEdit() . También contiene temporizadores. No dude en experimentar con otras formas de medir y / o almacenar valores para ver qué funciona mejor para usted.

function onEdit() {
  // Use start & stop to time operations
  var start = new Date().getTime();

  // We want the size of the sheet, so will select ranges across and down the
  // whole sheet. Cannot use getDataRange(), as it selects only occupied cells.
  var numCols = SpreadsheetApp.getActiveSheet().getRange("1:1").getLastColumn()
  var numRows = SpreadsheetApp.getActiveSheet().getRange("A:A").getLastRow();

  var stop = new Date().getTime();
  var timeToMeasure = (stop-start);

  // Did things change?
  var oldSize = SpreadsheetApp.getActiveSheet().getRange("A1:B1").getValues();
  if (oldSize[0][0] != numCols || oldSize[0][1] != numRows) {
    // Yes, they did - Let''s store the new dimensions
    start = new Date().getTime();

    SpreadsheetApp.getActiveSheet().getRange("A1:B1").setValues([[numCols,numRows]]);

    var stop = new Date().getTime();
    var timeToStore = (stop-start);  

    Browser.msgBox("Sheet is "+numCols+" by "+numRows+"."
                  +" ("+timeToMeasure+"ms to measure, "+timeToStore+"ms to store.)");
  }
}