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
- Nos conectamos con Sheets y con la hoja donde va a ir mi Indice (En mi caso se llama “Inicio”)
- Traemos todas las pestañas a una variable (En mi caso “misHojas”)
- Borramos el índice actual (Puede que hayamos eliminado pestañas, entonces es mejor volverlo a hacer de 0)
- Vamos a recorrer todas las hojas actuales, y para cada una vamos a:
- Revisar que no sea la hoja de Inicio (Si es la hoja de inicio no hace nada)
- 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
- 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
- (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.
