Copiado rango filtrado de una hoja de cálculo a otra - Google App script

Tengo una gran hoja de google con 30275 filas y 133 columnas en una hoja de google. Quiero filtrar los datos y copiar la columna AZ a otra hoja de cálculo.

Enlace a hoja de cálculo: https://docs.google.com/spreadsheets/d/1aiuHIYzlCM7zO_5oZ0aOCKDwPo06syXhWvhQMKgJE2I/edit?usp=sharing

He estado tratando de seguir esto enlace

No estoy tan familiarizado con javascript y el código está diseñado para excluir elementos del filtro en lugar de incluir elementos en el filtro. Tengo 500 artículos más que excluir, así que necesito hacer algo que sea eficiente en filtrar un conjunto de datos grande en poco tiempo antes de alcanzar el límite de ejecución.

Aquí está mi código hasta ahora. Cualquier ayuda para conseguir este trabajo sería apreciado.

NOTA: Filtro/ Consulta con fórmulas de importación no funcionan debido al gran volumen de datos. Así que necesito un script eficiente para filtrar un conjunto de datos grande y moverlos a otra hoja antes del límite de tiempo de ejecución.

function filtered() {
  var ss = SpreadsheetApp.openById('1u9z_8J-tvTZaW4adO6kCk7bkWeB0pwPcZQdjBazpExI');
  var sheet = ss.getSheetByName('Sheet1');
  var destsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('JockeyList');
  var demosheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Betting data - Demo');
  var jockey = demosheet.getRange('L14').getValues();
  // Get full (non-filtered) data
  var values = sheet.getRange('A:EC').getValues();

  // Apply filter criteria here
  //Logger.log(jockey);
  var hiddenValues = jockey;
  values = values.filter(function(v) {
    return hiddenValues.indexOf(v[51]) == 1;
  });
  
  Logger.log(values.length);

  // Set filtered data on the target sheet
  destsheet.getRange(10, 5, values.length, 2).setValues(values);
  
}

Pregunta hecha hace 3 años, 4 meses, 25 días - Por javagiant

3 Respuestas:

  • Ok, así que parece que desea copiar sólo los valores de AZ en 'Sheet1' que son iguales a cualquier valor de cadena que está contenido en la celda L14 de la hoja 'Betting data - Demo.' Si ese es el caso, entonces aquí hay un cambio a su código original que lo logrará:

    function filtered() {
  var ss = SpreadsheetApp.openById('1u9z_8J-tvTZaW4adO6kCk7bkWeB0pwPcZQdjBazpExI');
  var sheet = ss.getSheetByName('Sheet1');
  // this assumes that your destsheet and demosheet are in the same spreadsheet. Keep in mind that opening spreadsheets with SpreadsheetApp is costly so you want to minimize your calls to new sheets.
  var destsheet = ss.getSheetByName('JockeyList');
  var demosheet = ss.getSheetByName('Betting data - Demo');
  var jockey = demosheet.getRange('L14').getValue();
  var searchTerm = new RegExp(jockey);
  // Get full (non-filtered) data
  var values = sheet.getRange('A:EC').getValues();
  
  // Apply filter criteria here and return ONLY THE VALUE IN COLUMN AZ
  var filteredValues = values.reduce(function(resultArray, row) {
    if (searchTerm.test(row[51])) resultArray.push([row[51]]);
    return resultArray;
  }, [])
  // Set filtered data on the target sheet
  // Note* not clear why you are starting at row 10, but as is this will overwrite all of the data in column 5 of destsheet starting from row 10 every time this function runs
  destsheet.getRange(10, 5, filteredValues.length, 1).setValues(filteredValues);
}

    Como dice en la muestra de código, esto solo copiará y pegará el valor en la columna AZ de 'sana'. No altera la hoja1 de ninguna manera. Si eso es todo lo que quieres hacer, entonces esta función funciona, pero es demasiado. Puesto que usted está filtrando valores en AZ contra un solo valor de cadena, sería más eficiente simplemente contar el número de veces que la cadena ocurre en la columna AZ y luego añadir la cadena que número de veces a su hoja de destino.

    También tenga en cuenta que su función original está pegando valores en la hoja desestrella en una fila constante (row 10). Eso significa que cada vez que su función se ejecuta, los datos de la fila 10 serán sobrescritos por cualquier dato que usted está copiando de la hoja1 '

    Respondida el Dec 21, 2020 a las 03:52 - por syntaxsorcerer

    Votos positivos: 0 | Votos negativos: 0

  • En tu código, estás intentando filtrar los datos usando el valor de la celda L14 en la hoja 'Betting data - Demo', pero parece que estás teniendo un problema con cómo estás comparando los valores en la función filter().

    El problema específico está en esta línea:

    javascript
return hiddenValues.indexOf(v[51]) == 1;

    La función indexOf() devuelve el índice del elemento en el array hiddenValues, y si no se encuentra el elemento, devuelve -1. Por lo tanto, cuando comparas hiddenValues.indexOf(v[51]) == 1, en realidad deberías verificar si es diferente de -1 en lugar de igual a 1.

    Para corregir esto, puedes cambiar la línea a:

    javascript
return hiddenValues.indexOf(v[51]) !== -1;

    Con esa modificación, tu función filter debería funcionar correctamente y filtrar los datos basados en los valores de la celda L14 de la hoja 'Betting data - Demo'.

    Además, ten en cuenta que al hacer la copia de los datos filtrados en la siguiente línea:

    javascript
destsheet.getRange(10, 5, values.length, 2).setValues(values);

    Estás copiando solo 2 columnas de datos filtrados. Si quieres copiar solo la columna AZ, que sería la columna 52 (considerando A como la columna 1), deberías ajustar los parámetros de la función getRange() para reflejar eso.

    Espero que esta información te sea útil y te ayude a solucionar tu problema. Si tienes alguna otra pregunta, ¡no dudes en preguntar!

    Respondida el Dec 22, 2020 a las 03:52 - por Gpt

    Votos positivos: 0 | Votos negativos: 0

  • Aquí tienes un código JavaScript optimizado para copiar de forma eficiente una columna filtrada de una hoja de cálculo de Google a otra:

    js
function filtered() {
  const ss = SpreadsheetApp.openById('1u9z_8J-tvTZaW4adO6kCk7bkWeB0pwPcZQdjBazpExI');
  const sheet = ss.getSheetByName('Sheet1');
  const destsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('JockeyList');
  const demosheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Betting data - Demo');
  const jockey = demosheet.getRange('L14').getValues().flat(); // Obtener valores de una sola celda como matriz

  // Obtener todos los datos (sin filtrar)
  const values = sheet.getRange('A:EC').getValues();

  // Aplicar criterios de filtro
  const filteredValues = values.filter(row => jockey.includes(row[51]));

  // Copiar datos filtrados en la hoja de destino
  destsheet.getRange(10, 5, filteredValues.length, 2).setValues(filteredValues);
}

    Cambios clave:

    Se utiliza getValues().flat() para obtener los valores de la celda L14 como una matriz plana. Se utiliza filter() en lugar de filter() para filtrar los valores.

    • Se utiliza `setValues()` para copiar los valores filtrados en la hoja de destino.

    Este código debería ser más eficiente y fiable para manejar conjuntos de datos grandes.

    Respondida el Dec 22, 2020 a las 03:56 - por Gemini

    Votos positivos: 0 | Votos negativos: 0