google-apps-script - scripts - no se ha encontrado ningún elemento con la id proporcionada o no tienes permiso para acceder a él

Script para resumir datos que no se actualizan

Question

Tengo una hoja de cálculo de Google de datos de la hoja de tiempo; tiene una hoja para cada mes, cada hoja tiene una gran cantidad de bloques de columna, un bloque por cliente.

He creado una hoja de resumen que va y obtiene el total para cada cliente y lo muestra en una lista:

function getClientTotals(sheetname, colcount)
{  
  colcount = colcount ? colcount : 6;
  var res;      
  var ss = SpreadsheetApp.openById(''myid_goes_here'');
  if(ss)
  {
    res = [];
    var totrow = ss.getRange(sheetname + ''!A1:ZZ1'').getValues()[0];
    for(var i = 0; i < totrow.length; i += colcount)
    {
      res.push([totrow[i], totrow[i + colcount - 1]]);
    }
  }   
  return res;
}

A continuación, agregué una celda a mi hoja de resumen que contiene =getClientTotals($C$7,$C$8) que transmite el nombre de la hoja del mes y el número de columnas para cada cliente (en el caso de modificaciones de "esquema").

Todo esto funciona bien, sin embargo, no se actualiza cuando se cambian los datos de origen. He agregado un activador onEdit ; sin alegría. Se actualiza si va al editor de scripts y pulsa Guardar, pero eso no es útil. ¿Me estoy perdiendo de algo?

Answer 1

Te estás perdiendo la fastidiosa función de error de almacenamiento en caché. Funciona de esta manera:

Google considera que todas sus funciones personalizadas dependen únicamente de los valores de sus parámetros directamente para devolver sus resultados (opcionalmente puede depender de otros datos estáticos).

Dado este prerrequisito, pueden evaluar sus funciones solo cuando cambia un parámetro. p.ej

Supongamos que tenemos el texto "10" en la celda B1, luego en alguna otra celda =myFunction(B1)

myFunction se evaluará y se recuperará su resultado. Luego, si cambia el valor de la celda B1 a "35", la personalización se volverá a evaluar como se esperaba y el nuevo resultado se recuperará normalmente. Ahora, si cambia la celda B1 nuevamente al "10" original, no hay reevaluación, el resultado original se recupera inmediatamente de la memoria caché.

Entonces, cuando usa el nombre de la hoja como parámetro para recuperarlo dinámicamente y devolver el resultado, está rompiendo la regla de almacenamiento en caché.

Desafortunadamente, no puedes tener funciones personalizadas sin esta increíble función. Por lo tanto, tendrá que cambiarlo para recibir los valores directamente, en lugar del nombre de la hoja, o no usar una función personalizada. Por ejemplo, podría tener un parámetro en su secuencia de comandos diciéndole dónde deberían ir los resúmenes y hacer que un onEdit actualice siempre que haya un total de cambios.

Answer 2

otra solución al problema del almacenamiento en caché

tener una variable ficticia en tu método. pasar

Filter(<the cell or cell range>,1=1)

como el valor de ese parámetro.

p.ej

=getValueScript("B1","B4:Z10", filter(B4:Z10,1=1))

la salida del filtro no se usa. sin embargo, indica a la hoja de cálculo que esta fórmula es sensible al rango B4: Z10.

Answer 3

Como dijo @Brionius puso un argumento dinamic extra sobre la función. si usa now () puede tener problemas de tiempo de espera para que la actualización sea un poco más lenta ...

cell A1 = int(now()*1000)
cell A2 = function(args..., A1)

Answer 4

Lo que podría hacer es configurar otra celda en alguna parte de la hoja de cálculo que se actualizará cada vez que se agregue una hoja nueva. Asegúrese de que no se actualice para cada cambio, sino solo cuando desee hacer el cálculo (en su caso cuando agrega una hoja). Luego pasa la referencia a esta celda a su función personalizada. Como se mencionó, la función personalizada puede ignorar este parámetro.

Answer 5

Tuve un problema similar al crear un tablero para el trabajo. La solución de Chamil anterior (es decir, el uso de la función Filtro de hoja pasada como el valor de una variable ficticia en su función) funciona bien, a pesar del comentario más reciente de Arsen. En mi caso, estaba usando una función para monitorear un rango y no pude usar el filtro en el mismo rango ya que creó una referencia circular. Así que acabo de tener una celda (en mi caso E45 en el código a continuación) en la que cambié el número cada vez que quería que mi función se actualizara:

=myFunction("E3:E43","D44",filter(E45,1=1))

Como indicó Chamil, el filtro no se usa en el guión:

function myFunction(range, colorRef, dummy) {
  variable ''dummy'' not used in code here
}

Answer 6

Dada la característica explicada por Henrique Abreu, puede probar la función de hoja de cálculo de QUERY , que la consulta de Me gusta de SQL es lo que uso a menudo en el trabajo con datos sin procesar y obtener datos como resumen en una pestaña diferente, los datos de resultados se actualizan en tiempo real después del cambio en los datos sin procesar.

Mi sugerencia se basa en el hecho de que su secuencia de comandos no tiene un trabajo avanzado, como búsqueda de URL, solo trabajo de datos, ya que sin datos reales leídos, no puedo dar una solución precisa con QUERY.

En cuanto a la característica de caché mencionada por Henrique Abreu (no tengo suficiente reputación para comentar directamente en su respuesta), hice pruebas y descubrí que:

1. parece que no hay caché funcionando, el guión de la función de prueba se muestra a continuación:

   función sumador (base) {Utilities.sleep (5000); base de retorno + 10; }

aplicando ese adder de función personalizado () en una hoja llamando a una celda, y luego cambié ese valor de celda adelante y atrás, cada vez que veo el mensaje de carga y el tiempo total más de 5 segundos. Puede estar relacionado con la actualización mencionada en este problema de GAS:

Este problema ahora ha sido arreglado. Las funciones personalizadas en Hojas Nuevas ahora tienen en cuenta el contexto y no almacenan en caché los valores de forma tan agresiva.

2. el problema mencionado en este tema permanece, mi prueba sugiere que, la hoja de Google vuelva a calcular la función personalizada cada vez SOLAMENTE CUANDO

   • Se cambia el valor DIRECTAMENTE llamado por función.

   function getCellValue (sheetName, row, col) {var ss = SpreadsheetApp.getActiveSpreadsheet (); var sh = ss.getSheetByName (sheetName); return sh.getRange (row, col) .getValue (); }

   
   Un cambio de cualquier valor en celdas amarillas conducirá a un nuevo cálculo de la función personalizada; la función no tiene en cuenta el cambio en el valor real de la fuente de datos.

   • la función que contiene la ubicación de la celda se cambia en la hoja. ex. insertar / eliminar una fila / columna arriba o lado izquierdo.