Crear un índice o tabla de contenido de Hojas en Google Sheets

Tabla de Contenido en Google Sheets
— by

En este artículo vemos como crear un índice o tabla de contenidos de las pestañas de una hoja de cálculo en Google Sheets

Videos

Aquí les dejo los dos tutoriales en formato de video

Método 1: Indice de Hojas con Enlaces

Esta es la manera manual de hacerlo, y lo hacemos con los enlaces a las distintas pestañas

Es el método más fácil, pero al ser manual, cada vez que haya nuevas pestañas, o cuando eliminemos una pestaña tenemos que manualmente editar, insertar o eliminar un elemento del índice

Para esto simplemente:

Paso 1: Escribimos en la pestaña donde va a ser la tabla de contenido, los nombres de todas nuestras pestañas

Paso 2: Con el botón de insertar enlace o con Ctrl + K insertamos un enlace en el primer ítem. En la sección «Hojas de esta hoja de cálculo» escogemos la correspondiente

Paso 3: Repetimos con todas las secciones

Quedaría así:

Y si dan clic sobre alguna celda con un enlace, les da la opción de ir a ese enlace, modificarlo o eliminarlo

Facil ¿Verdad?

Pero aburrido.

Veamos que más podríamos hacer

Método 2: Indice de Hojas con Fórmulas

Una segunda opción es usar la función HIPERVINCULO

La verdad es que es muy parecida a la opción anterior pero con algunas posibilidades adicionales

Para construir un índice básico con HIPERVINCULO, seguimos los siguientes pasos:

Paso 1: Buscar las identificaciones de las pestañas

Para poder vincular desde la función hipervínculo necesitamos la identificación de cada pestaña.

Esta la encontramos, seleccionando la pestaña y buscando en la dirección URL después de «edit» el código que empieza por «#gid»

En este ejemplo el código es #gid=517650350

Paso 2: Crear nuestra función HIPERVINCULO

La función HIPERVINCULO necesita dos argumentos: la URL y el texto

Si queremos hacer por ejemplo el vínculo a la pestaña «Colombia» entonces construimos la función así:

=HIPERVINCULO(«#gid=517650350″;»Colombia»)

Paso 3: Repetir para todas las pestañas

Y listo!

Método 2.1: Indice de Hojas a la ultima fila

Hasta ahora no hay ninguna diferencia con el método anterior, pero podemos ir un paso más allá y por ejemplo no solo vincular a la pestaña Colombia, sino que nos lleve a la última fila llena, para que podamos rápidamente agregar nuevos datos desde la última fila.

Esto es muy util cuando nuestras pestañas ya tengan muchas filas.

Nos ahorramos un paso

Para esto partimos de la función HIPERVINCULO que ya construimos pero le vamos a agregar algo más

Nosotros podemos vincular a una celda específica.

Hagan la prueba. Váyanse a cualquier celda, den clic derecho y busquen la opción «Obtener enlace de esta celda»

Les va a copiar el enlace en el portapapeles

Si lo pegan en cualquier otro lado, les va a salir algo como esto:

«https://docs.google.com/spreadsheets/d/1d7O7dz0FoX8WsiyItDOcd46x7YnSJ2ms2gPk8rDPo3M/edit#gid=0&range=A9»

Lo único que nos interesa es la última parte:

«#gid=0&range=A9»

Fíjense que nosotros ya habíamos puesto la parte de #gid=0.

Ese es el enlace a la pestaña

Lo que ahora nos interesa agregarle es la segunda parte

«&range=A9»

Y adicionalmente queremos reemplazar ese «9» por la última fila de esa pestaña

Esa última fila la podemos obtener con la siguiente fórmula:

=CONTARA(Colombia!A:A)

Entonces mi HIPERVINCULO ahora quedaría así:

=HIPERVINCULO(«#gid=517650350&range=A»&CONTARA(Colombia!A:A)+1;»Colombia»)

Método 3: Indice con Google Apps Script (Macro)

Los métodos anteriores son muy útiles, pero si estamos agregando o eliminando hojas todo el tiempo, tendremos que estar cambiando nuestros enlaces o fórmulas todo el tiempo

Para hacer algo más automático tenemos que acudir a Google Apps Script

Si nunca han trabajado con Macros aquí les dejo un tutorial

Por ahora nos vamos a «Herramientas»>»Editor de Secuencia de Comandos»

Lógica del algoritmo

Este es el paso a paso de lo que va a hacer el código

  1. Nos conectamos con Sheets y con la hoja donde va a ir mi Indice (En mi caso se llama «Inicio»)
  2. Traemos todas las pestañas a una variable (En mi caso «misHojas»)
  3. Borramos el índice actual (Puede que hayamos eliminado pestañas, entonces es mejor volverlo a hacer de 0)
  4. Vamos a recorrer todas las hojas actuales, y para cada una vamos a:
    1. Revisar que no sea la hoja de Inicio (Si es la hoja de inicio no hace nada)
    2. Creamos la fórmula HIPERVINCULO (No hay otra forma en Google Apps Script para vincular que yo conozca) con el id y el nombre de cada pestaña
    3. Insertamos esta formulas en la columna A y en la fila 2 (después en la 3, después en la 4, y así sucesivamente
    4. (Opcional) Creamos un vínculo en cada pestaña para volver al inicio

Código Completo

//Funcion para crear un indice con vinculo a todas mis pestañas
function indiceDinamico() {
  //Configuracion
  const nombreInicio = "Inicio";
  var misHojas = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var hojaInicio = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(nombreInicio);
  var i=2;
  
  //Borrar todo el contenido antes de actualizar el indice
  hojaInicio.getRange(2,1,hojaInicio.getLastRow()).clearContent();
  
  //Ciclo que recorre todas las pestañas actuales
  misHojas.forEach(function(hoja){
    
    //Condicional para que no tome la pestaña "Inicio"
    if(hoja.getName()!=nombreInicio){
      
      //Agregue la formula HIPERVINCULO con el nombre y la Id de cada pestaña
      var formula = '=HYPERLINK("#gid='+hoja.getSheetId()+'";"'+hoja.getName()+'")'
      hojaInicio.getRange(i,1).setFormula(formula)
      
      //Agregue la formula HIPERVINCULO con la pestaña Inicio a cada pestaña, para poder devolverse
      hoja.getRange(1,1).setFormula('=HYPERLINK("#gid=0";"Ir a Inicio")')
      i++;
    } // Cierre If Inicio
  }) // Cierre Ciclo
}

Conclusión

Vimos entonces cuatro maneras distintas para insertar una hoja de contenido de nuestro Sheets

Como siguiente paso, podríamos hacer un índice no solo de las hojas de nuestra hoja actual sino de todos los archivos de una carpeta en Drive o algo así. Solo una idea.

Como siempre, espero sus preguntas y sugerencias en los comentarios.

Suscríbete para acceder al curso «Descubriendo el poder de Google Sheets» y empezar tu camino a la maestría de Sheets, Forms y Apps Script

Boletín

Nuestras últimas noticias en tu correo electrónico

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Pin It on Pinterest

Share This