INSERTAR TABLA DINÁMICA Y CONTROLAR ERRORES CON TYPESCRIPT

Hola a todos!.

Estos días me voy a centrar de nuevo en publicaciones sobre typescript. En mi opinión personal creo que en los próximos meses vamos a ver grandes avances en Office Scripts y no debemos dejar de aprender nuevas cosas de este lenguaje.

En el post de hoy quiero centrarme fundamentalmente en dos acciones: Insertar una tabla dinámica y añadir elementos y por otra parte el control de errores en el código.

Para trabajar esto utilizaré los datos poblacionales por provincias españolas en 2021:

Y el ejercicio que voy a realizar es crear en la columna C una nueva clasificación en función de la población y, a partir de esos datos, crear una tabla dinámica. Voy a realizar el ejercicio en la misma hoja para que lo veáis perfectamente, pero se podría referir a cualquier hoja del libro.

Para poder avanzar os voy a dejar el código completo y lo iremos comentando:

function main(workbook: ExcelScript.Workbook) {
	const mihoja = workbook.getWorksheet("Hoja1");
	const valores = mihoja.getUsedRange().getValues();
	// Nombre de nueva columna
	const nColumn: (string)[][] = [
		["RANGO POBLACIONAL"]
	];
	//obtenemos valores de todas las columnas
	valores.splice(1).forEach((row) => {
		//formateamos valores de la columna B. Rango de población
		let pRango = "";
		const Poblacion = row[1];
		if (Poblacion <= 100000) {
			pRango = "<= 100.000";
		} else if (Poblacion <= 200000) {
			pRango = "de 100.001 a 200.000";
		} else if (Poblacion <= 300000) {
			pRango = "de 200.001 a 300.000";
		} else if (Poblacion <= 400000) {
			pRango = "de 300.001 a 400.000";
		} else if (Poblacion <= 500000) {
			pRango = "de 400.001 a 500.000";
		} else if (Poblacion <= 600000) {
			pRango = "de 500.001 a 600.000";
		} else if (Poblacion <= 700000) {
			pRango = "de 600.001 a 700.000";
		} else if (Poblacion <= 800000) {
			pRango = "de 700.001 a 800.000";
		} else if (Poblacion <= 900000) {
			pRango = "de 800.001 a 900.000";
		} else if (Poblacion >= 900001) {
			pRango = "mayor de 900.001";
		}
		//añadimos el array al título
		nColumn.push([pRango])
	});
	//limpiamos columna C
	mihoja.getRangeByIndexes(1, 2, nColumn.length, 1).clear(ExcelScript.ClearApplyTo.all);
	//Exportamos datos a nueva columna
	const nuevaColumna = mihoja.getRangeByIndexes(0, 2, nColumn.length, 1);
	nuevaColumna.setValues(nColumn);
	//Para añadir la tabla dinámica debemos comprobar si existe otra y borrarla
	// capturamos el error si no hay tabla y no hacemos nada
	try {
		var tablas = mihoja.getPivotTable("Mitabla").getName()
		mihoja.getPivotTable("MiTabla").delete();
	} catch (error) { }
	//creamos tabla dinámica
	const tabla = mihoja.getRangeByIndexes(0, 0, nColumn.length, 3);
	const pivotTable = mihoja.addPivotTable("MiTabla", tabla, "F1");
	pivotTable.addRowHierarchy(pivotTable.getHierarchy("RANGO POBLACIONAL"));
	pivotTable.addDataHierarchy(pivotTable.getHierarchy("POBLACIÓN"));
}

Comenzamos creando y declarando las variables que harán referencia a la hoja y a sus valores:

	const mihoja = workbook.getWorksheet("Hoja1");
	const valores = mihoja.getUsedRange().getValues();

Luego añadimos encabezado a la columna C y formateamos los datos de la columna B con un for, indicando los nuevos rangos de población:

valores.splice(1).forEach((row) => {
		//formateamos valores de la columna B. Rango de población
		let pRango = "";
		const Poblacion = row[1];
		if (Poblacion <= 100000) {
			pRango = "<= 100.000";
		} else if (Poblacion <= 200000) {
			pRango = "de 100.001 a 200.000";
		} else if (Poblacion <= 300000) {
			pRango = "de 200.001 a 300.000";
		} else if (Poblacion <= 400000) {
			pRango = "de 300.001 a 400.000";
		} else if (Poblacion <= 500000) {
			pRango = "de 400.001 a 500.000";
		} else if (Poblacion <= 600000) {
			pRango = "de 500.001 a 600.000";
		} else if (Poblacion <= 700000) {
			pRango = "de 600.001 a 700.000";
		} else if (Poblacion <= 800000) {
			pRango = "de 700.001 a 800.000";
		} else if (Poblacion <= 900000) {
			pRango = "de 800.001 a 900.000";
		} else if (Poblacion >= 900001) {
			pRango = "mayor de 900.001";
		}
		//añadimos el array al título
		nColumn.push([pRango])
	});

Antes de grabar los datos que hemos formateado en la columna C, vamos a eliminar cualquier información previa:

	//limpiamos columna C
	mihoja.getRangeByIndexes(1, 2, nColumn.length, 1).clear(ExcelScript.ClearApplyTo.all);
	//Exportamos datos a nueva columna
	const nuevaColumna = mihoja.getRangeByIndexes(0, 2, nColumn.length, 1);
	nuevaColumna.setValues(nColumn);

El siguiente paso es crear una tabla dinámica, pero antes de insertarla debemos verificar si existe otra tabla anterior o previa y eliminarla. Por ejemplo si decidimos obtener el nombre de esa tabla previa, lo podríamos hacer con esto:

var tablas = mihoja.getPivotTable("Mitabla").getName()

Lo que nos devolvería el nombre de Mitabla, (que es el nombre de nuestra tabla dinámica), pero si resulta que no hay tabla previa mostrará un error. Para controlar este error se utilizar la declaración try...catch (podéis obtener más información sobre esto en la red, con concreto aquí ).

En nuestro caso, si resulta que no hay tabla dinámica no hacemos nada, y si hay tabla dinámica borramos la anterior y creamos la nueva tabla:

// capturamos el error si no hay tabla y no hacemos nada
	try {
		var tablas = mihoja.getPivotTable("Mitabla").getName(
		mihoja.getPivotTable("MiTabla").delete();
	} catch (error) { }
	//creamos tabla dinámica
	const tabla = mihoja.getRangeByIndexes(0, 0, nColumn.length, 3);
	const pivotTable = mihoja.addPivotTable("MiTabla", tabla, "F1");
	pivotTable.addRowHierarchy(pivotTable.getHierarchy("RANGO POBLACIONAL"));
	pivotTable.addDataHierarchy(pivotTable.getHierarchy("POBLACIÓN"));
}

Para crear una nueva tabla, debemos especificar dónde la queremos y a continuación crearla:

const tabla = mihoja.getRangeByIndexes(0, 0, nColumn.length, 3);

const pivotTable = mihoja.addPivotTable("MiTabla", tabla, "F1");

finalmente añadimos las filas (que serán el Rango Poblacional) y el sumatorio de la población.

El resultado del ejercicio es el siguiente:

Espero que sea de interés y en breve también de utilidad, cuando tengáis que programar vuestras hojas Excel Online.

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.



¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies