Curso Gas

buscar  Classic

Software de sistema de tickets - Zendesk.com.mx

11th January 2016

Importando tablas de texto y datos de otras fuentes

Todas las organizaciones usan bases de datos que exportan tablas como archivos de texto. La estructura de estas tablas en esos archivos es diversa. Este artículo explora varias maneras de volcar esos datos en una hoja de cálculo Google.

 Ar ch iv o, Imp or tar ... La manera más práctica de levantar tablas es a través del menú Archivo, Importar... , que abre una ventanita de opciones, preguntando dónde volcar los datos del archivo, y en cuál es la estructura del archivo. Las estructuras reconocidas son las más usuales, en las que las columnas están marcadas con un separador (que puede ser una coma, un tabulador, o cualquier otro caracter elegido a tal efecto) y las fil as separadas por salto de línea. Viene seleccionada la muy conveniente opción "Detectar automáticamente", que intenta descubrir el formato a partir de un simple análisis. Cuando esto funciona, no vale la pena buscar alternativas. alternativas. Pero cuando esto no funciona...

Complementos Cuando lo anterior no funciona porque el archivo de texto contiene una estructura diferente, vale la pena buscar complementos (Add-ons) específicos para esa estructura. estructura. Puede ser el caso de archivos XML. Sin embargo a la fecha de este artículo no abundan los complementos para importar datos.

GAS: Googl Googl e Apps Scripts (secuencias de comando) Cuando todo lo anterior falla, no queda otra que programar. Programar es difícil y caro (en términos de horas de trabajo), y por eso conviene dedicarle un momento para decidir si vale la pena ponerse a programar. Los GAS se utilizan para automatizar algún trabajo manual. Sin embargo, hay que tener en cuenta que desarrollar el GAS lleva muchísimo más trabajo que el que se va a evitar, y sólo vale la pena cuando son reutilizados, cuando se ejecutan con frecuencia. Raramente valga la pena elaborar un GAS para un trabajo de única vez. Obviamente, cada estructura particular requerirá un algoritmo particular. Sin embargo todos tienen un primer paso en común: leer los datos del archivo de texto y volvarlos a una variable string. Esto se puede hacer de varias maneras, por ejemplo: 1. 2. 3. 4.

Pegar el texto en una celda de una Hoja de Cálculo Google Google (HCG) Pegar el texto en un cuadro de texto en un panel panel lateral de HCG Pegarr el texto en un Documento Pega Documento de Google (Docs) (Docs) Subirr el archivo de Subi de texto a Google Drive

Una cuestión importante a tener en cuenta al pegar texto es su codificación. La codificación atañe a los caracteres

buscar  especiales pueden cambiar. Para evitar este problema hay que cambiar la codificación que usa la base de datos para Classic La única alternativa que evita totalmente este problema es subir el archivo a Drive para procesarlo directamente sin copiar y pegar.

Pegar el texto en u na celda de HCG Quizás la manera más práctica y más fácil para programar. La principal limitación es el número de caracteres: 50.000. Esto suele ser poco para grandes tablas. Este límite no se puede superar, el único camino para evitarlo es separar el texto en partes menores a 50.000 caracteres. Esto requiere trabajo manual, y sólo conviene para casos excepcionales: tablas pequeñas que rara vez superarán este límite. GAS vuelca el contenido de la cel da de una variable string de la siguiente manera:

var mi Te Text xt o = Sp Sprr ea eadshe dsheetApp etApp.. ge gett Act i veS veSpreadshe preadsheet et ( ) . ge gett Sh She eet( ' Hoja 1 ' ). ge getRa tRang nge(' e(' A1' ) . get Va Val ue( ) ;

 Alternativamente y mucho más recomendable: usar usar rangos con nombre, en este caso la celda "entradaDeTexto" "entradaDeTexto"

var mi Te Text xt o = Sp Sprr ea eadshe dsheetApp etApp.. ge gett Act i veS veSpreadshe preadsheet et ( ) . getRangeByName ( ' entradaDeTexto ' ) . get Va Val ue( ) ;

Pegar el texto en un cuadro de texto de un panel (sidebar) Las secuencias de comando de mejor aspecto y más fáciles de usar son aquéllas que utilizan paneles laterales (sidebars), cuyo contenido se escribe en HTML y por lo tanto permite elegir tipos de letra, colores y hasta incluir imágenes. Entre otras cosas, permite utilizar cuadros de texto. Programar paneles requiere famliarizarse con la API HTML de GAS, además de saber HTML, DOM y javascript. Se pueden usar cualquier cuadro de texto, como INPUT, TEXTAREA o DIV contenteditable. En particular TEXTAREA resulta conveniente porque facilita al usuario ajustar su tamaño tamaño para ver mejor el contenido. Desde javascript sus contenidos se leen con:

/ / Cu Cuando ando cuadro es I NPU PUT T varr cuadro va cuadro = do docum cument. ge gett QuerySe erySell ec ectt or( ' i np nput' ut' ) ; varr t ex va extt o = cu cuad adrr o.val ue; / / Cuan Cuando do cuadro es TEXTARE TEXTAREA A varr cuadro va cuadro = do docum cument. ge gett QuerySe erySell ec ectt or( ' t ex extt area' ) ; t ex extt o = cu cuad adro. ro. t ex extCo tConten ntentt ; / / Cuan uando do cuadro cuadro es DI DI V o una una de otr as much uchas as eti qu quet et as posi bl es var cuadro var cuadro = do docum cument. ge gett QuerySe erySell ec ectt or( ' di v' ) ; t ex extt o = cu cuad adro. ro. i nn nne erTex rTextt ;

Luego hay que enviar el contenido a GAS usando la  API de HTML [  [https://deve https://developers.google lopers.google.com/apps-script/ .com/apps-script/reference/ht reference/html/] ml/] . [Más sobre sidebars [  [https://de https://developers.goo velopers.google.com/apps-s gle.com/apps-script/guides/dialog cript/guides/dialogs s] ]

Pegar el texto en un Docs Esta es una alternativa peculiar, conveniente cuando interesa usar o conservar el texto en un Doc, por ejemplo para hacerle retoques manuales, incluso de manera colaborativa, antes de procesarlo con GAS. Hay muchas maneras de levantar el texto de un documento, la más directa ej ecutando desde una HCG es:

var mi Texto = Document App. ope openB nByI yI d( docId ) . getB etBod ody() y() . ge getTex tText( t( ) ;

Hay que conocer el ID del del documento. Alternativamente se puede usar openByUrl( openByUrl(docUrl docUrl))  en lugar de openById (docId).

buscar  Classic

 Arc hi vo d e text o en Dr iv e La manera más directa de abrir un archivo de texto subido a Drive es po r medio de su Id:

var mi Texto = Dr i veApp. veApp. get Fi l eB eByI yI d( d( archivoId ) . getBl getBl ob( ob( ) . get get Dat aA aAsSt sSt r i ng( ng( charset) ; DriveApp permite indicar la codificación (charset) para para que no se cambien los caracteres. Este argumento es opcional. Si la codificación no se conoce y ocasiona problemas, en lugar de getDataAsString se puede utilizar getBytes(), que en lugar de una string devuelve un array de bytes con el contenido intacto, aunque más complicado para manipular.

Limitaciones de procesamiento de GAS El tiempo de ejecución de GAS está limitada a unos pocos minutos, lo que empeora además si se considera que su ejecución no es rápida: es bastante lenta. Esto significa que la conversión de archivos de textos grandes puede verse abortada. Lo primero que hay que saber es que los GAS abortan de manera silenciosa, no se dispara ninguna función para guardar alguna variable de estado o registro que indique por dónde iba el procesamiento cuando abortó. La primera recomendación es ir volcando a la HCG los resultados a medida que se van obteniendo, en lugar de esperar al final para hacerlo.  Además, hay tres maneras de superar esta limitación de de procesamiento: 1. Dividir el trabajo en lotes pequeños. El tamaño de los lotes lotes se elije más o menos en función función a la experiencia y nada más. No hay nada científico para hacer al respecto. 2. Dejar registro frecuente frecuente del punto que se está procesando, para saber por dónde retomar luego de un aborto. 3. Alternativamente, iniciar con un algoritmo algoritmo que analice los resultados de la HCG para para determinar hasta dónde se procesó, y continuar desde lo que falta procesar. La primera alternativa tiene el problema de elegir el tamaño adecuado. adecuado. La segunda agrega una tarea que enlentece bastante la ejecución de cada bloque. La tercera argega una tarea que enlentece el inicio de la ejecución luego de un aborto. En todos los casos, esta limitación requiere considerar la eficiencia del algoritmo. Una de las acciones más lentas es la de volcar o leer rangos en la HCG. Google recomienda leer o escribir un rango grande de una sola vez antes que leerlo por partes en lecturas o escrituras consecutivas. Esto requiere una solución de compromiso entre volcar los datos a la HCG a medida que se obtienen o todos juntos al final.

En resumen GAS permite automatizar algunas conversiones de archivo, pero no es lo más adecuado para tablas grandes. Hacer una secuencia que pueda retomar un trabajo inconcluso es una tarea de programación avanzada, y antes conviene tener algo de experiencia haciendo estas conversiones. Mi sugerencia para quien quiera usar GAS para convertir archivo: que comience con archivos pequeños, o los divida para procesar en lotes pequeños. Luego, con algo de experiencia en el tema, podrá encarar encarar un GAS que retome un lote aboratado. Comentarios y discusiones sobre este tema serán mejor tratados en la comunidad de Hojas de Cálculo Google, en  [https://plus.google.com/u/0/communities/117381564739239305447 https://plus.google.com/u/0/communities/117381564739239305447 castellano [

].

Publicado 11th January 2016 por 2016  por Alejandro  Alejandro Silvestri 0

Ver comentarios

Paneles personalizados con HTML en Hojas de cálculo Google

buscar 

Classic

Las Hojas de Cálculo Google (HCG) tienen un mecanismo para crear interfaces de usuario altamente interactivas y personalizadas, a través de sus paneles laterales (sidebars). Estos paneles se diseñan en HTML y pueden contener código javascript, lo que los hace tan p otentes como cualquier página web. En este artículo, dirigido a usuarios familiarizados con Google Apps Script y mejor con algunos conocimientos básicos de HTML, pretendo acompañar al desarrollador en sus primeros pasos hacia el desarrollo de paneles con HTML. No

dejes

de

comentar

y

compartir

tu

experiencia

[https://plus.google.com/u/0/communities/117381564739239305447

en

la comunidad

de

Hojas

de

Cálculo

Google

].

Breve glosario Si no estás familiarizado con la jerga, este d iccionario te vendrá bien: • HCG: HCG: Hoja de Cálculo Google • Script Script:: secuencia de comando (en castellano), macro (en la jerga de Excel), o código • GAS: GAS: Google Apps Script, lenguaje y entorno de programación de scripts para HCG entre otros • HTML: lenguaje HTML:  lenguaje para diseñar  [http://de  [ http://definicion.de/wpfinicion.de/wp-content/uploa content/uploads/2009/06 ds/2009/06/glosario.jpg] /glosario.jpg] páginas web • Tag: o etiqueta HTML, como , , , •  Atri but o: o: parámetros  parámetros de una etiqueta HTML, por ejemplo el atributo ID en una etiqueta A: • Javascript: Javascript: lenguaje  lenguaje de programación de páginas web, idéntico a Google Apps Script • Desarrollador:  usuario que escribe el código GAS, HTML y ja vascript de un panel

¿Qué es un panel? Para que no haya equivocación, algunas imágenes de una hoja de cál culo con un panel lateral a la derecha:

[http://2.bp.blogspot.com/-ppR6OtOMw0/Uc0OZZpOIPI/AAAAAAAABC0/R tOMw0/Uc0OZZ pOIPI/AAAAAAAABC0/RWYz9KEw9RI/s WYz9KEw9RI/s1600/1Q1q 1600/1Q1qtCNCtXW_Tw tCNCtXW_Tw5lsRYzM8gLKvvaciGvw 5lsRYzM8gLKvvaciGvwnXR85Q.png] nXR85Q.png]

En esta imagen se ve el panel "Cursor inspector". El contenido HTML creado por el desarrollador es todo lo que está debajo de la barra negra. En este caso se aprecian recuadros, tablas y cuadros de texto en los que el usuario puede escribir.

buscar  Classic

Este otro ejemplo ilustra la posibilidad de agregar botones e imágenes, logrando un aspecto muy profesional. Los paneles personalizados funcionan tanto en HCG, como en Docs y Formularios de Google. Si bien hay muchas maneras de crearlos, en este artículo trataremos la más moderna y con más futuro.

HTML para el panel

[http://www http://www.seismicthemes.com .seismicthemes.com/wp-content/ /wp-content/uploads/2012 uploads/2012/08/wordpre /08/wordpresssstheme-seo.jpg]

El editor de secuencias de comando (que se abre desde el menú Herramientas) puede contener archivos GAS y archivos HTML. En uno de estos últimos se escribe el código HTML de un panel, que se crea con el menú Arc menú  Arc hiv o, Nuevo, Archivo HTM HTML L. Google simplifica la tarea, omitiendo una serie de etiquetas habituales en las páginas web, pero innecesarias en los paneles. Mientras una página web web típica luce como:

d>. . . . . . d> y>. . . > los archivos HTML de GAS se concentran en el contenido del body, omitiendo las etiquetas ,

y

, pero permitiendo la etiqueta . La estructura típica de un panel es: es: . . . códi go ht ht ml . >. . . Ninguno de estos tags es obligatorio, pero se usarán en algunos algunos ejemplos, y se analizarán uno a uno. Para poder probar rápidamente los ejemplos: 1. Crear Crear una hoja de de cálculo nueva nueva 2. Abr Abrir ir el editor de secuencias secuencias de comando comando (menú Her Herramientas, ramientas, Editor de secuencias de comando... comando...)) 3. Crear Crear el archi archivo vo panel.html panel.html (menú  (menú Ar  Ar chi vo, Nu evo, A rchi vo HTML HTML)) 4. En e ell archiv archivo o Código.gs Código.gs (o  (o en cualquier otro) borrar todo y pegar lo siguiente:

f un unct ct i on on onO Ope pen() n() { Sprea preadsh dshee eett App. ge gett Ui ( ) . createMen enu u( ' Mi pa pan nel ' ) . ad add dI t em(' Mos ostt rar pan anel el ' , ' most r ar Pa Pan nel ' )   .addToU .addTo Ui ( ) ; }

buscar  . showSi debar ( Classic

. set SandboxMode( Ht ml Ser vi ce. SandboxMode. I FRAME) ); }

Luego de guardar, volviendo a la hoja de cálculo: 1. Actualizar la página con F5. Esto forzará la ejecución de la función onOpen, que agrega el nuevo menú. 2. Utilizar el menú Mi panel, Mostrar panel  . La primera vez no abrirá el panel, sino que solicitará permisos de ejecución, que hay que aceptar para poder probar el código. En adelante, los ejemplos de código HTML se pegan en el archivo panel.html, y se muestran con el menú Mostar panel.

Etiqueta style E m pi e z o p or el la p

 [http://www.stickersmania.fr/1250-1671-large/sticker-tag-graffiti-style-100x160-cm-ref-2284-.jpg]

or ser la primera, aunque sea la menos importante. En una página web esta etiqueta está en la sección HEAD, pero los paneles no tienen esa sección, por lo que se acostumbra ubicar la etiqueta style al principio. Se puede omitir si no se utiliza ningún estilo. Típicamente los estilos se incluyen recién en las etapas finales de un desarrollo, por eso no es raro que los paneles omitan la etiqueta style cuando están todavía en elaboración. Sin estilos, los paneles son feos:

Google facilita estilos propios para que sin esfuerzo los paneles se integren visualmente a la hoja de cálculo. Estos estilos se cargan con la siguiente línea, que suele ser la primera de todas:

Recomendación: utilizar esta línea en todos los paneles, desde el principio del desarrollo, y luego agregar estilos personalizados en la etiqueta style. Lo que no se recomienda es usar el atributo  style, una práctica anticuada y desalentada.

Código HTML Como en general los paneles tienen cuadros de texto y tildes para que el usuario escriba sus datos y elija opciones, es una práctica habitual utilizar etiquetas INPUT, y por el mismo motivo es usual encerrarlas en una etiqueta FORM, aun sin la intención de enviar ningún formulario. La etiqueta INPUT representa diversos elementos, según su

atributo type:

buscar  •
t ype="t ext" >

•
t ype=" r adi o"> produce un círculo cliqueable para elegir una de variar opciones •
t ype=" checkbox"> produc e un cuadrito para tildar  Nota: Si bien el panel no enviará ningún formulario, conviene  [http://www.i3s.unice.fr/~comet/Images/html.png] encerrar todos los input en una etiqueta

, y dejar los botones fuera de ella. De otro modo es posible el navegador interprete la pulsación de un botón como la orden para enviar el formulario y el contenido del panel desaparecerá. Esta precaución evita disparar un comportamiento imprevisto del sandbox. La etiqueta input tiene otros atributos relevantes: • name: un nombre cualquiera. Sirve para agrupar varios radio buttons (todos con el mismo name). También facilita el acceso al elemento vía document . f orms[ 0] . nameDel I nput • value: valor precargado en el casillero Finalmente, cada etiqueta input suele ir acompañada de una etiqueta label, que proporciona el texto con el título o explicación del campo input. En este caso cada etiqueta input debe contener un atributo id único, y cada etiqueta label debe tener un atributo for con el valor del id del el emento input relacionado. Por lo tanto, la estructura general para cada campo de la interfaz de usuario es la siguiente:

Tí t ul o par a el campo de t ext o Descr i pci ón del checkbox Descri pci ón del r adi o 1 Descri pci ón del r adi o 2

check ed

checked

checked

Bot ón

Copiando y pegando el código verde en el archivo panel.html se podrá ver cómo queda.

La etiqueta script El código html sirve para armar la interfaz de usuario, pero no hará [http://team-blog.tripod.com/rtf2html-logo3-256x256.png] nada sin su correspondiente código javascript. Google recomienda que el código vaya al final del archivo HTML. El código javascript se ejecuta en el navegador, tiene acceso a los elementos HTML pero no tiene acceso a las API de GAS. Para esto Google provee un puente: una manera de disparar la ejecución de una función de GAS desde

 javascript, de la siguiente manera:

buscar  Classic Script, para ejecutarla en el servidor. La primera cuestión que hay que tener en cuenta es que la ejecución es asincrónica: miFuncionGas no devolverá su resultado inmediatamente. Si se espera un resultado de la función GAS, ésta se obtendrá como argumento de una función "listener", y la línea anterior se reescribe de la siguiente manera:

googl e. scr i pt . r un. wi t hSuccessHandl er ( r ecept or ) . mi Funci onGAS( ar gument os) ; f unct i on r ecept or ( r esul t ado) {. . . procesar el r esul t ado de l a f unci ón GAS. . . } De este modo, la primer línea dispara la ejecución de mi Funci onGAS( ar gument os)  en el servidor, y al terminar pasa su resultado como argumento de la función r ecept or( resul t ado) . Completando el ejemplo anterior:

document . get El ementByI d( ' bot on' ). addEvent Li st ener ( ' cl i ck' , botonPul sado); f unct i on bot onPul sado(e) { googl e. scr i pt . r un. wi thSuccessHandl er ( r ecept or) . mi Funci onGAS( ) ; } f unct i on r ecept or ( r esul t ado) {. . . procesar el r esul t ado de l a f unci ón GAS. . . }

Este ejemplo a medias no funcionará si no se desarrolla el código de la función receptor en panel.html, y la función miFuncionGAS() completa en código.gs

Historia

[http://imagecache2.allposters.com/IMAGES/KUN/4837.jpg]

Siempre preocupó la seguridad de este tipo de servicios. El razonamiento de Google es el siguiente: "si vamos a permitir que nuestros usuarios ejecuten en su navegador tanto código HTML y javascript de desarrolladores desconocidos, ¿cómo asegurar que no sea n malintencionados?" Originalmente Google lanzó su servicio UI (Interfaz de Usuario) para crear paneles y cuadros de diálogo, que permitía hacer lo mismo que se hace con HTML, pero de una manera mucho más restrictiva. Su proyecto "Caja" logró crear un "sandbox" (arenero, caja de arena), un entorno seguro para ejecutar HTML y  javascript lo suficientemente libre para crear interfaces de usuario, pero con todas las limitaciones necesarias para impedir el uso malintencionado. Caja evolucionó y pasó por tres etapas, cada una lograba más libertad de uso y mayor velocidad de ejecución. Cuando quedó claro que Caja podía brindar lo que se necesitaba, Google abandonó y desalentó su se rvicio UI. En este momento el contenido HTML se puede mostrar en uno de tres modos de "sandbox" diferentes: • EMUL  ATED, un sandb

ox

buscar  muy Classic tible con naveg adores antigu os • NATI V  [http://media.bestofmicro.com/R/Z/406655/original/sandbox.jpg] E, un sandbox más rápido pero no tan compatible • IFRAME, un sandbox más rápido todavía y con menos restricciones, que sólo funciona en los navegadores más avanzados. No funciona, por ejemplo, en Internet Explorer 9 Originalmente EMULATED fue el modo de sandbox por defecto. En 2014 el modo por defecto pasó a ser NATIVE, mucho mejor que EMULATED, mientras se comenzó el desarrollo de IFRAME. Se puede suponer que en el futuro IFRAME será el modo por defecto. Mientras tanto, conviene utilizar IFRAME, lo que requiere indicarlo expresamente como se observa en la función mostrarPanel:

f unct i on mostr arPanel ( ) {   Spr eadsheetApp. get Ui ( ) . showSi debar ( Ht ml Servi ce. cr eateHt ml Output FromFi l e(' panel ' ) . s et T i t l e( ' Un t í t u l o pa r a mi panel ' ) . setSandboxMode(HtmlService.SandboxMode.IFRAME) ); }

Epílogo Hay mucho más para decir del servicio HTML. A grandes rasgos: • Cuando sea posible elegir, conviene procesar datos en  javascript en vez de GAS. • No hay una función en GAS que permita disparar otra en  javascript. • Conviene obtener datos vía google.script.run  en lugar de [http://necesitodecirlo.ticoblogger.com/files/2014/09/lapida2yt.jpg] usar TEMPLATES. •   google.script.host  tiene métodos para, por ejemplo, cerrar el panel. • Se pueden mostrar imágenes en un panel usando el tag IMG y su url, pero no se pueden tener archivos de imágenes en el proyecto de una hoja de cálculo. • Google recomienda usar jQuery No

dejes

de

comentar

y

compartir

tu

experiencia

[https://plus.google.com/u/0/communities/117381564739239305447

en

la comunidad

de

Hojas

de

Cálculo

Google

].

Bonu s Si vas a escribir

[http://www.voiceofcoaches.com/wp-content/uploads/2013/04/bonus-icon3-300x225.jpg]

en html, te recomiendo asignarle el atributo id a los elementos que vayas a acceder con javascript. La manera tradicional de crear una variable para un elemento con id es:

var mi El ement o = document . get El ement sByI d(' I dDel El ement o' ) ; Una línea de este tipo para cada elemento. Sin embargo, te recomiendo el siguiente código que recorre todo el html y te genera una variable para cada elemento con id. Para facilitar las cosas, la variable tiene el mismo nombre del atributo id:

var el emento, i , element os = document. querySel ectorAl l (' *[ i d]' );

buscar  Classic

O de manera más compacta:

f o r ( i =( e =doc ument . qu er y Sel ec t o r Al l ( ' * [ i d ] ' ) ) . l eng t h; i - - >0; ) wi nd ow[ e [ i ] . i d] =e[ i ] ;

Referencias En inglés: •  API HTML para GAS [https://developers.google.com/apps-script/reference/html/?hl=es-ES] • Introducción a sidebars con HTML  [https://developers.google.com/apps-script/guides/dialogs?hl=es-ES] • Opciones para type en la etiqueta input [http://www.w3schools.com/html/html_form_input_types.asp] • Ejemplo de código para el archivo html  [https://developers.google.com/apps-script/quickstart/docs.html.txt?hl=es-ES] • Código completo de un complemento que usa un panel  [https://developers.google.com/apps-script/quickstart/docs?hl=esES]

Publicado 18th February 2015 por Alejandro Silvestri 3

19th December 2014

Ver comentarios

Scripts en celdas: funciones de Google Apps Scripts evaluadas en celdas de Hojas de Cálculo Google

[https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy?url=http%3A%2F%2Fp1.pkcdn.com%2Fcodigo-digital-deprograma_307497.jpg&container=blogger&gadget=a&rewriteMime=image%2F*]

[https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy? url=http%3A%2F%2Fp1.pkcdn.com%2Fcodigo-digital-deprograma_307497.jpg&container=blogger&gadget=a&rewriteMime=image%2F*]

Las Hojas de Cálculo Google permiten escribir y ejecutar "macros" o secuencias de comando (o scripts), pero además permiten escribir funciones personalizadas para ser llamadas desde las fórmulas en las celdas. En este artículo exploramos sus posibilidades y limitaciones. No

dejes

de

comentar

y

compartir

tu

experiencia

[https://plus.google.com/u/0/communities/117381564739239305447

en

la

comunidad

de

Hojas

de

Cálculo

Google

].

[http://mpe-s2-p.mlstatic.com/binoculares-largavistasprofesionales-10x-70-x-70-con-zoom-15545-MPE20105312798_052014-F.jpg]

¿Qué hay en este artículo?  A continuación veremos:

• Mayúsculas y minúsculas en los nombres de las funciones

buscar  • Cuándo se recalculan las funciones GAS Classic puede saltearse algunas partes debidamente señaladas, incluso puede saltearse el artículo entero e ir a jugar al Candy Crush.

Breve glosario Si no estás familiarizado con la jerga, este d iccionario te vendrá bien: • HCG: Hoja de Cálculo Google • Script: secuencia de comando (en castellano) o macro (en la jerga de Excel) • GAS: Google Apps Script, lenguaje y entorno de programación de scripts para HCG entre otros • • • •

Fórmula: la que determina el valor de una celda en una HCG Función de HCG: funciones para ser usadas en fórmulas Función de GAS: algoritmo que se puede llamar desde otra parte de las secuencias de comando  Arr ay: matriz

 AB C: c óm o esc ri bi r un a fu nc ió n p ers on alizad a Las funciones personalizadas (al igual que todo GAS) se programan en javascript.  Aquí hay un excelente curso en castellano para aprender javascript  [http://www.codecademy.com/tracks/javascript] . El menú Herramientas, Editor de secuencias de c omando... abre la herramienta "editor de secuencias de comando". En este sentido, es muy coherente. Para este ejemplo, el paso siguiente es elegir "proyecto en blanco", y se abre el editor. Luego copiar y pegar en el editor:

f unct i on DUPLI CAR( val or ) {

 [http://upload.wikimedia.org/wikipedia/en/f/f2/Google_Apps_Script.png]

return  val or * 2; }

Guardar y listo, hemos escrito la función de GAS duplicar(valor) ya está lista para usar en las fórmulas. En cualquier celda de la HCG ingresar la siguiente fórmula:

=DUPLI CAR( 7)

Me abstengo de predecir el resultado.

Nombres de las funci ones En las fórmulas se pueden usar todas las funciones globales definidas en el editor de secuencias de comando de esa HCG. Para usar alguna función definida en otra HCG, lo más fácil es copiar el código desde el editor de una HCG al editor de la otra.  Al asomarse al mundo [http://4.bp.blogspot.com/-5WoRQdxWBm0/U5Tb0gBBDhI/AAAAAAAAAng/mYh-fj17tUY/s1600/funcion.gif] de los scripts, hay que tener bien presente que los scripts tienen una sintaxis muy diferente a las fórmulas de hojas de cálculo. GAS se escribe en javascript, las fórmulas se escriben usando la sintáxis estándar de hojas de cálculo (establecida y estandarizada por OpenOffice y liberada bajo licencia pública general GNU). Mientras GAS hace distinción de mayúsculas y minúsculas, de manera que "duplicar", "Duplicar" y "DUPLICAR" son tres funciones distintas, las fórmulas de HCG no hacen tal distinción, y esas tres maneras hacen referencia a una única función. Estos dos ejemplos arrojan el mismo resultado:

=DUPLI CAR( 7)

buscar  =dupl i c ar ( 7)

Classic

Por lo tanto no hace falta preocuparse por las mayúsculas y mi núsculas. Cabe preguntarse: "si se definen las funciones DUPLICAR y duplicar ¿cuál se invoca desde las fórmulas?" Respuesta práctica: ¿para qué querés definir dos funciones con el mismo nombre? Respuesta técnica: se invoca la última en ser definida, la que está más abajo en el código, pues ésta redefine y sobreescribe a la anterior.

Tipos de valores Las funciones  [http://4.bp.blogspot.com/-WJ8Pmady8jo/T_G4Y_qlneI/AAAAAAAAAc8/fZJkSuoNVdg/s1600/developpers.jpg] reciben argumentos y devuelven un único valor. Las funciones personalizadas manejan los siguientes tipos: • • • • •

number  para valores numéricos string para textos boolean para booleanos (TRUE y FALSE) Date para fechas  Arr ay para matrices

Para el que quiera complicarse Cabe mencionar que esto es relativamente nuevo. Hasta hace poco HCG no usaba el objeto Date, y el manejo de fechas era bastante confuso. Antes, pasar una fecha producía un number en GAS, ahora produce un objeto Date. Antes, devolver la string '17/12/14' producía una fecha, ahora produce un texto. [http://cdns2.freepik.com/foto-gratis/rabia_2831837.jpg ]

Generalizando, antes los tipos devueltos por las funciones de GAS se interpretaban en la HCG según el contenido de texto (no quiero abundar en detalles sobre algo que ya no existe), ahora el tipo se corresponde con el tipo del valor retornado. Esto también es un poco confuso porque javascript no maneja tipos (pero el compilador sí, y por lo tanto los tipos son tácitos).

Rangos como argumentos Cuando se pasa un rango como argumento, la función GAS recibe un argumento del tipo Array, cuyos valores corresponden a los valores de las celdas. Si el rango es de una sola cel da, por ejemplo DUPLICAR( A1), la HCG pasa el v alor de esa celda como argumento. Si el rango tiene varias celdas, la HCG pasa un Array de dos dimensiones con los valores de todas las celdas del rango. Incluso cuando el rango es unidimensional, como por ejemplo el rango A:A. La matriz pasada tendrá siempre dos dimensiones: un vector de filas, siendo cada fila un vector de valores de cada celda.

[http://www.osenseis.com/wp-content/uploads/2014/10/El-pelot%C3%B3n-de-los-torpes300x300.jpg]

Por ejemplo, suponiendo que cada celda co ntiene su propio nombre (la celda A1 conti ene "A1"), en GAS el rango A1:A3 produce la siguiente matriz: [["A1"], ["A2"], ["A3"]] y el rango A1:C1:

[["A1", "B1", "C1"]]

buscar  No hay manera de pasar un argumento del tipo Range. No hay manera de descubrir cuál es el rango del argumento. Classic

[http://www.rdiplastics.com/wpcontent/uploads/moldes-3.jpg]

Matrices como argumentos HCG permite escribir matrices en sus fórmulas. Los rangos no son la única manera de proporcionar una matriz de valores. También se pueden escribir "literalmente", como en el siguiente ejemplo:

=SUM( {2; 3; 5; 7; 11; 13})

o =SUM( {{2; 3; 5}; {7; 11; 13}})

Notar que la sintaxis de GAS es diferente a la de las fórmulas de HCG. En GAS (como en javascript) las matrices o arrays se escriben con corchetes en lugar de llaves y coma en lugar de punto y coma. Por ejemplo,

=MI FUNCI ON( {{2; 3; 5}; {7; 11;13}})

recibirá como argumento

[ [ 2, 3, 5] ,

[ 7 , 11, 13] ]

[http://www.rdiplastics.com/wp-content/uploads/moldes-3.jpg ]

Matrices como resultados ¿Qué pasa cuando una función de GAS devuelve un objeto Array como resultado? El resultado se vuelca sobre un rango de iguales dimensiones. Se trata de un caso especial conocido en HCG como "función matricial", y no es específico de GAS. La función de HCG IMPORTRANGE es un ejemplo de función matricial. Cuando la matriz resultante de una fórmula sea vector (una matriz

un

de

una sola dimensión [http://files.climaorganizacional9.webnode.mx/200000567-dc3c8dd37b/resultados.jpg] ), sus valores se extenderán a las celdas hacia abajo, tantas celdas como valores haya.

Cuando la matriz tenga dos dimensiones, se desplegará hacia la derecha y hacia abajo.

buscar  Classic

El lado oscuro d e las fechas

[http://1.bp.blogspot.com/_Rdr2fyjWN00/TDNIXLWLgI/AAAAAAAAAQY/xX6rNsTo7ds/s1600/fecha+y+hora.png]

En HCG las fechas son solamente un formato de representación de valores numéricos. Sin embargo, las funciones en GAS las representan diferente. La HCG convierte un valor numérico en fecha cuando el formato de celda de ese valor es de fecha. ¿Simple no? Como no es posible introducir una fecha de forma literal en una fórmula (1/1/14  se calcula como dos divisiones y resulta en un catorceavo en lugar de una fecha), la única manera que conozco de pasar fechas como argumentos es por referencia a una celda que contenga una fecha. El objeto Date no es trivial y puede prestarse a confusión. Si hay que usarlo en cálculos en GAS, conviene leer la definición del objeto [https://developer.mozilla.org/enUS/docs/Web/JavaScript/Reference/Global_Objects/Date] y practicar bastante.

 Al gu nas li mi tac io nes d e las f uncio n es GA S Regla de oro: usar las funcio nes perso naliza das  [http://www.signovial.pe/blog/wp-content/uploads/2014/07/senal-pare.jpg ] para calcular valores basados exclusivamente en los argumentos  que recibe. Si vas a usar las funciones de otra manera, te conviene conocer las limitaciones que tienen antes de romperte la cabeza adivinando por qué no funcionan.  A modo de re sumen, es mala idea pretender que una función llamada desde una fórmula modifique el valor de alguna otra celda . Ya estás prevenido. Ahora sí, a los bifes: Las funciones escritas en GAS disponen de todas las API de Google y de todas las funciones propias de  javascript, pero no tienen modo de invocar funciones de H CG. Por ejemplo , en una función de GAS n o se puede usar VLOOKUP: esa función no existe en GAS. Se puede programar una función que realice lo mismo, pero ése es otro cantar. Por otro lado, cuando las funciones de GAS se invocan desde fórmulas en celdas, tienen muchas más restricciones que si fueran ejecutadas desde un menú como una macro normal, y se resumen del siguiente modo:

1. Las funciones no pueden escribir . No pueden modificar el contenido de otra celda

2. No pueden usar APIs que requieran permisos. El único permiso que tienen es el de acceder a la propia hoja de

buscar  4. No admite argumentos no determinísticos: no se pueden usar NOW() o RAND() como argumentos

Classic

En los dos primeros casos la fórmula arroja un error bastante elocuente como éste: "ERROR: No cuenta con el permiso para llamar a setValue (línea 15)"

En el caso 4 la celda mostrará la leyenda "Loading..." para siempre. Un mensaje que desafía la intuición.

¿Qué es una función d eterminística? Es aquélla cuyo resultado depende exclusivamente de sus argumentos. La mayoría de las funciones son determinísticas, pero algunas no, por ejemplo NOW(), que devuelve un valor diferente cada vez que se calcula.

0/Tfpj QPKY9SI/AAAAAA AAAVo/fI8KUUHLttQ/s1600/Modelo+por+Atr actor+extr a%25C3%25B1o.jpg] Una función GAS no es determinística cuando su resultado es afectado por valores cambiantes en su algoritmo que no dependen de los argumentos, como por ejemplo,

• •

Math.random() genera números aleatorios

•

Range.getValue() obtiene el valor de una celda

Date.now() devuelve la fecha en milisegundos

El último caso es muy frecuente, cuando la función toma valores de rangos por sus propios medios en lugar de recibirlos como argumentos.

Recalculando...

buscar  Classic

[http://px.fotos.cdn.0223.com.ar/f/c5ec404a-1457-4782-a9f6c1ea0526009f/imgTapa.jpg]

HCG tiene un algoritmo avanzado de recálculo, que para ser más rápido evita recalcular las celdas cuyos valores no cambian. En rigor, si los argumentos de una función determinística no cambiaron, no hace falta recalcular la función. HCG asume que las funciones GAS son determinísticas, y por lo tanto no las volverá a evaluar mientras no cambie el valor de ninguno de sus argumentos, incluso cuando se recalculan otras celdas de la HCG.

 Autocompletar  Las funciones personalizadas soportan autocompletado, de manera que el usuario obtenga ayuda sobre la sintaxis (por ejemplo los tipos de datos para cada argumento) mientras escribe la función en una celda. Para eso hay que indicarle a GAS de qué tipo es cada argumento, lo que se hace dentro de comentarios, como en este ejemplo: / **   * Mul t i pl i c a e l v al o r por 2 . *   * @par am{num ber } i nput Val or a dupl i car.   * @r et urn El dobl e de i nput.   * @cust omf uncti on */ f unct i on DOUBLE(i nput) { ret urn i nput * 2; }

Técnicamente, GAS implementa JDoc, que es una manera de documentar javascript. JDoc usa palabras clave luego del @ , como @param o @return , dentro de un comentario multilínea inmediatamente antes de la definición de la función. GAS requiere la cláusula @customfunction para usar esta información a la hora de autcompletar.

Sintaxis muy breve: • @return  Comentario a mostrar, indicando qué devuelve la función. • @param  {tipo de dato} nombreDelArgumento Comentario sobre el argumento.

Si hay varios argumentos se usan varias cláusulas @param , una para cada uno. Tipos de datos hay muchos en JDoc, algunos son:

• • •

{number}

• •

{number[]} para un vector de valores numéricos

{string} {*} para cualquier tipo de dato

{Date}

Hay mucho más en JDoc [http://usejsdoc.org/tags-param.html ] , pero hay que tener en cuenta que ese sitio se refiere a javascript, y no específicamente a GAS.

buscar  Classic

s.com/uploadedImages/Public_Website/Content_-_Primary/Teardowncom/Sample_Reports/sample-

Ejemplos prácticos Un poco de código de ejemplo, que le puede servir a más de uno:

/ / Devuel ve el nombr e de l a hoj a f uncti on hoj a() { r etur n Spr eadsheet App. get Act i veSheet ( ) . get Name( ) ; }

/ / Devuel ve l a "key" o i dent i f i cador de l a hoj a de cál cul o f uncti on key() { ret urn Spr eadsheet App. getActi veSpr eadsheet( ) . get I d() ; }

/ / Devuel ve el r ango de l a cel da donde se eval úa l a f órmul a var est aCel da = SpreadsheetApp. get Act i veRange( ) ;

/ / Obt i ene el val or de un rango con nombr e var r ango = Spr eadhsheet App. get Act i ve() . get RangeByName(' nombre del r ango' ) . get Val ue( ) ;

[http://juandesoladotorg.files.wordpress.com/2011/03/referencias.gif ]

Referencias • La comunidad de Hojas de Cálculo Google [https://plus.google.com/u/0/communities/117381564739239305447 ] en castellano • Curso de javascript [http://www.codecademy.com/tracks/javascript] en castellano •  API de Hojas de Cálculo Google [https://developers.google.com/apps-script/reference/spreadsheet/?hl=es-ES ] (en inglés) • Funciones personalizadas en HCG [https://developers.google.com/apps-script/guides/sheets/functions?hl=es-ES ] (en inglés) • Fórmulas matriciales [https://support.google.com/docs/answer/3093275?hl=es ] en castellano

Certificado de lectura

Si necesitás un certificado de lectura de este artículo, copiá lo siguiente en un editor de texto (incluyendo la firma),

buscar  La Comunidad Castellana de Hojas de Cálculo Google certifica que (tu nombre acá) ha llegado al final del artículo Classic

Publicado 19th December 2014 por Alejandro Silvestri Ver comentarios

7th July 2014

Google Apps Script: algo más que un lenguaje de macros

Google Apps Script es el lenguaje de "macros", "secuencias de comando" o "scripts" de Google Docs. Si bien este lenguaje es popularmente conocido por ser utilizado para macros de hojas de cálculo Google, su alcance va mucho más allá. Google Apps Script (conocido también como GAS, por sus iniciales) es el primer lenguaje de computación en la nube accesible para usuarios de forma masiva. El hecho de ser el primero significó que no había modelo a seguir, Google tuvo que ir probando diferentes formas de usar este lenguaje, recorriendo un camino de prueba y error continuos. Quienes siguen este lenguaje desde sus inicios, han visto crecer su potencial a través de nuevas API experimentales, la mitad de las cuales se "graduaban" para convertirse en parte de la API estable y definitiva de GAS. La otra mitad moría con la tenebrosa leyenda "deprecated".  A lo largo de su desarrollo Google ha intentado diferentes enfoques, y entre 2013 y 2014 decidió darle forma definiti va (quién sabe cuánto tiempo significa "definitiva"), reordenando su documentación e introduciendo un nuevo modelo de conceptos para este lenguaje. Hoy GAS es mucho más que un lenguaje de macros, y s u amplitud puede confundir al programador que decide expl orar funciones avanzadas.

Una visión panorámica Quien haya empezado escribiendo macros para hojas de cálculo puede sentirse confundido a la hora de querer hacer algo fuera de las hojas de cálculo. Para poner orden, nada mejor que una visión de "bosque". Hay varios tipos de scripts, que se diferencian por la manera en que serán ejecutados. Cada tipo de script tiene una determinada capacidad en cuanto a qué APIs puede utilizar, y a qué datos privados puede acceder. Es vital tener en claro cuáles son estos tipos de scripts, esto permitirá comprender qué cosas se pueden hacer y cómo, y qué cosas no se pueden hacer, antes de empezar a hacerlas. Para ir despacio de lo general a lo particular, conviene se parar los scripts en dos grandes categorías: • Container-bound , o atado a un documento contenedor : son los scripts que se ejecutan dentro de una hoja de cálculo Google, un Doc, un Form, (el contenedor). Un ejemplo son los menúes personalizados. • Web apps, son scripts atados a una dirección web, independientes de cualquier documento Google. Sirven para generar páginas web dinámicas, o gadgets para Google Sites. Claramente, el programador de macros desarrolla script "container-bound", y muchas veces desconoce las po sibilidades que brindan las web apps.

buscar  Classic Documentación sobre container bound scripts [https://developers.google.com/apps-script/scripts_containers?hl=es-ES]

Hay tres tipos de container bound scripts que conviene distinguir: 1. Macros de un documento 2. Complementos de Google Docs ( Add-on) 3. Funciones personalizadas para hojas de cálculo Google (spreadsheeet custom functions)

Macros La mayoría de los tutoriales enseñan a crear macros para una hoja de cálculo, y por eso es el tipo de script más conocido. Estos scripts ejecutan la función onOpen sin privilegios. Luego, cuando el usuario elige ejecutar el script, por ejemplo a través de un menú creado por la función onOpen, GAS solicitará un permiso q ue el usuario puede aceptar por única vez. A partir de entonces, el script se ejecuta con los privilegios del usuario: puede acceder a los documentos del usuario y conocer su dirección de email. Las macros más avanzadas pueden crear una interfaz de usuario HTML en un panel lateral, con botones y cuadros de texto. Google dispone de una Galería de secuencias de comando (script gallery) que permite al usuario instalar en su documento un script desarrollado por un tercero. En la práctica, esta galería no dio resultado, no f ue adoptada por el público en general, lo que dio lugar a una nueva estrategia de d istribución de scripts: los Complementos.

Complementos (Add-on) Los "complementos" (add-on en inglés) son scripts similares a las macros, con la diferencia que no son propios de un único documento. Luego de que el usuario instala un complemento, este script se ejecuta en todos los documentos que abre el usuario, típicamente presentando un submenú propio en el menú Complementos. De manera similar a las macros, los complementos ejecutan su función onOpen sin privilegios (no pueden acceder a información personal del usuario), hasta que el usuario utiliza el complemento a través de su menú. Con esa acción el usuario habilita al script a ejecutarse con privilegios en el documento. Los complementos se obtienen desde el menú Complementos, y se instalan de una vez para todos los documentos. Ésta es la principal diferencia con las macros, que están asociadas a un único documento. Al momento de escribir este blog, los desarrolladores no pueden publicar libremente sus complementos: esta función se encuentra en etapa de prelanzamiento, y por motivos de seguridad su publicación no es automática, sino que está sujeta a una revisión previa. Más información sobre el desarrollo de complementos [https://developers.google.com/apps-script/quickstart/docs?hl=es-ES ]

Funciones personalizadas (spreasheet custom functions ) Se trata de un tipo muy específico de script: son funciones escritas para ser usadas en fórmulas, dentro de celdas de una hoja de cálculo. Estas funciones se ejecutan en un entorno muy limitado, y apenas pueden calcular un resultado a partir de sus argumentos. Estas funciones no pueden "hacer" cosas, más allá de calcular un resultado. No pueden cambiar el nombre de una hoja, ni crear un menú, ni mover el cursor. Se ejecutan sin privilegios, como "anónimos". El

modelo

de

GAS

asigna

un

tipo

propio

a

estas

funciones,

spreadsheet

custom

functions

[https://developers.google.com/apps-script/execution_custom_functions?hl=es-ES] .

Web apps Las web apps son una categoría de scripts totalmente diferente a los container-bounds: no están incluidas en ningún documento "contenedor"; en su lugar se acceden a través de una dirección web propia (una url). Además, mientras aquéllas se ejecutan exclusivamente con los privilegios del usuario que las utiliza, las Web apps pueden ejecutarse con esos privilegios, o con los del desarrollador. Hay tres tipos de web apps: • Web app propiamente dicha • Content service • Google Sites gadget

En rigor los tres tipos son web app, pero los últimos dos son casos específicos de web app. El modelo de GAS es un

buscar  Classic Web app El script se ejecuta cuando el navegador accede a su url. Así como al abrir un documento se ejecuta la función onOpen, en el caso de las Web app se ejecuta la función doGet o doPost, según el navegador esté realizando un GET o un POST. Para el que no está familiarizado con esta jerga, cuando el usuario escribe una url en la barra de direcciones, el navegador realiza un GET. GAS dispone de la API HTML Services para crear páginas web, estáticas o dinámicas. Como su nombre lo indica, las web app permiten desarrollar aplicaciones web.

Content Service Es el nombre que se le da a una web app que, en lugar de implementar una interfaz web para el usuario, se limita a proveer datos en formatos diferentes al HTML: XML, JSON, JSONP, texto, etc. Su propósito no es atender una solicitud del usuario, sino proporcionar información a consultas automáticas del tipo AJAX realizadas por otras páginas web. AJAX tiene limitaciones a la hora de solicitar datos a servidores de otros dominios, por ese motivo los content services se u tilizan mucho la técnica JSONP.

Google Sites gadget Se trata de una web app con una especificación adicional para que pueda ser incorporada como gadget dentro de una página de Google Sites. Más información sobre gadget para Google Sites [https://developers.google.com/apps-script/execution_gadgets?hl=es-ES ]

Seguinos en esta comunidad [https://plus.google.com/u/0/communities/117381564739239305447 ]

Publicado 7th July 2014 por Alejandro Silvestri 4

20th January 2014

Ver comentarios

Evolución de las hojas de cálculo Google

Seguinos en la comunidad en castellano de Hojas de Cálculo Google  [https://plus.google.com/u/0/communities/117381564739239305447]

Con el próximo lanzamiento de la nueva versión de Hojas de Cálculo Google (HCG), cuya beta está disponible desde Diciembre de 2013, se cierra un capítulo en el desarrollo de esta magnífica aplicación. En este artículo intento poner en perspectiva los cambios que sufrieron las HCG desde su lanz amiento, para especular sobre su futura evolución.

La versión original A mediados de 2009 Google lanzó sus HCG al público, pero solamente dentro de Google Apps. Más adelante las agregó a las cuentas regulares de GMail, dejó de hablar de "cuentas de GMail" para denominarlas "cuentas d e Google", donde GMail sería ser sólo un producto más de una creciente colecci ón. La versión original era igual a la de testeo G oogle Labs Spreadsheets.

buscar  Classic

[https://st.fl.ru/users/gi/gi0235/upload/f_57451b33d8944dce.png

]

En este momento las HCG usaban el estilo de menú "Ribbon", que había sido recientemente introducido en Microsoft Office para Vista. Las tres barras de botones Format, Sort y Formulas agrupaban todos los comandos de la hoja de cálculo. Desde siempre, pequeños cambios se fueron introduciendo en la interfaz, de manera suave, sin que eso signifique un cambio de versión para el usuario. El menú File se incorporó a la izquierda:

buscar  Classic

[http://cdn1.chillnite.com/wp-content/uploads/2007/04/google_spreadsheet_big.jpg ]

Quisiera ser como Excel Esta etapa de la evolución de las HCG estuvo marcada por la incorporación de funciones y comandos de Excel. En este sentido, HCG era una versión liviana y limi tada de Excel. La proliferación de comandos hizo que Google replanteara su interfaz "Ribbon" y la sustituyera por una barra de menúes y una barra de botones, adoptando la funcionalidad de las hojas de cá lculo más antiguas, pero con un aspecto mejorado y más limpio.

buscar  Classic

[http://1.bp.blogspot.com/_ZaGO7GjCqAI/SOnAq6IqYcI/AAAAAAAAL8k/vxOpHY5I_OQ/s640/google-spreadsheets-oct08.png ]

Igual que antes, se fueron haciendo retoques menores, agregando nuevas funciones, y cambiando levemente el aspecto:

[http://techmngtsolutions.files.wordpress.com/2010/05/tst_googledocs.jpg ]

buscar  Classic • El alineamiento estético con el resto de los productos de Google, cimentado en HTML5 • Fuerte énfasis en la colaboración, incorporando chat y lista de usuarios activos

[http://i.i.cbsi.com/cnwk.1d/i/tim//2010/04/09/spreadsheet_collab_610x450.png]

 A esta versión se la recuerda también por haber ordenado las pestañas inferiores, que dejaban de ser enlaces rudimentarios, y ahora admitían ser arrastrados. Si bien era la versión más compleja hasta ese momento, también fue la más intuitiva y fácil de usar. Un dato curioso: a los desarrolladores se les había terminado la lista de cosas de Excel que había que imitar. Luego de este lanzamiento, "nos" pidieron relevar las funciones más solicitadas por el público en general. Y como resultado de esta encuesta, en sucesivas mejoras, incorporaron por ejemplo la posibilidad de boquear celdas individuales. Excel había dejado de ser el modelo a seguir, en 2012 las HCG empezaban a hacer su propio camino al andar.

La última versión Visto en perspectiva, la versión 2014 (en beta al momento de este artículo) no presenta innovaciones espectaculares ni cambios dramáticos como las anteriores. La interfaz está apenas retocada. En este caso los desarrolladores trabajaron duro hacia adentro, en el "motor" de cál culo, y con mucho esfue rzo lograron dar respuesta a un antiguo reclamo: que se pueda modificar una HCG sin conexión a Internet.

¿Por qué no lo hici eron antes? Desde el comienzo las HCG realizaban los cálculos de las celdas en los servidores de Google. No importaba la potencia de tu PC, el recálculo demoraba siempre lo mismo. Y el motivo de esto era simple y contundente: las HCG (así como todo el conjunto de Google Docs) debían funcionar en móviles, en cualquier hardware, por poco potente que pudieran ser. La idea era poder usar una HCG en un celular, sin limitaciones. En este contexto, no tenía sentido encarar el esfuerzo de migrar el motor de cálculo al navegador. Pero algo cambió en Google, algo que no parecía muy relacionado con las HCG. Durante años Google intentó poner en el mercado el concepto de Chromebook, una notebook barata para navegar en Internet. El principal problema fue que era inútil cuando no había Internet. 2013 fue el año del segundo relanzamiento de las Chromebooks, ahora sí con toda la fuerza, y Google Docs aportaría su granito de arena con una versión "sin conexión" para todos sus servicios. Lógicamente, hacer funcionar las HCG sin conexión fue la parte más difícil, y la última en llegar.

buscar  manera de poner el tilde de "cumplido" a un objetivo interno de Google que les iba de las manos. Classic Mientras los celulares seguirán operando de la manera tradicional, delegando el cálculo a los servidores de Google, en las PC las HCG podrán hacer uso de la potencia local.

No sólo modificar sin conexión La última versión es mucho más que eso. Un replanteo total del código, derrumbando los límites originales, como la cantidad de columnas o de filas, y agregando funciones largamente solicitadas.

¿Cómo (creo que) sigue? Este replanteo profundo parece llegar para quedarse. No más nuevas versiones por muchos años. En adelante, se seguirán incorporando novedades de forma paulatina y suave, ya sin cambios de versión. Me parece ver dos líneas evolutivas: • La tradicional, que se agota: se seguirán incorporando funciones y comandos de Excel, se seguirán sumando formatos de archivos para convertir  • La integración con Google Plus: los cambios en el aspecto y funcionalidad de Google Plus llegarán también a HCG; la colaboración con otros usuarios adoptará la forma de comunicación de G+, se integrarán círculos, se facilitará la difusión de HCG en G+, y todo lo que te puedas imaginar de esta yunta Entre otras cosas, está pendiente la posibilidad de compartir las HCG con los círculos de Google Plus; de dar permisos a usuarios de Google Plus sin necesidad de conocer o dar a conocer el usuario de GMail. En esta línea algo ya se está moviendo en GMail. Y adentrando en la pura especulación y sin fundamento alguno, me parece que a lo largo de los años veremos la integración de los Formularios de Google con Google Plus, con más facilidad para difundir encuestas y registrar quién responde.

Seguinos en la comunidad en castellano de Hojas de Cálculo Google  [https://plus.google.com/u/0/communities/117381564739239305447]

Publicado 20th January 2014 por Alejandro Silvestri 4

13th January 2014

Ver comentarios

SUMAR.SI.CONJUNTO es SUMIFS en la nueva versión de Hojas de Cálculo Google 2014

 A fines de 2013 Google l iberó la versión beta de las nuevas Hojas de Cálculo Google (HCG), que se pueden activar así [https://support.google.com/drive/answer/3541068] . Espero que se lance definitivamente a principios de 2014. Esta nueva versión incorpora (al fin) la función SUMIFS, que en Excel en castellano se llama SUMAR.SI.CONJUNTO.

 [http://chandoo.org/img/f/sumifs-excel-formula.png ]

SUMAR.SI.CONJUNTO Si leíste este artículo anterior [http://hojasdecalculogoogle.blogspot.com.ar/2013/06/sumarsiconjunto.html ] , con la nueva función SUMIFS  se terminaron las complicaciones. La función SUMIFS funciona exactamente igual que SUMAR.SI.CONJUNTO. De hecho, SUMIFS es el nombre de la función en Excel en inglés, y no hay que confundir con SUMIF (SUMAR.SI).

buscar  Classic

SUMIFS(A1:A10; B1:B10; ">20"; C1:C10; "<30")

la función suma valores de la columna A, cuando los respectivos valores de la columna B son mayores que 20 y los de la C son menores que 30. No hay límite en la cantidad de criterios que se pueden agregar, siempre poniendo primero el rango y luego la condición. La página de ayuda de SUMIFS [https://support.google.com/drive/answer/3238496] describe la sintaxis. Ojo, porque los ejemplos están para la configuración regional en inglés, y para probarlos hay que reemplazar las comas s eparadoras de argumentos por punto y coma. Si se necesitan criterios más complejos, siempre se puede implementar la manera más versátil pero más compleja del artículo anterior  [http://hojasdecalculogoogle.blogspot.com.ar/2013/06/sumarsiconjunto.html] . Así como existe SUMIFS, también existen:

• COUNTIFS , equivalente a CONTAR.SI.CONJUNTO, para contar según varios criterios •  AVERAGEIFS, equivalente a P ROMEDIO.SI.CONJUNTO, para promediar según varios criterios

Comunidad HCG

Unite

a

la

comunidad

de

Hojas

de

Cálculo

Google

en

castellano

[https://plus.google.com/u/0/communities/117381564739239305447 ] para más artículos como éste.

Publicado 13th January 2014 por Alejandro Silvestri 3

Ver comentarios

Software de support desk

Atención más sencilla y eficaz. Prueba gratuita de 30 días. Comience ahora.

31st October 2013 ¿Cuadros de diálogo y formularios con hojas de cálculo? Es un lugar común imaginar las hojas de cálculo como una manera práctica de hacer formularios, con campos, para que otros usuarios lo completen. Dejando volar un poco la imaginación, algunos intentaron hacer un sistema cotización web con una hoja de cálculo, para poner en su sitio web y que los clientes interactúen usando listas desplegables y obtengan su presupuesto. La necesidad existe, pero el enfoque es radicalmente equivocado. En este artículo no apunta tanto a revelar cómo lo podemos hacer, sino a evitar que se den la c abeza contra la pared contándoles cómo no se puede hacer.

¿Por qué no hacer un formu lario con un a hoja de cálcul o? El cuadriculado de una hoja de cálculo es muy versátil, y es muy fácil confeccionar un formulario, escribiendo preguntas en unas celdas y poniéndole bordes a otras donde queremos la respuesta. Ésa es la manera en que nacen l a mayoría de los sistemas en las empresas. El problema aparece a la hora de distribuir ese formulario entre los usuarios que lo deben completar. Las Hojas de Cálculo Google (HCG) no son archivos, y cuando se comparten, los usuarios acceden a la MISMA HCG. Incluso, si la modifican al mismo tiempo, verán los cursores de los otros usuarios y lo que escriben. Si tiene casilleros para completar, el usuario verá lo que escribió el último que pas ó por ahí. Finalmente, cuando todos los usuarios nos avisan que ya completaron el "formulario", lo único que tendremos es un palimpsesto, la única HCG, escrita y reescrita una y o tra vez. La conclusión inmediata es copiar la HCG, haciendo una copia por cada usuario. Su utilidad es limitada, sólo sirve cuando se sabe de antemano a quién se enviará. No sirve, por ejemplo, para un formulario público o un sistema de cotización web, al que accederá cualquiera. Incluso si esto no fuera un problema, sí puede serlo el trabajo de compilar las respuestas recibidas en diferentes HCG. La razón fundamental por la que no conviene intentar hacer formularios con HCG, es porque se rigen por paradigmas diferentes. Usar una HCG para hacer un formulario es como usar ladrillos para hacer un auto. No digo que no se pueda.

buscar  Classic Google Forms es una herramienta para crear formularios, más elegantes que una hoja de cálculo, pero también más limitados. Google Forms se organiza como una sucesión de preguntas con sus casilleros para respuestas, que pueden aparecer en una sola página web, o en páginas consecutivas. Google Forms admite casilleros de texto, de fecha, de selección de una lista, y algunos otros, y las vuelca en una hoja de cálculo, una formulario por fila, una pregunta por columna. De este modo, tendremos todas las respuestas en una única hoja. Sin embargo tiene varias limitaciones: • No son programables, no se les pueden adosar secuencias de comando • Los tipos de respuestas son limitados, no admite, por ejemplo, campos numéricos • De la misma manera, no permite un control fino sobre las respuestas de texto. Por ejemplo, no se puede verificar si se escribió una dirección de email válida • El formulario no es interactivo. Por ejemplo, no puede calcular y mostrar un precio en base a los datos aportados Google Forms tiene muchas limitaciones de formato (colores e imágenes). Permite elegir un formato prediseñado de una lista corta, y nada más. Esto es así para evitar que se use esa herramienta para phishing u ni para otros engaños cibernéticos.  Algunas funciones muy requeridas sólo están disponibles en Google Apps. Sin Google Apps: • Las respuestas son anónimas, el formulario no registra el nombre del usuario que responde. Ni siquiera le solicita iniciar sesión para responder  • El mismo usuario puede responder todas las veces que quiera, y se registrarán como respuestas adicionales, sin ningún indicio que permita descubrir que son del mismo usuario Los formularios son abiertos para todo el público, pero una manera de restringirlo a un grupo cerrado de usuarios consiste en enviarles el enlace por email solamente a ellos. Sin embargo, nada impide que uno de ell os reenvíe el enlace a otra persona y se termine haciendo público. Finalmente, los formularios no sirven para mostrar información, sino solamente para solicitarla. Es decir, no se puede navegar a través de las respuestas recibidas con el formulario. Las respuestas se almacenan en una hoja de cálculo, una respuesta por fila. Por todo esto, hay que usarlos para lo que fueron creados: para recopilar información pública. Si se desea identificar a quién respondió, una técnica consiste en acercarle un código único (por email, por ejemplo), que el usuario deberá pegar en algún campo del formulario. Pero no es un camino viable como cuadro de diálogo de un sistema hecho en hojas de cálculo.

Insistiendo con usar una hoja para dibujar un formulario  Al mejor estilo Excel, podemos usar una hoja de cálculo para darle forma a un formulario, y unas pocas líneas de código en un botón, para volcar su contenido en una tabla de datos en otra hoja. Pero no podremos compartirlo, y si lo hacemos, deberemos cuidar de que dos usuarios no estén completando el formulario al mismo tiempo. De hecho, sabemos que otros usuarios podrán ver en tiempo real lo que se está escribiendo ahí. Esto funciona solamente en un ambiente colaborativo, entre gente que se conoce y se respeta. Las HCG tienen chat que facilitan la coordinación. Pero no se puede poner en una página web, abierto al público, ni se puede convocar a nuestros clientes a que usen el formulario de esa manera.

The hard way La única manera de hacer un cuadro de diálogo o un formulario con mayor nivel de personalización en una HCG, es programarlo con Google Apps Script (GAS). En su momento Google desarrolló una API particular para esto, y luego hasta creó un "GUI Builder" para diseñar cuadros de diálogo de manera gráfica. Pero todo esto fue superado, y ahora los cuadros de diálogo se hacen en HTML. Sandboxed HTML. Para quienes soñaban con hacer formularios con Forms o HCG, encontrarán que el desarrollo de un formulario mediante código es un trabajo experto y tedioso. En cambio, para los programadores, la nueva API HTML es una bendición: ¡ahora pueden hacer el formulario en HTML!  A no engañarse, hay qu e saber programar, lograr que funcione es un desafío, sandoboxed HTML falla donde menos lo esperábamos, y finalmente, cuando todo está como debe estar, notaremos que es extremadamente lento: el cuadro de diálogo demooooora en abrir. Google promete que se puede acelerar la ejecución de estos formularios, adoptando ciertas convenciones extrañas al escribir el código. No lo probé, no conozco a nadie que lo haya probado. Algún día lo probaré para ver cuánto se

acelera la ejecución.

buscar  dar cuenta que en vez de eso conviene programar la aplicación entera, y ol vidarse de la hoja de cálculo. Classic

Conclusiones Salvo en los casos particulares donde Google Forms te sirva, por ahora lo mejor es completar las tablas de datos celda por celda, sin formularios. La tecnología está, falta un intermediario que la ponga al alcance de todos. No me extrañaría que algún día el producto Forms termine cubriendo este interés, pero estamos hablando de años.

Publicado 31st October 2013 por Alejandro Silvestri 2

30th July 2013

Ver comentarios

Funciones especiales

Las hojas de cálculo Google tienen una serie de funciones muy especializa das, muy particulares , que conviene conocer por lo menos de vista, porque si alguna vez  [http://www.efunda.com/math/bessel/images/BesselJPlot.gif] las llegás a necesitar, te resolverán lo que no se puede hacer de otra manera. He aquí una rápida revisión de estas capacidades poco conocidas.

Insertar una imagen con una fórmula La función IMAGE(URL, opción) inserta una imagen en la celda donde se introduce la fórmula. Basta indicar la URL de la imagen (la dirección web, como http://www.efunda.com/math/bessel/images/BesselJPlot.gif  [http://www.efunda.com/math/bessel/images/BesselJPlot.gif] ). Usualmente se combinan varias celdas para que se vea la imagen completa.

Una característica interesante es que la URL puede provenir de una fórmula, de manera que podemos hacer cambiar la imagen en función de los valores de otras celdas. La siguiente fórmula muestra los colores del semáforo según el valor 0, 1 o 2 de la celda A1: =image( if(A1=2;"http://2.bp.blogspot.com/_AyWdu-Rfl6E/S-BYnWBCevI/AAAAAAAAASw/7IZ79dANE0/s1600/circulo_rojo.JPG"; if(A1=1;"http://www.quia.com/files/quia/users/sandrarosado/FormaGeometricas/circuloamarillo"; "http://alene2.files.wordpress.com/2010/02/2-4720circulo20verde_180.jpg")))

La fórmula no es compleja, es larga porque incluye tres url de la s tres imágenes. El argumento opción es opcional (no es una redundancia, no se me confundan con las palabras), y permite elegir 4 formas de dimensionamiento de la imagen. IMAGE es una de las pocas funciones con una descripción completa en la ayuda de Google, que aparece mientras la escribimos en una celda, pero que para su conveniencia la pego a continuación:

IMAGE inserta una imagen dentro de una celda. =image(URL) o=image(URL; 1): al insertar esta fórmula en una celda, la imagen se escala para que quepa dentro de la celda seleccionada. Si la celda es mayor que la imagen que quieres insertar, el resto de la celda aparecerá en blanco.

=image(URL; 2): al insertar esta fórmula, la imagen se expande y se ajusta al tamaño de la celda seleccionada. La relación de

buscar  =image(URL; 3): al insertar esta fórmula, la imagen se inserta en la celda respetando su tamaño original. Si la imagen es mayor

Classic =image(URL; 4; height; width) : con esta fórmula se puede personalizar el tamaño de la imagen especificando los valores de altura y de ancho de la misma en píxeles. En esta opción es necesario indicar los parámetros height y width.

Insertar un gráfico en miniatu ra en una sola celda La función SPARKLINE(datos, opciones) lo hace: crea un gráfico con los d atos proporcionados, que se muestra dentro de la celda. El argumento opcional opciones permite elegir el tipo de gráfico y otras características. Es súper simple de usar, y el hecho de usarse como fórmula, le permite copiarse y pegarse para generar multitud de gráficos. Es usual crear un gráfico de éstos para cada fila de una tabla. Más info y ejemplos [https://support.google.com/drive/answer/3093289] (en inglés).

Importar datos d esde páginas web IMPORTHTML(URL, etiqueta, índice)  es una función impresionante. Simplemente vuelca en la hoja de cálculo el contenido de una tabla de cualquier página web. El siguiente ejemplo devuelve una búsqueda de Google de los términos de la celda A1: =ImportHtml("https://www.google.com.ar/search?q="&  A1; "list";0)

Otro ejemplo: esta fórmula en una celda produce una tabla larga de 4 col umnas y más de 300 filas, con todas l as funciones de las hojas de cálculo Google. =ImportHtml("https://support.google.com/drive/table/25273? hl=es&page=table.cs&rd=1";"table";0)

El segundo argumento, etiqueta, puede ser list o table, como se observa en los dos ejemplos. list vuelca los datos en una columna, table despliega una tabla completa. table busca la etiqueta html para extraer los datos. Si hay varias de estas etiquetas, el argumento final, índice, se usa para indicar cuál de todas, empezando por cero. Pero antes de que les explote el cerebro, les cuento al gunas limitaciones: • Sólo funciona con páginas web, accesibles por Internet. No funciona con los sistemas de tu oficina si no se pueden acceder por Internet. • Sólo funciona con páginas públicas, que no requieran autenticación (usuario y contraseña). • Las páginas web deben tener sus datos dispuestos en un tag o en algún tipo de lista, de otro modo no es posible acceder a ellos. La mayoría de las páginas web dinámicas no usan ninguna de estas formas de presentar datos. Son los servidores de Google los que acceden a la página web para extraer la información; no es el navegador del usuario el que lo hace. Por eso, aunque el usuario pueda acceder a un sistema privado, si los servidores de Google no tienen acceso libre, la función no podrá obtener los datos.

Complementando importHtml En general, los sitios web de diarios y noticias tienen páginas dinámicas y no usan etiquetas , y la función IMPORTHTML no funciona. Pero por suerte, la mayoría de estos mismos sitios web publican su información por medio de los denominados feeds. En estos casos, la función IMPORTFEED viene al rescate. Si bien se puede usar de la manera tradicional IMPORTFEED(URL), su sintáxis es más completa, permite filtrar los datos, elegir las columnas a mostrar y limitar la cantidad de filas: ImportFeed(URL; query; headers; numItems)

Finalmente, para casos más selectos, existe la función IMPORTXML, cuyo nombre lo dice todo.

Importar un archi vo CSV Un archivo CSV es un archivo que contiene una tabla en formato de texto, separando las columnas por comas. Se puede importar de varias maneras. Una es subir el archivo CSV a Google Drive y convertirlo a hoja de cálculo Google. Otra

consiste

en

usar

fórmulas

como

se

muestra

en

este

otro

artículo

[http://hojasdecalculogoogle.blogspot.com.ar/2013/07/limpiando-los-textos-separando-en.html ] . La otra manera es la que vengo

a comentar aquí, usando la función IMPORTDATA(URL).

El archivo CSV debe estar accesible en Internet vía la url indicada en el argumento. Una manera de poner su propio

buscar  De manera similar a lo comentado para la función IMAGE, se puede cambiar la url para mostrar diferentes tablas en la Classic

Traducciones e idioma En el raro y muy específico caso en que hagas alguna hoja de cálculo compartida e internacional, este par de funciones te pueden ser útiles: • DETECTLANGUAGE(range)  intenta identificar el idioma a partir del contenido de un rango • GoogleTranslate(texto; id ioma del texto; idioma a t raducir) traduce un texto Los idiomas usan la codificación de Internet: es para castellano, en para inglés, es-419 para castellano latinoamericano...

Publicado 30th July 2013 por Alejandro Silvestri Ver comentarios

29th July 2013

Limpiando los textos: Separando en columnas, extrayendo la parte que sirve

Un uso muy  [http://www.macstore-mx.net/wp-content/uploads2/2010/06/Captura-de-pantalla-2010-06-29-a-las-9.57.13-AM.png] difundido de las hojas de cálculo es el acondicionamiento o fomateo del texto, que proviene de algún sis tema o de cualquier "fuente de información", y hay que manipularlo para subirlo en otro sistema. Es muy común obtener tablas de datos bajo la forma de texto separado por comas, o encolumnado con espacios intercalados, y a veces no se consigue abrir como hoja de cálculo de forma automática, y hace falta separar los datos a mano.  A continuación se plantean situaciones típicas, y donde es posible, dos soluciones para cada u na: la solución clásica, conocida por todo aquél que domina las hojas de cálculo, y una solución nueva, que hace uso de nuevas tecnologías de hojas de cálculo, sólo conocidas por los iniciados en estas artes.

Texto encolumnado Un formato de exportación de tablas que utiliza la mayoría de los sistemas es el de texto encolumnado. El sistema genera un archivo de texto, con una cantidad de caracteres determinada para cada columna. Si el contenido es más corto, lo rellena con espacios. La manera más práctica de separar el texto en columnas consiste en pegar todo en la columna A, a partir de la celda A2 (dejando la fila 1 libre), y luego separar en columnas a pa rtir de la B hacia la derecha. Veamos un ejemplo con un texto de cuatro columnas, de manera que: • la primer columna abarca 15 caracteres • la segunda, 10 • la tercera y la cuarta, 5 cada una El texto de las dos primeras filas, en las celdas A2 y A3, es:  A2)Nombre

Apel l i do

Edad Nac.

 A3) J osef a

Manol

83

USA

En la fila 1, a partir de B1, anotamos la posición del caracter inicial de cada columna:

B1) 1 C1) 16 D1) 26 E1) 31 F1) 1000 Siempre tendremos 1 en B1. Para hacer funcionar las fórmulas de manera elegante, se agrega un valor alto (1.000 por ejemplo) luego de la última columna. En B2 introducir la fórmula: B2)=mid($A2; B$1; C$1-B$1)

Copiando esa fórmula a las cuatro columnas, y hacia abajo hasta el final de la lista de la columna A, se reconstruye la tabla de datos.

La solución nueva buscar  Classic La fórmula volcará el resultado hacia las celdas de l a derecha automáticamente. No hace falta como en el caso anterior poner los valores en la fila uno. Y como se puede apreciar, esta fórmula ya contiene los valores de la cantidad de caracteres que corresponde a cada col umna. Para generalizar, por cada columna se agrega el código: (.{#})

reemplazando # por la cantidad de caracteres de esa columna.

El formato CSV Los archivos con extensión CSV tienen uno de los formatos de tablas de datos más simples y compatibles en toda la industria: son archivos de texto, que separan con comas el contenido de cada celda de una fila, y con salto de línea las filas. Las hojas de cálculo Google se pueden descargar en este tipo de archivos, y viceversa, al subirlos Drive lo reconoce y lo convierte a hoja de cálculo. No obstante, no sucede lo mismo si en vez de subir el archivo, pegamos el texto directamente en una hoja de cálculo.  Al pegar, las hojas de cálculo usan los saltos de líneas para separar el texto en celdas de forma vertical, pero no realizan una separación horizontal. Si bien se puede llenar la hoja de cálculo con fórmulas para separar este texto en columnas, a veces la manera más práctica es programar una "macro" (en rigor se llaman "secuencias de comandos"). Menú Herramientas, Editor de secuencias de comando...  , pegar lo que sigue y guardar: function separarH(texto, separador){ return texto.split(separador); }

Si el texto CSV se pegó en la columna A, en B1 se introduce la siguiente fórmula, y luego se copia a lo largo de la columna B: B1)=separarH(A1; ",")

y listo. Notar que el segundo argumento indica el caracter que se usará como separador. En otro caso podría usarse punto y coma, por ejemplo. La fórmula se introduce en la columna B ¿Y el resto de las columnas? Esta función, por simple que parezca, es una función matricial, y su resultado "desbordará" hacia las siguientes columnas de manera horizontal , ocupando todas las que necesite.

La solución nueva Sin necesidad de secuencias de comandos, suponiendo que se van a obtener cuatro columnas, la siguiente fórmula lo resuelve: B1)=regexextract(A2; "(.*),(.*),(.*),(.*)")

Esta fórmula se extiende automáticamente hacia la derecha para mostrar las cuatro columnas. Generalizando, para cada columna hace falta agregar ,(.*) Los amantes de la automatización pueden preferir esta forma, para 100 columnas: B1)=regexextract(A2; rept("(.*),"; 100 - 1) & "(.*)")

Separar dir ecciones de email Es usual copiar listas de distribuciones del campo PARA de un correo electrónico, para luego hacer una lista en una hoja de cálculo. El primero problema es que todas las direcciones de correo se pegan en una única celda, produciendo algo parecido a esto, pero más largo: "Pepita

Manuela"

,"Manolo

José"

<  [email protected]  >,"Josefa

Manol"



Se pueden separar por coma, usando la función anterior. Sin embargo en este caso resulta más útil que las c eldas separadas se desplieguen hacia abajo en vez de hacia la derecha. Para esto se puede transponer el resultado:

B1)=transpose(separarH(A1; ","))

buscar  separarV:

Classic function separarV(texto, separador){

return texto.split(separador).map(function(e){return [e];}); } B1)=separarV(A1; ",")

Destri par direccion es de email GMail usa el formato de direcciones "nombre" . Suele ser necesario extraer nombres y direcciones de correo en columnas separadas.

La manera clási ca con MID y SEARCH La función MID(texto, inicio, longi tud), extrae una determinada cantidad de caracteres del texto dado, a partir de una posición inicial. La función SEARCH(texto buscado, texto donde buscar, posición inicial), indica en qué posición se encontró el texto buscado. Combinándolas, usando SEARCH para determinar dónde están las comillas, y los signos < y >, y extraer el nombre en la celda B1 y la dirección en la celda C1:  A1)"Pepita Manuela"   B1)=mid(A1; 2; search(""""; A1; 3)-2) C1)=mid(A1; search("<"; A1)+1; search(">"; A1)-search("<"; A1)-1)

La solución nueva

 A1)"Pepita Manuela"   B1)=regextract(A1; """(.*)""") C1)=regextract(A1; "<(.*)>")

Estas fórmulas, evidentemente más compactas que las clásicas, esconden un lado oscuro: el segundo argumento corresponde a una expresión regular , donde cada símbolo tiene un significado no evidente, que es necesario estudiar para poder adaptarlas a otras situaciones. Un poco más sobre las funciones de expresiones regulares, en este otro artículo [http://hojasdecalculogoogle.blogspot.com.ar/2013/06/busqueda-avanzada-de-textos-dentro-de.html?view=sidebar ] .

Unir direcci ones de email La inversa es también es bastante común. Teniendo una columna con los nombres y otra con las direcciones de email, se hace necesario concatenarlas todas en un único texto que se pueda copiar y pegar en el campo PARA de un correo electrónico. Este ejemplo es para GMail, que acepta el formato "nombre" . Para el ejemplo supongamos que la tabla con diez contactos está en el rango A1:B10; sus nombres en la columna A y sus direcciones en la columna B. No voy a desarrollar la tediosa solución clásica que necesita un montón de celdas auxiliares, sino que voy directo a la solución nueva: la fórmula matricial (matricial = mágica) que lo compone todo en un solo paso, en una sola celda: =arrayformula(join(","; """" &A1:A10 & """ <" &B1:B10 & ">"))

Esta celda reunirá las diez direcciones de correo electrónico, lista p ara copiar y pegar en el campo PARA. La función central es  JOIN(separador, rango de celdas), que produce un único texto con el contenido de las celdas separadas por coma, punto y coma o cualquier cosa que se indique en el argumento "separador", que no está limitado a un único caracter. La fórmula matricial se utiliza para expandir las posibilidades de JOIN, que en lugar de tomar los contenidos de las celdas y unirlos con coma, toma una operación más completa, la concatenación de comillas, símbolos, una celda de la columna A y otra de la B. Más

detalles

sobre

las

fórmulas

matriciales

en

este

[http://hojasdecalculogoogle.blogspot.com.ar/2013/06/liberando-la-energia-del-atomo-la.html?view=sidebar ] .

otro

artículo

buscar  Classic

REGEXEXTRACT. Sus aplicaciones son enormes, pero su dominio requiere bastante lectura y mucha práctica. Creo que ya no quedan nuevas funciones sencillas para incorporar en el mundo de las hojas de cálculo. En lo sucesivo, cualquier función potente requerirá cada vez más dedicación y experiencia.

Publicado 29th July 2013 por Alejandro Silvestri 2

Ver comentarios

27th June 2013 Macros en las Hojas de Cálculo Google: la interfaz de usuario [http://optimistas.es/wp-content/uploads/2013/05/Imaginaci%C3%B3n-la-clave-del-%C3%A9xito.jpg]

[http://optimistas.es/wp-content/uploads/2013/05/Imaginaci%C3%B3n-la-clavedel-%C3%A9xito.jpg]

Dicen que la imaginación adopta una de dos formas: la de actividad creativa, de donde surgen innovaciones tecnológicas y obras como Harry Potter, o la de mera fantasía, una actividad estéril y a veces perniciosa. La característica central de la actividad creativa no radica en su resultado (la mayoría de las veces fracasa), sino en que se encuentra atada a la realidad: la imaginación explora posibilidades desde una premisa real. Para potenciar su creatividad, este artículo se dedica a presentar la realidad de las interfaces de usuario, qué pueden y qué no pueden hacer con ellas las macros de hojas de cálculo Google, y evitar así perder el tiempo intentando lo imposible.

La seguridad ante todo La mayor virtud de las hojas de cálculo Google es su facilidad para compartir y colaborar. Esta característica sería el camino preferido para virus y troyanos, si Google no fuera estricto con la seguridad de sus productos. Las limitaciones que sufren los scripts para agregar elementos a la pantalla del usuario se deben a que Google quiere evitar que los desarrolladores abusen de los usuarios. Con esto en mente, será más fácil entender por qué las cosas son como son

Yo en Excel esto lo pod ía hacer  Lo que llamamos macros, son secuencias de comando contenidas en una hoja de cálculo Google. Estas secuencias pueden interactuar muchos productos de Google (Google Docs, Drive, GMail, Calendar...) para acceder y manipular su contenido, pero están muy limitados para interactuar con la página web que el usuario tiene ab ierta. En otras palabras, una macro puede modificar celdas de una hoja de cálculo que el usuario no tiene en pantalla, pero no puede abrir una ventana del navegador para mostrar la hoja de cálculo. Esto es muy diferente a Excel, que para modificar una celda requiere abrir la hoja de cálculo, y ésta a parece en pantalla.

buscar  Classic

[http://kissflow.com/wp-content/uploads/2012/11/Google Apps-workflow-analytics.png]

Por seguridad, Google decidió limitar la injerencia de las macros en la interfaz de usuario a unas pocas acciones funcionales. Esto es lo que pueden hacer: • Cambiar la hoja activa • Cambiar la selección (la posición del cursor) • Agregar un menú y sus respectivos submenúes • Cada submenú puede desplegar otro submenú, o disparar una script • El menú se agrega para una hoja de cálculo, no para todas • Adosar un script a un dibujo o a una imagen • que se dispara cuando se hace clic • Presentar una ventana de diálogo • Presentar un cuadro lateral Por otro lado, los scripts no pueden: • Cambiar el contenido ni la posición de los menúes predeterminados • Agregar botones a la barra de herramientas • • • •

Agregar controles a la hoja de cálculo (es decir, zonas interactivas) Usar el menú Usar botones de la barra de herramientas Agregar menúes generales que se presenten en todas las hojas de cálculo

• Abrir otra hoja de cálculo en la pestaña actual, ni en una pestaña nueva • En rigor, no pueden navegar nada • No tienen acceso a la página web • No tienen acceso al DOM de la interfaz de la hoja de cálculo • Por ejemplo, no pueden insertar elementos HTML

Menú Como

comenté

en

un

artículo

anterior  [http://zef.me/wp-content/uploads/2010/03/gas-menu.png]

[http://hojasdecalculogoogle.blogspot.com.ar/2013/06/macros-en-hojas-decalculo-google.html] , lo usual es agregar el menú en el momento en que se abre la hoja de cálculo, recordando que no

perdura luego de cerrar la hoja de cálculo. En rigor, el menú se agrega a la instancia actual de la interfaz de Hojas de Cálculo Google, que es una página web que se crea al ca rgarse, se cierra al abandonarse, y los cambios no persisten.

f unct i on onOpen() { Spr eadsheetApp. get Acti ve() . addMenu( ' Menú agr egado' , [{ name: ' Submenú que di spar a l a f unci ón nombr eDeLaFunci ónALl amar ConEl Menú' , f unct i onName: ' nombr eDeLaFunci ónALl amar ConEl Menú' }, { name: ' Ot r o submenú que di spara ot r a f unci ón' , f unct i onName: ' nombr eDeLaOt r aFunci ónALl amar ConEl Menú' }] ); } El menú se agrega a la derecha de los menúes predeterminados. Al elegir un menú, el usuario dispara la función asociada, que se invoca sin argumentos. La estrategia de menú no está pensada para modificarlos sobre la marcha. Una vez agregado el menú, no se pueden insertar nuevos submenúes, ni inhabilitar otros. En el mejor de los casos se puede eliminar el menú. Tampoco dispara eventos al desplegarse. El único evento de menú es el clic de usuario en un submenú, que invoca la función asociada.

buscar  Classic Ésta es la única manera de agregar botones a la hoja de cálculo. No son botones animados, pero cumplen la función.

Mensajes y entrada d e datos

[http://1.bp.blogspot.com/-Itaq0w52Ku4/ULzmkxoOBxI/AAAAAAAAA9U/aBX3jRO56JU/s1600/4.png ]

La clase Browser [https://developers.google.com/apps-script/reference/base/browser?hl=es-ES ] tiene el método msgBox , para presentar al usuario una ventanita con un mensaje y algunos botones (Aceptar, Cancelar, etc.), y el método inputBox , para solicitar un texto al usuario. Ambos métodos son muy limitados, y por ejemplo: • El mensaje que muestran en la ventana no tiene formato y es de un solo párrafo: ignoran los saltos de línea. • inputBox  no admite un texto predeterminado. La casilla de texto que el usuario debe completar siempre aparece vacía. • No hay manera de controlar el tamaño ni la posición ni el formato de la ventanita. Sirven para su propósito específico, y no admite usos no ortodoxos. Por otro lado son muy fáciles de usar, pues no requiere elaborar una estrategia de programación asincrónica ni oir eventos, pues la ejecución del script se pausa hasta la respuesta del usuario: var nombre = Br owser. i nputBox( ' ¿Cómo te l l amás?' ) ;

Cuadros de di álogo y barras laterales: HTML Services Un cuadro de diálogo es una ventana flotante sobre la hoja de cálculo. No es una ventana del navegad or, y sólo [http://holageek.com/v1/wp-content/uploads/2011/07/html-full.jpeg] puede estar en la página web de la hoja de cálculo. El usuario no puede continuar usando la hoja de cálculo mientras tenga la ventana abierta. Una barra lateral (o sidebar ) es una ventana vertical que se alínea a la derecha de la pantalla, y sí permite al usuario continuar usando la hoja de cálculo.

El contenido de ambas es (casi) totalmente libre, y se escribe en HTML, mediante HTML Services . Esta página explica en inglés el uso de HTML Services buscar  [https://developers.google.com/apps-script/guides/html-service?hl=esES#serve_html_as_a_google_docs_sheets_or_forms_user_interface] . El tema no es trivial, requiere un tiempo de aprendizaje

Classic Para evitar confusiones, HTML Services es la versión nueva y definitiva de Google para interfaces de usuario en sus productos, que reemplaza a la primera, UI Services [https://developers.google.com/apps-script/uiapp?hl=es-ES] , más restrictiva y con un paradigma diferente al HTML. Si alguien oyó hablar de GUI Builder , una página para crear interfaces de usuario, sepa que entró en obsolescencia en Marzo de 2013, y ya no conviene dedicar esfuerzo para aprender su uso. HTML Services permite al desarrollador de scripts escribir un cuadro de diálogo o una barra lateral directamente en HTML (incluso en HTML5). Sin embargo, por razones de seguridad, HTML Services es un poco más restrictivo que el HTML común, y  [http://otake.com.mx/Wallpapers/Html5-01-1024x768.jpg] pasa por un proceso denominado Caja Sanitization, que verifica que se respeten las directivas de seguridad, que impiden un uso malintencionado. Por lo demás, HTML Services es la única manera de ejecutar código en el navegador, como contraste a los scripts de Google que se ejecutan en sus servidores.

Princip ales restricciones d e HTML Servic es  A modo de reseña, listo algunas restricciones:

[https://code.google.com/p/google-caja/logo]

• Usa el modo estricto de HTML, lo que significa que no debe contener errores (por ejemplo, tags inexistentes, o que se abren y no se cierran) • No admite código javascript escrito en el propio HTML: los tags SCRIPT deben usar SRC="nombre de archivo.js" • Tiene limitaciones para agregar tags SCRIPT de forma dinámica por código, y no admite JSONP • No admite el uso de IFRAME • Al código javascript no se le permite acceder al navegador, solamente al DOM del propio HTML • No admite la fuinción eval Para saber más, les recomiendo la descripción completa de Caja Sanitization [https://developers.google.com/appsscript/guides/html-service-caja?hl=es-ES] (en inglés). Publicado 27th June 2013 por Alejandro Silvestri 7

26th June 2013

Ver comentarios

Macros en Hojas de Cálculo Google: rangos, hojas, planillas de cálculo

Las hojas de cálculo

 [http://www.fondos10.net/wp-content/uploads/2010/06/Hoja-verde.jpg

Google permite acceder a sus hojas y celdas de forma programática, por medio del lenguaje Google Apps Script. La forma en que se organizan los objetos es ] similar a la que

implementa Excel para sus macros de Visual Basic, pero puede resultar un poco confusa para el recién llegado, aun cuando tenga experiencia usando hojas de cálculo e incluso usando lenguajes de programación.

Continuando con mi artículo anterior  [http://hojasdecalculogoogle.blogspot.com.ar/2013/ 06/macros-en-hojas-de-calculo-google.html] , en

Classic

éste pasearemos por los objetos principales de Spreadsheet Services [https://developers.google.com/appsscript/reference/spreadsheet/?hl=es-ES] ,

mostrando la manera en que se combinan para acceder a los datos.  [http://marocan.com.mx/images/mcalculo.png ]

Rompiendo mitos Google Apps Script no es un

lenguaje dedicado exclusivamente a las macros de hojas de cálculo, sino que tienen muchos otros usos. Por eso, cada macro de hoja de cálculo inicia sin asumir que hay una hoja de cálculo abierta, [http://disepro.com.ar/wp-content/uploads/2013/04/Google.jpg ]y lo usual es que l as primeras instrucciones sean las que determinan con qué hoja de cálculo se va a trabajar. Tampoco se llaman macros, sino secuencias de comando, o scripts. Finalmente, Spreadsheet Services representa una hoja de cálculo bajo la forma de un objeto Spreadsheet, una hoja como objeto Sheet, y un rango de celdas como un objeto Range. Pero no existe un objeto para una celda. En rigor, una celda es un rango de 1 x 1, de una fila de altura y una columna de ancho. En este artículo nos enfocaremos en los scripts para hojas de cálculo Google, que el usuario ejecuta desde un menú, o como función en una fórmula.

Los servicios inmediatos de SpreadsheetApp Las Spreadsheet Services de Google Apps Scripts presentan los elementos que constituyen una hoja de cálculo, en forma de objetos  de javascript. La clase SpreadsheetApp es la puerta de entrada a las hojas de cálculo Google. La clase tiene algunos métodos muy cómodos para acceder a la hoja d e cálculo abierta: • Hojas de cálculo • Spr eadsheetApp. get Act i ve( ) • Devuelve un objeto Spreadsheet, que representa la hoja de cálculo activa, es decir, aquélla desde donde el usuario disparó el script. • Spr eadsheetApp. openByUrl ( url ) • Abre otra hoja de cálculo. Ot ros métodos: .open y .openById  • Hoja s • Spr eadsheetApp. get Act i veSheet ( ) • Devuelve un objeto Sheet, que representa la hoja activa, es decir, la hoja seleccionada. • Rango • Spr eadsheetApp. getAct i veRange() • Devuelve un objeto Range, que representa el rango seleccionado, es decir, el rango iluminado con el cursor. De esta manera se accede directamente a objetos que representan los elementos que el usuario ha sele ccionado.

Escarbando hasta llegar al rango Para poner en una fórmula alguna referencia a otra celda, se utiliza la sintaxis del ejemplo: 'Hoja 1' ! $A$5:B10

Ésta no es la sintaxis que se utiliza con los objetos. Para acceder al mismo rango por medio de los objetos de Spreadsheet Services, se usa: Spr eadsheet App. get Act i ve( ) . get Sheet ByName(" Hoj a 1") . get Range( 5, 1, 6, 2)

que se interpreta mejor escrito en varias líneas: / / La "spreadsheet " act i va, desde cuyo menú se di spar ó el scr i pt var hojaDeCálculo = SpreadsheetApp.getAct i ve( ) ;

buscar 

/ / La hoj a l l amada Hoj a 1. . get Sheet ByName(' Hoj a 1' ) ;

Classic var rango = hoja1. get Range(5, 1, 6, 2);

Las filas y las col umnas se numeran desde la 1 (en vez de 0 como suele ser e n  javascript), y de esta manera la columna  A es la 1. Esta forma de expresar los argumentos al método getRange es útil cuando el tamaño y posic ión del rango surge de algún cálculo numérico, pero es incómodo cuando el programador conoce el rango expresado en el formato  A1. Hay que notar además que el método getRange no tiene un argumento para indicar la h oja, sino que es un método que se aplica sobre la hoja, en este caso hoja1.  Afortunadamente, el método getRange admite que el argumento le sea p roporcionado en formato A1, de manera que la última línea de código se puede escribir también así: var rango = hoja1. get Range(' A5:B10' ) ;

Para expresar el rango se pueden usar mayúsculas o minúsculas indistintamente. Hay que notar que para getRange los signos $ son irrelevantes, pero n o molestan. $A$5:B10 y A5: B10 son el mismo rango. Incluso se puede usar la sintaxis expandida, incluyendo la hoja, como en este ej emplo: var rango = hoja1. get Range(' Hoj a 2!A5:B10' ) ;

Esta última forma requiere varios comentarios. Primero, el rango devuelto es el del argumento, es decir, un rango de la Hoja 2. La Hoja 1, implícita en el objeto hoja1, se ignora en favor de la hoja indicada en el argumento. Entonces ¿para qué hace falta el objeto hoja1? Pues no hace falta, y el método getRange se puede llamar directamente desde el objeto Spreadsheet, así: var rango = hojaDeCálculo. getRange(' Hoj a 2!A5:B10' ) ;

Segundo, el nombre de la hoja (Hoja 2 en el ejemplo) no va entre comillas como acostumbra en las fórmulas de las hojas de cálculo. Seguramente confunden las comillas que el lenguaje  javascript require para indicar que se proporciona un texto, pero el texto suministrado difiere en ese detalle, y lamentablemente no admite las comillas. Es común copiar una referencia de una fórmula directamente al editor de secuencias de comando, dejando las comillas que producirán un error al ejecutarse el método. Para dejar clara la comparación: • Referencia en una fórmula: 'Hoja 2'!A5:B10 • Referencia en un texto de javascript: Hoja 2!A5:B10 Finalmente, también se puede obtener el rango p or su nombre de intervalo (s uponiendo que se le asignó un nombre) con: var r ango = SpreadsheetApp.getActi ve( ) . getRangeByName( "mi Cel da") ;

Escarbando más p ara llegar al cont enido de una celda Como la celda es solamente un caso particular de rango, caben distinguir dos maneras principales para leer o escribir un rango: • rango. get Val ue() • rango. setVal ue(' Nuevo cont eni do de l a cel da' ) • Estos métodos obtienen o escriben un único valor constante a una única celda: la celda superior izquierda del rango • rango. get Val ues() • rango. set Val ues( mat r i zDeConteni dos) • Estos métodos difieren de los anteriores en que se expresan en plural (etValue s) • Obtienen o escriben todos los valores del rango, por medio de una matriz de dos dimensiones, del mismo tamaño. Un ejemplo para ilustrar el uso de setValues: hojaDeCálculo. getRange(' Hoj a 2!D2:E3' ) . setVal ues(

[[ ' Texto en d2' , 100],

[ ' Texto en E2' , ' Texto en E3' ]]

);

El contenido puede ser numérico o de texto. Los paréntesis conforman la matriz de dos dimensiones en javascript, que se interpreta como un vector ( array) de filas, cada una consistente en un vector de contenidos de celdas: [ [fila 1 y col umna 1 , fila 1 y columna 2] , [fila 2 y columna 1 , fila 2 y columna 2] ]

buscar 

Es muy importante tener en cuenta que setValues exige que el tamaño de la matriz sea exactamente igual al tamaño del

buscar  Classic

var f i l a I n i c i al = 2; var col umnaI ni ci al = 4; / / corr esponde a l a col umna D var matr i zDeCont eni dos = [ [' Texto en d2' , 100] , [ ' Text o en E2' , ' Texto en E3' ] ]; hoja2. getRange(  

f i l aI ni ci a l , c ol u m n aI ni c i al , mat r i zDeConteni dos. l engt h,

  matr i zDeCont eni dos[ 0]. l ength ) . setVal ues( matr i zDeCont eni dos) ;

Cuando de fórm ulas se trata Lo anterior sirve para leer el contenido de una celda, que es el resultado de su fórmula, y para escribir constantes: textos o números. Para interactuar con las fórmulas se usan los métodos getFormula, setFormula, getFormulas, setFormulas. rango. setFormul a(' =SUM( A1:A5) ' ) ;

Es necesario anteponer el signo =, como de costumbre. Cabe señalar que setFormulas se usa para escribir las fórmulas de todas las celdas de un rango, proporcionando cada una en una matriz. Si lo que se pretende es escribir la misma fórmula en todo el rango, hay que ejecutar una acción de copiado: primero se escribe una fórmula en una celda, y luego se copia esa celda a un rango de esta manera: rangoConLaFórmula. copyTo( rangoALlenarConFórmulas) ;

Notación A1 y n úmero de fila y columna Spreadsheet Services admite rangos en formato A1, o indicando la hoja, la posición d e la fila, la columna, el a ncho y el alto del rango. Estos ejemplos demuestran cómo convertir de una notación a la otra:

Convertir de notación A1 a números var r ango = Spr eadsheet App.get Act ve( ) . get Range( ' Hoja 1!B5:G10 ' ) ; var nom breDeLaHoj a = r ango.get Sheet ( ) . get Name() ; v ar f i l a I n i c i a l =

r a ngo . g et R ow( ) ;

var columnaI ni ci al = r ango. get Col umn() ; var al t o =

r ango.get NumRows( ) ;

var ancho =

r ango. get NumCol umns( ) ;

Convertir de números a notación A1 var rango = Spr eadsheet App.get Act ve( )   . get SheetByName( nombreDeLaHoja)   . get Range( filaInicial,  

columnaInicial,

 

alto,

 

ancho

); var notaciónA1  = " ' " + nombreDeLaHoja  + " ' ! " + rango.get A1Notati on( ) ; El método getA1Notation devuelve solamente la referencia de filas y columnas (en este caso "B5:G10"), pero sin el nombre de la hoja, que en el ejemplo está agregado por concatenación.

Seguinos en la comunidad en castellano de Hojas de Cálculo Google [https://plus.google.com/u/0/communities/117381564739239305447

Publicado 26th June 2013 por Alejandro Silvestri Ver comentarios

]

Qué puedo hacer con mi tabla de datos Classic

Estrategias para la co nfección d e tablas de datos Una tabla de datos no es más que un cuadriculado rectangular con texto, números o fechas en sus casilleros, pero con una peculiaridad: está destinada a ser interpretada por fórmulas de hoja de cálculo. Por eso, ateniéndose a ciertas reglas sencillas, se potencia su explotación. En este punto cabe señalar la diferencia entre una tabla de datos para análisis, y una tabla de presentación de datos. La tabla de presentación de datos está diseñada para que la lea una persona: abundan los colores y las descripciones; el formato es importante, no es raro encontrar dos o tres filas para los encabezados, con celdas combinadas para los "encabezados de encabezados", como ésta:

[https://fq3esopfr.wikispaces.com/file/view/Tabla+de+datos+experimento+de+toallas.png/352952672/Tabla%20de%20datos%20exper  imento%20de%20toallas.png]

En cambio, una tabla de datos para análisis prioriza el "acceso automatizado" a los datos. Se le puede aplicar formato (colores, bordes, etc.), pero sin afectar su estructura. Por ejemplo, no se deben combinar celdas ni tener más de una fila de encabezados:

 [http://4.bp.blogspot.com/_SaM7_5Yzn3k/SedfLlk04wI/AAAAAAAAABU/wwQqkajEN6E/s320/tabla+de+datos.JPG]

En mi experiencia, ésta es la mejor manera de organizar las tablas de datos en Hojas de cálculo Google: "Las máximas de las tablas de datos". 1. Única: Una sola tabla de datos por hoja 2. Nominada: Nombrar la hoja de manera elocuente, para que se entienda el contenido de la tabla 3. Vértice: Crear la tabla a partir de la celda A1, sin d ejar filas o columnas intermedias en blanco 4. Limpia: En la hoja, no usar celdas que no pertenezcan a la tabla 1. No agregar nada después de la última fila: no agregar fila de totales 2. No agregar nada a la derecha de la tabla (excepto nuevas columnas de la tabla) 5. Encabezada: Reservar la fila 1 para los encabezados, y resaltarla en negrita 1. Siempre en la fila 1 2. No anteponer títulos ni descripciones 3. Limitarse a una única fila de encabezados, y no combinar celdas 6. Definida: No dejar ninguna columna sin encabezado 7. Tipada: Mantener el tipo de dato en toda la columna. Por ejemplo, si es numérico, no intercalar un texto. Si es fecha, debe estar bien escrita. Los tipos de datos son: 1. Número 2. Texto 3. Fecha 8.  Ató mi ca:  Separar los datos en la mayor cantidad de columnas necesarias. Por ejemplo, a veces conviene separar el nombre en nombre y apellido; de esta manera se podrá buscar y ordenar por apellido. Evitar amontonar varios datos en una sola celda. Por ejemplo "argentino, varón, casado, mayor de edad" debería separarse en cuatro columnas. 9. Desagregada:  Los datos deben estar abiertos en la mayor cantidad de filas. Siempre es posible obtener la suma de varias filas, pero es imposible descubrir las filas a partir de su suma. 10. Rigurosa: No basta con que una persona lo pueda entender: lo debe entender la computadora. No es lo mismo 9 que "nueve". No es lo mismo 1/2/2013 que "primero de Febrero de 2013". 11. Explícita: Evitar el uso de colores para marcar filas o celdas. Las fórmulas no interpretan colores. Si hay que marcar algunas filas, se crea una columna para la marca, escribiendo VERDADERO en las que corresponda. En todo caso, los colores pueden ser redundantes.

buscar 

12. Pura: Corregir los errores que puedan haber en las celdas de la tabla. Un error en los datos produce error en las

buscar  Classic

Explotación de datos La tabla armada según las indicaciones se presta para una diversidad de usos. A continuación se explican los beneficios de esta estrategia, y se indican algunas formas de acceso a los datos.

La tabla que crece Un problema común es descubrir que cada vez que se agrega una fila a la tabla de datos, hay que corregir todas las referencias al rango. Si el rango anterior era Tabla!A1:F99, ahora será Tabla!A1:F100. La mejor técnica para evitar este inconveniente es referenciar columnas completas, así: Tabla!A:F . Por supuesto, es indispensable que la tabla esté limpia. Una desventaja es que los encabezados quedan dentro del rango. Si las fórmulas no funcionan con los encabezados, ésta es una alternativa a lo anterior: Tabla!A1:F10000 , donde 10.000 es un número de filas que nuestra tabla nunca tendrá. Otra manera consiste en agregar una fila final vacía, pintarla de naranja o rojo, y exigir que las nuevas filas se inserten siempre por encima de la fila vacía final. Si la fila vacía es la fila 100, el rango será Tabla!A1:F100. Cada vez que se inserte una nueva fila, el rango se actualizará automáticamente. La desventaja es que las fórmulas recibirán siempre una fila demás, la fila vacía. Además, esta estrategia no sirve para IMPORTRANGE, porque las referencias en otras hojas de cálculo no se actualizan dinámicamente. Luego de elegir la manera de referenciar la tabla de datos, es muy recomendable ponerle nombre al rango con el menú Datos, Intervalo con nombre y protegidos..., Añadir i ntervalo. El nombre más adecuado es el nombre de la tabla. De la misma manera, para no tener que actualizar los rangos manualmente cuando se agreguen columnas a la tabla de datos, la mejor estrategia es utilizar una columna final vacía (en los rangos ejemplos es la columna F), y disciplinadamente agregar columnas insertándolas antes de esa columna final.

Funciones t otalizadoras En otra hoja se desarrolla el análisis de los datos. Las "totalizadoras" son funciones de agregación, como SUM, COUNT, AVERAGE. Lo más conveniente es referenciar la columna, pues la mayoría de estas funciones no se ve afectada por el encabezado, por ejemplo: =SUM( Tabla!D ) Por lo general, estas funciones se aplican sobre una única columna. Una ventaja de referenciar los rangos es que si la columna se mueve cambiando de posición en la tabl a, los rangos se actualizan dinámicamente, y no hace falta modificar las fórmulas. Es muy recomendable tomarse un tiempo para asignarle nombre a estos intervalos (rangos) de columna. Suponiendo que la columna D corresponde a ingresos, la fórmula anterior se vuelve mucho más elocu ente de esta manera: =SUM( Ingres os )

Búsqueda y alternativas a VLOOKUP La función VLOOKUP (BUSCARV en Excel) es simple y potente, pero su diseño primitivo no responde a las exigencias de las hojas de cálculo avanzadas. Un par de limitaciones a modo de ejemplo: • Sólo busca por la primer columna del rango • La columna resultado se indica por un número, no por una referencia, y no se actualiza dinámicamente si la columna se mueve a otra posición Una alternativa consiste en combinar la función MATCH y la función OFFSET: =OFFSET(rango de resultado; MATCH( valor buscado; rango de búsqueda; 0)-1; 0; 1; 1) Parece complicado, pero sólo hay que definir tres argumentos, y basta copiar y pegar. VLOOKUP también es complicada, hasta que uno se acostumbra a usarla.

Generando minitablas de presentación Se puede filtrar filas y extraer una tabla reducida con sólo algunas de las columnas de la tabla de datos, mediante la función QUERY, que se explica mejor en este otro artículo  [ http://hojasdecalculogoogle.blogspot.com.ar/2013/06/tu-base-dedatos-en-hojas-de-calculo.html] , y que tiene esta forma:

=QUERY(Tabla!A:F; "select A, C where E='Pepito SRL' ")

buscar  Esta función genera la tabla completa; incluso reacomoda automáticamente su tamaño cuando cambian los datos de la Classic  A la hora de agregar un pie de tabla con totales, surge el problema del redimensionamiento de la minitabla. Como no se trata de una tabla de tamaño fijo, al crecer sobreescribe la fila al pie. Las dos estrategias son: • Colocar los totales encima de los encabezados (es decir, arriba en vez de abajo) • Colocar los totales bien abajo, donde sepamos de antemano que la minitabla nunca llegará

 Acc eder d esde ot ra h oja d e cálcu lo Con la hoja debidamente compartida, otras hojas de cálculo pueden obtener los datos de la tabla importando un único rango. Para estar preparado por si la tabla crece, conviene referenciar colum nas completas. =IMPORTRANGE("claveDeLaHoja123"; "Tabla!A:F") La manera en que se referencian celdas entre diferentes hojas de cálculo, hacen que la mejor práctica sea usar IMPORTRANGE exclusivamente para replicar los datos en la otra hoja de cálculo, y luego usar fórmulas en otras celdas para operar con ellos.

Columnas automáticas Frecuentemente es necesario complementar la tabla con columnas calculadas mediante fórmulas. En general, una columna como éstas contiene una misma fórmula copiada a todo lo largo, y la fórmula suele hacer referencia a datos de la misma fila. Mientras las columnas de datos contienen constantes, es decir, datos que no provienen de fórmulas, las columnas automáticas muestran datos calculados, y las fórmulas de cada fila se copian de la otra fila de la m isma columna. Un primer criterio consiste en "amontonar" todas estas columnas automáticas a la derecha, de modo que para a ctualizar los datos, baste con pegarlos a la izqui erda en un único rango, sin pisar las fórmulas. Un problema común consiste en la necesidad de copiar las fórmulas cada vez que se agregan filas. Esta tarea se puede automatizar a través de un uso no ortodoxo de  ARRAYFORMULA [http://hojasdecalculogoogle.blogspot.com/2013/06/liberando-la-energia-del-atomo-la.html ] . La técnica suele funcionar bien para fórmulas sencillas que no contengan funciones de agregación (SUM, COUNT, OR, AND), y consiste en reemplazar la fórmula original por una fórmula matricial que "crece sola" cuando se agregan filas, de la s iguiemte manera: Fórmula original de la fila 2: =IF(A2>B2; A2; B2) Fórmula reescrita para que crezca sola: =ARRAYFORMULA( IF(A:A>B:B; A:A; B:B) ) La reescritura se hizo en dos pasos: se reemplazaron las referencias a celdas (B2) por referencias a columnas (B:B), y se puso todo dentro de ARRAYFORMULA(). Esta fórmula debe escribirse en una celda de la fila 1. Como la fórmula se debe insertar en la fila 1, no permite escribir un encabezado. O por lo menos no de la manera que uno quisiera, pero sí de esta otra manera: =ARRAYFORMULA( IF(ROW(A:A)=1; "encabezado de la fórmula automátic a"; IF(A:A>B:B; A:A; B:B) )) Se ve complicado, pero como su forma es siempre la misma, u no se acostumbra con el uso.

Google Apps Script El eterno problema de las referencias a las tablas, es que hay que actualizarlas cuando crecen. Al escribir scripts, Google tiene una función que devuelve el rango ocupado de una hoja. Mientras la tabla de datos esté limpia, el rango ocupado coincide con la tabla completa. SpreadsheetApp.getActive().getSheetByName("nombre de la hoja").getDataRange()

Por otro lado, Google destaca la librería ObjService [ https://sites.google.com/site/scriptsexamples/custom-methods/gs-objects] de James Ferreira, que convierte una tabla en objetos de javascript y facilita su manipulación programática.

 Acc eso au to máti co por Inter net Una tabla así creada permite publicar sus datos para que sean accedidos por otras aplicaciones y páginas web, sin necesidad de usar la Hoja de cálculo Google.

Google Spreadsheet API) [https://developers.google.com/googleurl admite parámetros de filtro y ordenamiento, y se pueden realizar consultas usando los encabezados de la tabla de Classic Como siempre, el propietario controla la visibilidad y decide si hace público o no el contenido de su hoja de cálculo. La página web que accede debe tener permiso.

Documentación de la tabla Nadie lo sabe al principio, pero cualquier hoja de cálculo puede terminar creciendo hasta convertirse en un sistema informático. Antes de tener que migrar hacia un sistema informático a medida, uno de los primeros obstáculos que encuentra una hoja de cálculo en su expansión es la falta de documentación: nadie sabe ni recuerda con qué criterio se hizo la hoja de cálculo. Explorar cómo está diseñada una hoja de cálculo es un a tarea de investigación, y muy costosa, mucho más de lo que se suele creer. Por este motivo, es una excelente costumbre tomar nota de los criterios de diseño de una hoja de cálculo. La manera más práctica consiste en crear una hoja dedicada a la documentación de estos criterios. Yo personalmente suelo crear dos. No quiero seguir extendiendo este artículo, le dedicaré uno completo a la documentación.

Publicado 14th June 2013 por Alejandro Silvestri Ver comentarios

13th June 2013

Tu base de datos en Hojas de Cálculo Google [http://cadazaredes.files.wordpress.com/2010/02/banner_ger_redes_es1.jpg ]

En sus Hojas de Cálculo, Google implementó la función QUERY, para realizar consultas SQL sobre tablas de datos.

Las bases de datos de menor a mayor 

 [http://4.bp.blogspot.com/-6DIfZtVrGI/T0KjDAkrWAI/AAAAAAAAAAw/8HFspaXQJBQ/s1600/database.jpg]

Las hojas de cálculo fueron siempre el primer paso para implementar bases de da tos, preferentemente de una sola tabla y no mayores a algunas decenas de miles.de filas. Cuando la base de datos crece en cantidad y en complejidad, y se abre en varias tablas vinculadas, las hojas de cálculo dejan de ser eficientes, y conviene pasar a una bas e de datos formal, como Microsoft Access o Google Fusion Tables. Luego, si la base de datos requiere alta performance y es accedida por múltiples usuarios, conviene implementar un servidor propio con Microsoft SQLServer o MySQL. Este artículo se ocupa de las primeras, las bases de datos pequeñas que se suelen implementar en una hoja de cálculo.

buscar 

buscar  Classic • El nombre de la hoja es el único título de la tabla • La fila 1 se usa para encabezados de la tabla, comenzando por la celda A1, y sin dejar columnas intermedias en blanco • La tabla se llena a partir de la fila 2, hacia abajo de forma consecutiva sin dejar filas en blanco • Debajo de la tabla se dejan todas las filas en blanco. No se agregan fórmulas de suma ni nada. • Las columnas a la derecha se dejan en blanco, o bien, sin dejar columnas intermedias en blanco, se completan con fórmulas, idénticas en todas las filas No es imprescindible seguir estos pasos rigurosamente, pero si la tabla se elabora según estas indicaciones, sus datos podrán usarse de muchas maneras diferentes. Estas indicaciones evitan problemas posteriores.

Descrip ción de la tabla de datos Conviene siempre crear una hoja para "documentar" la tabla, para que otros puedan interpretarla. La documentación consiste simplemente en describir los siguiente: • Qué contiene la tabla. Suele ser un listado de "algo", que hay que definir. • Qué significa cada fila. Por ejemplo, en un listado de facturación, cada fila corresponde a una factura.. En una lista de clientes, cada fila corresponde a datos de un cliente. Si más de una fila corresponde al mismo cliente, entonces no es una lista de clientes, y falta identificar qué significa cada fila. • Qué significa cada columna. Los encabezados suelen ser elocuentes, pero también suelen estar restringidos en longitud, y muchas veces su significado depende del contexto y se presta a confusiones. Por eso conviene aclarar con lujo de detalles qué significa cada encabezado.

La funci ón QUERY QUERY es una función matricial propietaria de Google, que devuelve un rango con el resultado de una consulta SQL aplicada sobre una tabla de datos. QUERY no admite todas las funciones SQL, pero sí las principales. Entre otras cosas, al aplicarse sobre una única tabla, no utiliza FROM, ni JOIN ni ningún tipo de relaciones. La función QUERY permite elegir qué campos mostrar y en qué orden, filtrar filas y ordenarlas con algún criterio, y agrupar filas y calcular funciones de agregamiento, como SUM o COUNT. Su sintáxis más general es: QUERY(rango de la tabla de datos; texto de la consulta)

Perdón, ¿qué es SQL? La función QUERY requiere el argumento "texto de la consulta", que consiste en una expresión SQL, que en su forma más general es así: SELECT las letras de columnas que querés ver, en mayúsculas, separadas por comas WHERE expresiones para filtrar las filas GROUP BY encabezados o columnas que se agruparán cuando tienen el mismo contenido ORDER BY encabezados o columnas que se usarán para ordenar alfabéticamente Lo puse en varios renglones por claridad, pero va todo en el mismo argumento de texto. La cláusula SELECT es obligatoria, el resto es opcional. Ejemplos: • =QUERY(Tabla!A1:K118,“Select C” ) • Muestra solamente la columna C • =QUERY(Tabla!A1:K118,“Select C,D,G,H”) • Muestra esas cuatro columnas • =QUERY(Tabla!A1:K118,“Select C,D,G,H WHERE C>10”) • Muestra solamente las filas cuya columna C es mayor a 10 Las comparaciones y funciones, como >=, SUM, COUNT, no son las de la hoja de cálculo, sino las propias de SQL.  Algunas coinciden, otras no. Por ejemplo, el promedio se calcula con AVG(). La lista completa de cláusulas SQL [https://developers.google.com/chart/interactive/docs/querylanguage#Language_Clauses] admitidas en QUERY está en inglés.

Publicado 13th June 2013 por Alejandro Silvestri Ver comentarios

buscar 

web Classic

[http://image.shutterstock.com/display_pic_with_logo/109411/101550196/stock-photo-export-and-import-keys-shows-internationaltrade-or-global-commerce-101550196.jpg]

Las hojas de cálculo tradicionales pueden tomar datos de otra hoja, indicando la carpeta y el nombre del archivo. Así se crean fórmulas de referencia como ésta: =['C:\Documents and Settings\All Users\Documentos\hoja con da tos.xls']Tabla!A1

Los problemas comienzan cuando la hoja con datos se mueve, o nos enviamos por correo electrónico nuestra planilla de cálculo: la ruta ya no funciona. Por este y otros motivos, Google encaró la vinculación de hojas de cálculo de otra manera, con las siguientes diferencias: • La hoja vinculada no se indica por su nombre y carpeta (que pueden variar), sino por su clave key, que no varía nunca, no importa si se mueve de carpeta, si cambia de nombre o si ca mbia de propietario. No importa dónde esté en la nube, su key será siempre la misma. • La importación no se realiza por celdas, sino por rangos. De esta manera una única función IMPORTRANGE trae un rango completo (muchas celdas) de una sola vez, lo que lo ha ce mucho más eficiente y veloz. Con esto en mente, paso a describir su uso, intercalando algunos consejos prácticos.

La función IMPORTRANGE IMPORTRANG E es una de las pocas funciones "propietarias" de Google. Google prefiere adoptar las funciones estándares (que son las que utiliza Open Office), y sólo opta por crear una función propia cuando no tiene más remedio. Su sintáxis es la siguiente, con dos argumentos:

[http://wpmu.org/wp-content/uploads/2012/10/import-fi.jpg]

IMPORTRANGE( key  de la hoja de cálculo, rango ) El primer argumento es la key de la hoja de cálculo de la que se extraerán los datos. No hay una manera obvia para obtener la key de una hoja de cálculo, por eso describo cómo obtenerlo de su dirección web. Supongamos que la hoja de cálculo en cuestión tiene la sig uiente dirección: https://docs.google.com/spreadsheet/ccc?key=0AtqguwfEft7udE5oNlVCM2tNeWRYdFExSzR3bzJedVE#gid=0 La key  es esa secuencia fea de letras y números, que comienza después de key= y termina antes del #, & o cualquier símbolo que no sea letra ni número. A veces llega hasta el final. La experiencia me lleva a insistir: la key no incluye la

palabra key ni el signo = que le preceden.

buscar  'Hoja 2'!A23 (como siempre, el nombre de la hoja va entre comillas si tiene espacios)

Classic Los dos son argumentos de texto, por lo que deben ir entre comillas si son constantes. En el ejemplo, la función completa es: IMPORTRANGE("0Atq guwf Eft7udE5oNlVCM2tNeWRYdFExSzR3bzJedVE" , "Hoja 2!A23" ) IMPORTRANGE es una fórmula matricial, que devuelve un rango de datos extraídos de otra hoja de cálculo Google. Este rango se vuelca en la hoja de cálculo empezando por la celda donde se introdujo la función IMPORTRANGE, y extendiéndose hacia abajo y a la derecha las filas y columnas que tenga el rango elegido.

Característi cas de IMPORTRANGE

[http://static5.depositphotos.com/1031343/441/v/950/depositphotos_4412282-For-import-and-for-export-stamps.jpg ]

Un par de cosas que conviene saber: • Se permite un máximo de 50 funciones IMPORTRANGE en una hoja de cálculo Google. Es decir que se pueden importar hasta 50 rangos (que no es lo mismo que 50 celdas). • Por eso, en lo posible, conviene poner todos los datos a exportar   en un único rango, en una misma hoja, de manera que otras hojas de cálculo usen IMPORTRANGE una sola vez. • Los datos no se actualizan instantáneamente, pero tampoco demora mucho en actualizar. • En el argumento rango, el nombre de la hoja va siempre sin comillas simples. • Como toda función matricial, se extiende según el tamaño del rango, pisando cualquier contenido que hubiera en las celdas. Las celdas de este rango se resisten a ser borradas o eliminadas; la función IMPORTRANGE las "regenera".

Otras maneras de importar datos Google proporciona varias funciones propietarias para importar datos de Internet o de cualquier servidor que utilice los protocolos estándares: • IMPORTHTML , importa el contenido de una tabla de una página web. Hay que indicar la dirección de la págin a web, y un número: el índice de tabla, para señalar qué tabla se requiere cuando la página tiene varias. Para encontrar el índice, conviene probar números correlativos a partir de cero.

• IMPORTDATA , importa el contenido de un archivo con formato de tabla CSV o TSV, disponible en una dirección web. • IMPORTXML , importa una tabla XML • IMPORTFEED, importa un feed atom, quizás otros formatos también

Ejemplo de IMPORTHTML Esta fórmula en una celda produce una tabla larga de 4 columnas y más de 300 filas, con todas las fu nciones de las hojas de cálculo Google. =ImportHtml("https://support.google.com/drive/table/25273? hl=es&page=table.cs&rd=1";"table";0)

¿Se puede importar un rango de u n archivo Excel? No. O bien se convierte el archivo Excel en hoja de cálculo Google (una operación que no se puede automatizar), o se busca en Excel la manera de publicar su contenido de modo que quede accesible por HTML o XML.

Publicado 11th June 2013 por Alejandro Silvestri Ver comentarios

Classic

Macros en Hojas de Cálculo Google

10th June 2013

Seguinos en la comunidad en castellano de Hojas de Cálculo Google  [https://plus.google.com/u/0/communities/117381564739239305447]

[http://www.kuleuven.be/english/news/unprecedented-studyreveals-new-genetic-risk-factors-of-breast-prostate-and-ovarian-cancer/image]

Lo que en Excel se llama "macro", en las hojas de cálculo Google se denomina secuencia de comandos, o script, y se usan para dos fines: • Programas, que se ejecutan desde un menú • Funciones, para usar en fórmulas

El lenguaje Goog le App Script Mientras las macros de Excel se escriben en Visual Basic, basado en BASIC, las secuencias de comando de Google se escriben en Google Apps Script  [http://www.devfest.at/image/ahRzfmRldmZlc3QtYXQtaGVsbXV0aHISCxILU2Vzc2lvblRhbGsYkU4M ]

[https://developers.google.com/apps-script/overview?hl=es-ES ] , que está basado en  javascript, el lenguaje estándar de las

páginas web, y el más popular del mundo. Este artículo asume conocimientos de javascript. Si es necesario, se aprende muy fácil, basta con buscar "aprender  javascript" en Google o en Youtube. El editor de secuencias de comandos se usa para escribir el código, y se abre con el menú Herramientas, Editor de secuencias de comandos... La primera vez presentará una página de plantillas de secuencias de comandos. Es bueno dedicarle un tiempo y recorrer los tutoriales y la plantilla para hoja de cálculo. Pero para empezar un proyecto, conviene utilizar "proyecto en blanco". En lo sucesivo, si ya hay código escrito, al abrir el editor de secuencias de comandos se salteará la pantalla de plantillas, a la que se puede volv er con el menú Ayu da, Pant alla d e bi enveni da... Google Apps Script, o GAS, usa la sintaxis de javascript, pero su poder reside en las API, que le da acceso a la mayoría de los productos de Google, como Google Drive y GMail. A continuación recorreremos la  API de hojas de cálculo [https://developers.google.com/apps-script/reference/spreadsheet/?hl=es-ES ] , conocidas como Spreadsheets Services.

Spreadsheet Services: la A PI de las hoj as de cálculo El punto de entrada a las hojas de cálculo es el objeto SpreadsheetApp, que está siempre disponible (no hace falta ninguna preparación especial para cargarlo). A partir de aquí se puede acceder a la mayoría de los aspectos de una hoja de cálculo. Las partes de una hoja de cálculo está ordenada de la siguiente manera: • SpreadsheetApp

[https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app?hl=es-ES ]

representa la aplicación de hojas de cálculo. Para comparar, existen también GMailApp, DriveApp, MapsApp, CalendarApp... creo que se entiende. Entre otras cosas permite acceder a cualquier hoja de cálculo Google, esté abierta o no.

buscar 

•   Spreadsheet

[https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet.html?hl=es-ES ] buscar 

Classic

Spr eadsheetApp. get Act i ve( ) •   Sheet [https://developers.google.com/apps-script/reference/spreadsheet/sheet.html?hl=es-ES]

representa una hoja

dentro de la hoja de cálculo. Nótese que la jerga es equívoca, porque denomina "hoja" tanto al archivo en su conjunto como a cada hoja dentro de él, como el pie de Cortázar  [http://www.literatura.org/Cortazar/Instrucciones.html] . En inglés se denominan spreadsheet y sheet respectivamente. Dos maneras de acceder a una hoja (entre otras): • Spr eadsheetApp. get Act i veSheet ( ) •  Spr eadsheetApp. get Act i ve( ) . get Sheet ByName( ' Hoj a1' ) • Estas dos maneras se usan en los ejemplos que siguen •   Range  [https://developers.google.com/apps-script/reference/spreadsheet/range.html?hl=es-ES ] es la única manera de acceder a las celdas de una hoja. Representa un rango, es decir una matriz rectangular de celdas, en una hoja determinada. Para trabajar con una sola celda, se trabaja con un rango de 1x1. Ejemplos para obtener un rango, y trabajar con su contenido •   Spreadsheet App. get Act i veRange( ) • devuelve el rango de la selección actual. Normalmente corresponde a la posición del cursor, si no hay un rango mayor seleccionado. •  Spr eadsheetApp. get Act i ve( ) . get Sheet ByName( ' Hoj a 1' ). get Range( ' A1' ) • devuelve el rango de la celda Hoja1!A1 , de la hoja de cálculo activa. El argumento puede adoptar todas las formas: • • • •

A 1:B 2 A:A (columna A) 1:1 (fila 1) No admite Hoja1!A1. La hoja se debe indicar en el objeto anterior.

• Spr eadsheetApp. get Act i ve() . get Range( ' Hoj a 1!A1' ) • similar a la anterior, esta forma sí admite indicar la hoja en el argumento. Si no se indica, se asume la hoja activa. • Spr eadsheetApp. get Act i ve() . get Range( ' Hoj a 1!A1' ) . get Val ue( ) • devuelve el valor del contenido de la celda superior izquierda del rango •   SpreadsheetApp. getAct i ve() . getRange( ' Hoj a

1!A1' ) . set Val ue( ' Nuevo conteni do de l a

cel da' ) • escribe el contenido de la celda superior izquierda del rango

Funciones a medida Todas las funciones escritas en el editor de secuencias de comando de una hoja de cálculo, se pueden llamar desde la fórmulas en cualquier celda de la misma hoja de cálculo. Por ejemplo, esta secuencia de comandos (aquí se requiere algún conocimiento de  javascript):

 [http://omnific.net.au/images/ComputerCode_banner.jpg]

f unct i on másUno( númer o) { r et urn númer o + 1; } Se puede incluir en una fórmula en cualquier celda de la hoja de cálculo:

= MÁSUNO(5) Si bien las secuencias de comando distinguen mayúsculas y minúsculas, las funciones de la hoja de cálculo no. Escribiendo =másuno(5)  también funciona.

Lamentablemente las funciones de GAS no pueden utilizar las funciones de las hojas de cálculo Google. Spreadsheet

buscar  Classic solamente para calcular un valor, y no puede, por ejemplo, obtener la selección actual, modificar el valor de otra celda, agregar un menú, insertar un gráfico... Estas acciones quedan reservadas para secuencias de comando disparadas por el usuario, y no dentro de fórmulas. Un dato útil: cuando la función es llamada por una fórmula, Spr eadsheetApp. getAct i veRange()  representa la celda que se está recalculando en lugar de la posici ón del cursor.

Secuencias ejecutadas p or el usuario Estas secuencias tienen acceso irrestricto a todo el poder de Google Apps Script. Se pueden agregar menúes para generar hojas completas, crear otras hojas de cálculo, enviar emails e interactuar con otros productos de Google, con servicios de terceros, e incluso con servidores propios, si fuera el caso. Estas secuencias de comando se pueden disparar desde un menú a medida, o haciendo clic en imágenes y gráficos insertados en la hoja de cálculo. La siguiente función agrega un menú para disparar otra función de GAS:

f unct i on onOpen() { Spr eadsheetApp. get Act i ve() . addMenu( ' Menú agr egado' , [{ name: ' Submenú que di spar a l a f unci ón' , f unct i onName: ' nombr eDeLaFunci ónALl amar ConEl Menú' }] ); } La función onOpen es una de las pocas que tiene un nombre predefinido. onOpen se ejecuta automáticamente cada vez que se abre la hoja de cálculo. Como los menúes se agregan a la interfaz de la hoja de cálculo, y no a la hoja en sí misma, éstos desaparecen al cerrarla. Por eso es usual utilizar onOpen para volver a agregar los menúes cada vez que se abre la hoja. Las secuencias de comando de Google son muy poderosas, pero su dominio exige mucha lectura y práctica. Es imposible abarcarlo completamente en este artículo, que sirve apenas de introducción, pero está bien cubierto en una serie de tutoriales de Google en inglés, con este punto de partida [https://developers.google.com/appsscript/quickstart/macros?hl=es-ES] .

Más so bre macros Macros

en

Hojas

de

Cálculo

Google:

rangos,

hojas,

planillas

de

cálculo

[http://hojasdecalculogoogle.blogspot.com.ar/2013/06/macros-en-hojas-de-calculo-google_26.html ]

Macros en las Hojas de Cálculo Google: la interfaz de usuario  [http://hojasdecalculogoogle.blogspot.com.ar/2013/06/macrosen-las-hojas-de-calculo-google.html]

Seguinos en la comunidad en castellano de Hojas de Cálculo Google  [https://plus.google.com/u/0/communities/117381564739239305447]

Publicado 10th June 2013 por Alejandro Silvestri Ver comentarios

7th June 2013 Liberando la energía del átomo: La función ARRAYFORMULA

buscar  Classic

[http://web1170.p5.spacequadrat.de/wordpress/wp-content/uploads/2011/11/banner_edvausschuss.jpg

]

Se trata de una función muy poderosa, pero muy poco intuitiva, que les resultará muy útil solamente en hojas de cálculo realmente avanzadas (o complicadas). Este artículo asume que el lector está familiarizado con los términos "función", "rango", "argumento", "criterio" y "matriz". Advertidos, ¡este artículo no es para cualquiera! :))

La " metafunción " A RRAYFORMULA  ARRAYFORMULA no es una función en el sentido clásico, porque no evalúa sus argumentos para producir el resultado. Para empezar, el argumento de ARRAYFORMULA no es un valor, sino una expresión, simple o compleja.  ARRAYFORMULA le dice a la hoja de cálculo que debe evaluar esa expresión de otra manera, diferente a la tradicional.  Array significa matriz, algo muy similar a un rango (un rectángulo de filas y columnas, con valores en cada casillero). Se espera que las expresiones devuelvan algún resultado de uno de estos tipos: • • • •

numérico tex to fecha o duración er ror  

Todos estos resultados se pueden mostrar en una celda. Pero a veces resulta útil que una expresión devuelva como resultado una matriz, un rango entero de valores. Nada mejor que un ejemplo para aclarar, y nada mejor que el ejemplo que se describe en otra entrada de este blog: SUMAR.SI.CONJUNTO [http://hojasdecalculogoogle.blogspot.com.ar/2013/06/sumarsiconjunto.html ] .

Un ejemplo explicado Queremos contar la cantidad de valores menores a 10 en el rango C1:C20. Esto se logra con la siguiente fórmula:

=COUNTIF(C1:C20;">=10")

Sin embargo, sabemos que la función countif no admite criterios complejos. Expandiendo el ejemplo anterior, queremos contar la cantidad de filas de la 1 a la 20, con un valor menor a 10 en la columna C, y un valor mayor a 0 en la columna B. No es posible hacerlo con countif. La expresión buscada tendría esta forma: =sum( if( C1:C20 <10; if( B1:B20>0; 1; 0);0))

Se espera que los dos IF anidados devuelvan 1 por cada fila que cumpla las dos condiciones. La función agregadora SUM sumaría esos 1, y daría como resultado la cantidad que se busca. Se espera también que el primer argumento del primer IF devuelva VERDADERO o FALSO. ¿Pero qué resultado se puede esperar de la comparación de un rango con un valor numérico? C1:C20<10

La hoja de cálculo señala un error, no interpreta esta sintaxis. Es aquí donde entra en juego ARRAYFORMULA: le indica a la hoja de cálculo que la expresión debe evaluarse como una matriz, un array. La fórmula reescrita como sigue, funciona tal como se esperaba: =ArrayFormula(sum( if( C1:C20 <10; if( B1:B20>0; 1; 0);0)))

 ARRAYFORMULA le da un nuevo significado a C1:C20 <10 (y por supuesto también a B1:B20>0 ). Ahora, la expresión C1:C20<10  devuelve como resultado una matriz de 20 filas y 1 columna, cuyos valores son VERDADERO o FALSO, según resulta de evaluar cada celda del rango con <10 .

sum if( C1:C20 <10;

Classic 1;   0);0)

se evalúa 20 veces y devuelve una matriz columna con 20 valores 1 o 0: 1 si se cumplen ambos criterios, 0 si no. Se trata de un "rango virtual", un rango que no existe en la hoja de cálculo, no ocupa ninguna celda. Por eso no se lo llama rango, sino matriz. Luego sum recibe estos 20 valores como si fuera un rango, y l os suma.

Claves para us ar ARRAYFORMULA La expresión argumento de ARRAYFORMULA se evalúa como expresión matricial. En el ejemplo anterior, if( C1:C20 <10; if( B1:B20>0; 1;   0);0)

es una expresión matricial. Toda expresión matricial acepta un rango o una matriz donde normalmente se esperaba un valor, y repite la evaluación recorriendo cada uno de los valores de la matriz. Si hay varias matrices, la expresión entera se evalúa recorriendo los valores correspondientes. Por esto, todos los rangos y matrices de una expresión matricial deben tener el mismo tamaño. El resultado será una matriz del mismo tamaño.

Funciones de agregación  Algunas funciones toman un rango como argumento, y devuelven un valor. Es el caso de las funciones su m, y count, por ejemplo. Se denominan funciones de agregación, pueden recibir una expresión matricial como argumento, pero su comportamiento no cambia con ARRAYFORMULA. Es decir, =ArrayFormula(sum(C1:C20))

no evaluará 20 veces sum de una única celda, si no que evalúa una vez sum para la matriz de 20 celdas.

Fórmulas condicionales  ARRAYFORMULA es la manera de implementar cuentas y sumas condicionales para más de un criterio. Por eso es usual encontrarla con funciones IF anidadas, una por cada criterio. Al implementar fórmulas matriciales con varios criterios, uno podría considerar usar las funciones OR y AND, pero notará que no devuelven los resultados esperados. Esto es porque OR y AND son funciones de agregación, como SUM. No repiten la operación OR (o AND) por cada elemento de la matriz, sino que la evalúan una única vez para el conjunto de elementos, realizando una única operación OR (o AND, se entiende) entre todos. Estas técnicas logran el resultado esperado: Se puede simular una función AND anidando IF. Por ejemplo, if( AND(C1:C20<10; B1:B20>0); VERDADERO; FALSO)

no funciona, pero se implementa con la expresión matricial if (C1:C20<10; if(B1:B20>0; VERDADERO; FALSO))

Se pueden usar operadores matemáticos para i mplementar AND y OR: if( AND(C1:C20<10; B1:B20>0); VERDADERO; FALSO)

se implementa con la expresión matricial if( (C1:C20 <10) * (B1:B20>0); VERDADERO; FALSO)

y if( OR(C1:C20 <10; B1:B20>0); VERDADERO; FALSO)

se implementa con if( (C1:C20 <10) + (B1:B20>0); VERDADERO; FALSO)

 ARRA YFORMUL A y CONTINUE Usualmente ARRAYFORMULA se utiliza con una función de agregción, que evalúa la matriz final y devuelve un resultado en una sola celda. ¿Pero qué pasa si no hay función de agregación? ¿Qué pasa si el resultado final es una matriz? ¿Qué pasa si a la fórmula anterior le quitamos el SUM?

buscar 

buscar  if( B1:B20>0;

Classic 0);0))

Esta fórmula, sin SUM devuelve una matriz de 20 filas y 1 columna, con valores 1 y 0 dependiendo de la evaluación de cada fila. Como una matriz no entra en una celda, las hojas de cálculo Google agregarán automáticamente otras 19 fórmulas en las 19 celdas de abajo, formando un rango de 20 celdas donde se muestra el resultado de la matriz. El resultado equivale exactamente a escribir la siguiente fórmula: =if(C1<10; if( B1>0; 1; 0);0)

y copiarla para abajo. En cada celda los argumentos serán C2 y B2, C3 y B3, etcétera; es decir, lo usual. La primer diferencia reside en que la única fórmula matricial es la de la primera celda del rango. Supongamos que es G30. El resto contendrá la función CONTINUE: =CONTINUE(G30; 2; 1)

que significa: "continuar mostrando los valores de la fórmula matricial de G30, correspondiente a la coordenadas 2;1 de la matriz". Una ventaja de esta forma matricial, es que se puede modificar la fórmula sin necesidad de copiarla hacia abajo. A la segunda diferencia sí se le puede encontrar alguna utilidad: si el tamaño de la matriz cambia, las celdas con CONTINUE se agregan o se eliminan automáticamente. Ésta es la única manera que tienen las Hojas de cálculo Google para extender fórmulas automáticamente, cuando se agregan datos. La cuestión no es trivial, y amerita un artículo aparte.

Publicado 7th June 2013 por Alejandro Silvestri Ver comentarios

6th June 2013

Búsqueda avanzada de textos dentro de celda: RegEx Expresiones Regulares

Publiqué este artículo originalmente en el foro de Google Drive [http://productforums.google.com/forum/#!topic/docses/jyBSTCS7uXc/discussion] .

Las fun ciones REGEX Hojas de Cálculo de Google incorporan algunas funciones que Excel no tiene. Es el caso de tres funciones que usan

expresiones regulares, que permiten hacer búsquedas avanzadas dentro de un texto. Por ejemplo, en el siguiente texto: Celda A1: Est a pel í cul a f ue est r enada en 1992, pero si gue teni endo l a vi genci a de aquél momento Se puede buscar "un número de cuatro c ifras" para obtener el año (1992) con la siguiente función: =RegExExt r act ( A1; "\ d{4}")

Esta función utiliza Expresiones regulares  para realizar búsquedas inteligentes. Las expresiones regulares son patrones de búsqueda  muy flexibles. No son fáciles de utilizar, pero sí son muy potentes. No son un invento de Google, y se usan desde hace años en muchos lenguajes de programación; por eso hay mucha d ocumentación en Internet. En el ejemplo, el segundo argumento \d{4}  es la expresión regular, que dice que busque 4 números seguidos. Las expresiones regulares utilizan una serie de códigos para indicar qué se debe buscar. Ésa es la parte potente pero difícil de las expresiones regulares. Este enlace lleva a una página en inglés, que no es de Google, con una lista de los códigos que se utilizan en expresiones regulares [https://developer.mozilla.org/enUS/docs/JavaScript/Reference/Global_Objects/RegExp#Special_characters_in_regular_expressions] . Las tres funciones de las Hojas de Cálculo Google que utilizan expresiones regulares son: • REGEXEXTRACT: devuelve el texto que responde al patrón • REGEXMATCH: devuelve TRUE si se encuentra algún texto que responda al patrón • REGEXREPLACE: reemplaza el texto que responde al patrón, por otro dado

Más información sobre estas funciones en la lista de funciones de Google .

[https://support.google.com/drive/bin/static.py?

Publicado 6th June 2013 por Alejandro Silvestri Classic

Ver comentarios

6th June 2013

SUMAR.SI.CONJUNTO

Este artículo es una transcripción revisada del artículo original del foro de Google Drive [http://productforums.google.com/forum/#!category-topic/docs-es/foro/jaH5cJ55oBU] . Seguinos en la comunidad en castellano de Hojas de Cálculo Google  [https://plus.google.com/u/0/communities/117381564739239305447]

SUMAR.SI.CONJUNTO Este nombre horripilante es la traducción que eligió Microsoft para la función SUMIFS, una función no estándar, que si bien es muy utilizada, no suele hallarse en otras aplicaciones de hojas de cálculo que no sea Excel. SUMAR.SI.CONJUNTO es una función que se agregó a Microsoft Excel hace unos años, y como no está en versiones anteriores de Excel todavía se la considera una "función nueva". Esto es así porque Excel 97 marcó un hito que estableció cuál es el "estándar" (adoptado por los desarrolladores) para las planillas de cálculo, y todo lo que no estaba en esa versión se considera "no estándar". La función SUMAR.SI.CONJUNTO no brinda una capacidad nueva, sino una manera más simple de hacer algo que ya se podía hacer en versiones anteriores, y por eso esta función no suele ser incorporada fuera de Excel. Su sintáxis es la siguiente: =SUMAR.SI.CONJUNTO(A1:A20; B1:B20; " >0"; C1:C20; "<10") (fuente Microsoft)  [http://office.microsoft.com/es-es/excel-help/funcion-sumar-si-conjunto-HA010047504.aspx ]

[http://office.microsoft.com/es-es/excel-help/funcion-sumar-si-conjunto-HA010047504.aspx ] Esta función suma del rango A1:A20 solamente aquéllas filas que cumplen con el criterio B1:B20>0 y C1:C20<10. Nótese que los tres rangos tienen la misma forma (1 columna por 20 filas). Podrían tener cualquier otra forma, pero los tres tienen que tener la misma.  Además, la función no está lim itada a dos criterios, puede tener muchos m ás.  A continuación voy a transcri bir este mismo ejem plo pero en varios renglones, para mayor cl aridad, y luego poner un equivalente para hojas de cálculo Google: =SUMAR.SI.CONJUNTO(   A1:A20; B1:B20; ">0"; C1:C20; "<10" )

En el primer renglón está el argumento del rango con los datos a sumar, y en los renglones siguientes pares de argumentos con un rango a evaluar y la evaluación propiamente dicha. =ArrayFormula(sum( if( C1:C20 <10; if( B1:B20>0;   A1:A20; 0);0))) Se trata de una fórmula matricial con varios IF anidados dentro del SUM. Como se ve, primero van los criterios y al final el rango suma. Conviene analizarla bien para entenderla, y luego modificarla como uno quiera: es muy potente. Pero para el que no puede dedicarle tiempo al tema, dejo tres ejemplos para un criterio, dos criterios y tres criterios, para copiar, pegar y editar los rangos: • =ArrayFormula(sum(if( B1:B20>0; A1:A20;0))) • =ArrayFormula(sum(if( C1:C20<10;if(B1:B20>0; A1:A20;0);0))) • =ArrayFormula(sum(if( D1:D20=5;if(C1:C20<10;if(B1:B20>0; A1:A20;0);0);0)))

Otra manera un poco más breve y simple de escribir (pero quizás más lenta de ejecutar) es ésta, siguiendo con los ejemplos anteriores: • =ArrayFormula(sum( (B1:B20>0) * A1:A20 )) • =ArrayFormula(sum( (C1:C20<10) * (B1:B20>0) * A1:A20 )) • =ArrayFormula(sum( (D1:D20=5) * (C1:C20<10) * (B1:B20>0) * A1:A20)) Los espacios no son necesarios, están solamente para facilitar la lectura.

CONTAR.SI.CONJUNTO Creo que es tan obvio, que evitaré explicar para qué sirve esta función. Para CONTAR.SI.CONJUNTO se usa la misma fórmula indicada para SUMAR.SI.CONJUNTO, pero con un mínimo cambio: se reemplaza el rango de suma  A1: A20 del ejemplo, por el valor 1.

buscar 

×

Report "Curso Gas"

Your name

Email

Reason -Select Reason- Pornographic Defamatory Illegal/Unlawful Spam Other Terms Of Service Violation File a copyright complaint

Description

Close Send

×

Sign In

Email

Password

Remember me Forgot password?

Sign In

Our partners will collect data and use cookies for ad personalization and measurement. Learn how we and our ad partner Google, collect and use data. Agree & close