Office Scripts: mi primer script

Hace un tiempo publiqué el anuncio de la nueva aventura en Excel: Office Scripts (ver aquí).

Ya tenía pendiente escribir mi primer Script.. y aquí lo tengo.

La verdad es que he leído largo y tendido sobre este 'nuevo juguete' en la poca documentación que existe al respecto, y completado mi lectura con lenguaje JavaScript genérico. Supongo que para los que no somos informáticos, aprender nuevos lenguajes se nos hace un poco cuesta arriba.

Me he dado cuenta que, aunque a primera vista, el aspecto se asemeja a nuestro conocido VBA para Excel (las macros de toda la vida), y tiene puntos en común: Ambos son lenguajes basados en objetos, son solo semejanzas y no coincidencias...
Sinceramente no sabría decir si esto nos beneficia o perjudica, por la mezcla posible entre lenguajes a la que nos puede llevar.

En todo caso, el Editor de código de Office Scripts disponible, de momento, solo para la versión de Excel Online (en la Web), tiene habilitada la funcionalidad de 'intellisense', lo que ayuda bastante a la hora de escribir nuestro scripts.

Un aspecto importante, antes de comenzar, es que todos nuestros scripts están disponibles en una carpeta de OneDrive en la subcarpeta de Documentos > Script de Office

Dentro encontraremos distintos archivos con extensión .osts que contienen el código generado y que tendremos disponible desde el Editor de Scripts desde cualquier libro que abramos!.

Ten presente, y no olvides, que los scripts de Office se escriben en TypeScript o JavaScript , y usan las API de JavaScript de scripts de Office para interactuar con los libros de Excel.

La grabadora de acciones genera código en TypeScript (superconjunto de JavaScript).
La documentación de scripts de Office usa TypeScript, pero si estás más familiarizado con JavaScript, desde luego puedes usarlo en su lugar.

Más que tenemos que saber antes de empezar... nuestros scripts son 'Case Sensitive' (sensible a las mayúsculas y minúsculas) !!!. OJO con este punto !!.

Y un punto importante más... Atención por que los índices son en base 0, i.e., el primer elemento de cualquier colección será el 0 siempre....

Veamos algunos de los operadores necesarios más habituales... (listado no completo!!).
Listemos operadores aritméticos permitidos:
+ Suma
- Sustracción
* Multiplicación
** Potencia (elevado a)
/ División
% División entera
++ Incremento
-- Decremento

Operadores de comparación:
== igual
=== igual valor y tipo
!= no igual / distinto
!== distinto valor o distinto tipo
> mayor que
< menor que
>= mayor o igual que
<= menor o igual que

Operadores lógicos:
&& lógico Y (AND)
|| lógico O (OR)
! lógico NO (NOT)

Operador de texto:
+ Añade o concatena cadenas de texto

Añadir comentarios en el script:
// para líneas sencillas
/* ... */ para multilíneas.

Algunas de las sentencias de control básicas como condicionales y bucles.

Sentencias condicionales:
equivale al clásico IF ... THEN ... ELSE ... en VBA.

if (condición) {
  //  código a ejecutar si se cumple la condición
}

if (condición) {
  //  código a ejecutar si se cumple la condición
} else {
  //  código a ejecutar si NO se cumple la condición
}
}

if (condición_1) {
  //  código a ejecutar si se cumple la condición_1
} else if (condición_2) {
  //  código a ejecutar si NO se cumple la condición_1 y la condición_2 es Verdadera
} else {
  //  código a ejecutar si las condiciones previas son todas Falsa
}

Otra sentencia condicional es SWITCH, que equivaldría a SELECT CASE ... END SELECT en VBA.

switch(expresión) {
  case x:
    // código
    break;
  case y:
    // código
    break;
  default:
    // código
}

Precaución de no olvidar incluir la instrucción 'break', si la omites se ejecutará el siguiente CASE aunque no se cumpla!.

Otras sentencias importantes son los BUCLES.
Por ejemplo el FOR simple:

for (statement_1; statement_2; statement_3) {
  // código a ejecutar
}

siendo:
Statement_1: se lee y ejecuta la primera vez (solo una) antes de nuestro bloque de código. El punto de inicio del bucle.
Statement_2: define la condición para ejecutar nuestro bloque de código. Condición final.
Statement_3: se ejecuta cada vez después del bloque de código. Tipo de incremento/decremento... el step.

Ejemplo:

for (i = 0; i < 13; i++) {
  text += "Nuestro número es " + i ;
}

Otro bucle clásico es WHILE:

while (condition) {
  // code block to be executed
}

Ejemplo: El siguiente código ejecuta, una y otra vez, mientras la variable 'i' sea menor a 13.

let i=0;
while (i < 13) {
  console.log( "El valor del numero es " + i);
  i++;
}

Con este breve adelanto, estamos listos para ver mi primer ejemplo de Script en Office. Accedemos a Excel Online desde nuestra cuenta de Office, y vamos a la ficha de Automatizar > grupo Herramientas de scripting > botón Editar de código. Se abrirá a la derecha el panel donde o bien buscaremos alguno de nuestros SCripts o Crearemos un nuevo. En este caso presionaremos Crear Script y copiaremos el siguiente código en la ventana:

async function main(context: Excel.RequestContext) {
  let hoja = context.workbook.worksheets.getActiveWorksheet();
  let rango = hoja.getUsedRange().load("Values, rowCount, columnCount");

  await context.sync();
  let UF=rango.rowCount ;
  let UC=rango.columnCount;

  //rango de recorrido
  let rngPais = hoja.getRangeByIndexes(2, 2, UF, 1).load("values")
  let rngUds = hoja.getRangeByIndexes(2, 3, UF , 1).load("values")

  await context.sync();

  // bucle para completar el Precio con un condicional 
  for (let i = 0; i < UF-1; i++) {
    let pais = rngPais.values[i];
    let uds = rngUds.values[i];
    let pz : number;

    // miramos país y unidades.
    if (pais[0] == 'ES') {
      pz = 100;
    } else if (pais[0] == 'FR') {
      pz = 20;
    } else if (pais[0] == 'PT') {
      pz = 3;
    } else { 
      pz = 0;
    }

    //además añadimos fórmula para Total
    let multiplica: any;
    let f=i+3;
    switch (true) {
      case (uds < 30):
        multiplica = "=1.05*D" + f + "*E" + f;
        break;
      case (uds< 60):
        multiplica = "=1.15*D" + f + "*E" + f;
        break;
    }

    await context.sync();
    rango.getCell(i + 1, 3).values = [[pz]];
    rango.getCell(i + 1, 4).formulas = [[multiplica]];
  }
}

En entradas siguientes detallaré y explicaré algo más el script de arriba. Básicamente identifica en la hoja de cálculo donde hay datos, e inserta de forma condicionada en la columna E los precios según el país... y además, según el número de unidades, añadimos un total u otro.