javascript - ¿Cuánto más rápido son las matrices que acceder a las celdas de Hojas de cálculo de Google dentro de Google Scripts?

arrays google-apps-script

Question

¿Cuánto más rápido es acceder y manipular una matriz en los scripts de Google que acceder y manipular datos desde una hoja de Google?

Aquí está la aplicación:

Estoy trabajando en un programa para crear un horario para los empleados. Mi estrategia es crear una lista de empleados disponibles para cada día, aleatorizar la lista y luego insertar un empleado aleatorio en cada espacio abierto uno por uno en función de parámetros específicos. Luego repita cada día de la semana hasta completar el horario.

A veces los parámetros no se cumplen dada la lista aleatoria, y necesito reiniciar el ciclo. En promedio, ejecutaré ~ 1100 iteraciones del ciclo hasta que se complete el cronograma. Si el GoogleScript se ejecuta> 6 min, se produce un tiempo de espera de la función y se detiene automáticamente.

Mi función es una combinación de acceso a datos de GoogleSheets y acceso a datos directamente desde matrices dentro de la función. En general, ¿habría una diferencia notable para volver a escribir mi función de modo que todos los datos se almacenen directamente y se manipulen dentro de una matriz?

Answer 1

La magnitud del impacto dependerá de la cantidad de lectura y escritura que esté haciendo. Si actualmente se usa una gran cantidad de transferencia de datos incremental, cambiar su enfoque puede generar un beneficio masivo.

Las llamadas a la API de hoja de cálculo generalmente tardan 0.01 segundos o más en completarse. Las llamadas que importan / exportan datos, o llaman subfunciones, tomarán más tiempo (por ejemplo, getDataRange , getValue(s) , setValue(s) ). Apps Script es lo suficientemente inteligente como para optimizar algunas llamadas sucesivas, pero si alterna lecturas y escrituras, entonces nada en el extremo de Google puede ayudarlo.
Puede ver estos datos de tiempo en el menú View -> Execution Transcript .

Mi sugerencia es mover cualquier fórmula de validación basada en celdas existente a funciones de script que operen con un nombre de empleado aprobado, un mapeo de objetos entre empleados y los turnos que ya han "trabajado", y el turno propuesto para trabajar. Luego, podría usar 1 llamada para importar la lista de disponibilidad de días de empleados, y para cada día, para cada turno, validar que un empleado disponible elegido al azar puede trabajarlo, dados sus turnos previos en el período de programación. Por último, volvería a escribir su objeto en la (s) hoja (s).

Script de sincronización (que se optimiza internamente por google debido a lecturas sucesivas)

function writeRand_(rows, cols)
{
  var datasheet = SpreadsheetApp.openById(ssid).getSheetByName(''Sheet1'');
  datasheet.getDataRange().clearContent();
  var rand = [];
  for(var row = 0; row < rows; ++row)
  {
    var data = [];
    for(var col = 0; col < cols; ++col)
      data.push(Math.random());
    rand.push(data);
  }
  datasheet.getRange(1, 1, rand.length, rand[0].length).setValues(rand);
  return datasheet;
}

function readAndTime()
{
  var rows = 50, cols = 8;
  var datasheet = writeRand_(rows, cols);
  // sum one-by-one
  var sum1 = 0;
  var startRangeSum = new Date().getTime();
  for(var row = 1; row <= rows; ++row)
    for(var col = 1; col <= cols; ++col)
      sum1 += datasheet.getRange(row, col).getValue()-0;
  var endRangeSum = new Date().getTime();
  // Read all, then sum.
  var sum2 = 0;
  var startArraySum = new Date().getTime();
  var inputs = datasheet.getDataRange().getValues();
  for(var row = 0; row < inputs.length; ++row)
    for(var col = 0; col < inputs[0].length; ++col)
      sum2 += inputs[row][col]-0;
  var endArraySum = new Date().getTime();
  Logger.log("Value count: " + rows * cols);
  Logger.log("Range sum: " + (endRangeSum - startRangeSum)/1000 + " sec. " + sum1);
  Logger.log("Array sum: " + (endArraySum - startArraySum)/1000 + " sec. " + sum2);
}

Lo anterior me da ~ .8s para el rango y .2s para la matriz, y ese .2s se debe esencialmente al tiempo que toma llamar a getDataRange() para hacer inputs

Answer 2

tehhowch dice correctamente =) Lo hago así.

/* The first couple of nested loops use only reading methods as well as based on this 
    they pick up cells whose value must be changed in the array for next loop
*/

for (var rowIndex = editedCell.getRow(); rowIndex <= wageGrid.getLastRow(); rowIndex++) {
  if (weBreak) break;

  for (var collIndex = (rowIndex == editedCell.getRow()) ? (editedCell.getColumn() + 1) : wageGrid.getColumn(); collIndex <= wageGrid.getLastColumn(); collIndex++) {

    var cell = aSheet.getRange(rowIndex, collIndex);
    var cellFontColor = cell.getFontColor();
    var cellYearAndMonth = aSheet.getRange(rowIndex, 1).getValue();
    var cellDay = aSheet.getRange(1, collIndex).getValue();

    if (cellFontColor == "#ff0000") {

      weBreak = true;
      break;
    }
    if (aSheet.getRange(rowIndex, 1).getValue().getMonth() == cellYearAndMonth.getMonth()) {

      cellsToSetValue.push(cell);
    }
  }
}

//the next loop has an array of cell and perform only "set" methods

for (var cellIndex = 0; cellIndex <= cellsToSetValue.length; cellIndex++) {

  cellsToSetValue[cellIndex].setValue(evt.value);
}

por lo tanto, solo hay dos retrasos cuando la API optimiza el trabajo de los métodos "obtener" y "establecer" y entre ellos todo sucede soportable rápidamente)