Saltar al contenido principal

APIs con Google Sheets - Integración con SMARTFENSE

  • Actualización

En esta guía te mostraremos cómo utilizar las APIs de SMARTFENSE desde Google Apps Script para crear una planilla de cálculo dinámica con la información proveniente de los distintos endpoints de campañas de tu instancia.

Este enfoque no solo te permitirá centralizar y actualizar los datos de forma automática, sino también preparar la información para integrarla fácilmente con herramientas de Business Intelligence, como Looker Studio, y así construir reportes y dashboards más completos.

Como primer paso, deberás registrar una nueva aplicación dentro de tu instancia para obtener el Client ID y el Client Secret. Para hacerlo, ingresá en Configuración > Integraciones > API.

Presiona el botón Registrar nueva aplicación y completa los siguientes campos:

  • Nombre: asígnale un nombre descriptivo a la aplicación.

  • Client ID: este valor será generado automáticamente por SMARTFENSE.

  • Client Secret: este valor también será generado automáticamente por SMARTFENSE.

  • Tipo de cliente: Público.

  • Tipo de concesión de autorización: Client credentials.

  • Alcances: para este caso utilizaremos permisos de solo lectura, que nos permitirán consultar la API de forma segura.

Antes de presionar el botón Guardar, asegúrate de realizar un respaldo del Client Secret, ya que lo vas a necesitar más adelante para configurarlo en el proyecto de Google Spreadsheet.

Una vez que hayas registrado la aplicación en tu instancia, ya podes crear un archivo en Google Spreadsheet. Dentro del archivo, ingresá al menú Extensiones > Apps Script y asígnale un nombre al proyecto.

A continuación, vamos a configurar dos propiedades del script que serán utilizadas para validar y autorizar las conexiones con la API. Para hacerlo, ingresá en Configuración del proyecto.

Crea las propiedades SMARTFENSE_CLIENT_ID y SMARTFENSE_CLIENT_SECRET. En cada una de ellas deberás ingresar el valor obtenido al registrar la aplicación desde la plataforma.

Realizado este paso comenzaremos a crear los scripts que negociaran la conexión a las APIs. En el editor crea los siguientes scripts:

Config.gs

const SMARTFENSE_CONFIG = {
  baseUrl: 'https://nombre_instancia.takesecurity.com',
  tokenUrl: 'https://nombre_instancia.takesecurity.com/oauth/token/',
  maxPages: 1000
};

Reemplaza nombre_instancia por el subdominio de tu instancia.

Auth.gs

/**
 * Lee credenciales desde Script Properties.
 */
function smartfenseGetCredentials_() {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('SMARTFENSE_CLIENT_ID');
  const clientSecret = props.getProperty('SMARTFENSE_CLIENT_SECRET');

  if (!clientId || !clientSecret) {
    throw new Error('Faltan SMARTFENSE_CLIENT_ID o SMARTFENSE_CLIENT_SECRET en Script Properties.');
  }

  return { clientId, clientSecret };
}

/**
 * Obtiene un access token OAuth2 con client_credentials.
 */
function smartfenseGetAccessToken() {
  const { clientId, clientSecret } = smartfenseGetCredentials_();
  const basicAuth = Utilities.base64Encode(clientId + ':' + clientSecret);

  const response = UrlFetchApp.fetch(SMARTFENSE_CONFIG.tokenUrl, {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: 'grant_type=client_credentials',
    headers: {
      Authorization: 'Basic ' + basicAuth,
      Accept: 'application/json',
      'Cache-Control': 'no-cache'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  Logger.log('TOKEN STATUS: ' + status);

  if (status < 200 || status >= 300) {
    throw new Error('Error obteniendo token. HTTP ' + status + ' - ' + body);
  }

  let json;
  try {
    json = JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta del token no es JSON válido.');
  }

  if (!json.access_token) {
    throw new Error('La respuesta no incluye access_token.');
  }

  return json.access_token;
}

ApiUtils.gs

/**
 * Valida que una URL pertenezca a la instancia esperada.
 */
function smartfenseValidateUrl_(url) {
  if (!url.startsWith(SMARTFENSE_CONFIG.baseUrl + '/')) {
    throw new Error('La URL no pertenece a la instancia esperada: ' + url);
  }
}

/**
 * Hace un GET autenticado y devuelve JSON parseado.
 */
function smartfenseGetJson_(url, accessToken) {
  smartfenseValidateUrl_(url);

  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + accessToken,
      Accept: 'application/json'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  Logger.log('GET URL: ' + url);
  Logger.log('GET STATUS: ' + status);

  if (status < 200 || status >= 300) {
    throw new Error('Error consultando API. HTTP ' + status + ' - ' + body);
  }

  try {
    return JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta de la API no es JSON válido.');
  }
}

/**
 * Recorre todas las páginas usando el campo next.
 * Devuelve una lista consolidada leyendo la colección indicada.
 *
 * @param {string} endpointPath Ej: '/api/v1/users/campaigns/phishing'
 * @param {string} collectionField Ej: 'results'
 * @returns {Array}
 */
function smartfenseGetAllPages(endpointPath, collectionField) {
  const accessToken = smartfenseGetAccessToken();

  let nextUrl = SMARTFENSE_CONFIG.baseUrl + endpointPath;
  let allItems = [];
  let pageCount = 0;

  while (nextUrl) {
    pageCount++;

    if (pageCount > SMARTFENSE_CONFIG.maxPages) {
      throw new Error('Se alcanzó el máximo de páginas permitidas (' + SMARTFENSE_CONFIG.maxPages + ').');
    }

    const page = smartfenseGetJson_(nextUrl, accessToken);
    const items = Array.isArray(page[collectionField]) ? page[collectionField] : [];

    allItems = allItems.concat(items);
    nextUrl = page && page.next ? page.next : null;
  }

  return allItems;
}

/**
 * Obtiene o crea una hoja.
 */
function getOrCreateSheet_(spreadsheet, sheetName) {
  let sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }
  return sheet;
}

function valueOrEmpty_(value) {
  if (value === null || value === undefined) return '';
  return value;
}

function stringifyIfNeeded_(value) {
  if (value === null || value === undefined) return '';
  if (typeof value === 'object') return JSON.stringify(value);
  return value;
}

Los tres scripts están comentados para interpretar su funcionalidad.

Ahora es momento de crear los scripts de cada endpoint que permitirá obtener los datos de las campañas.

Exportación de campañas de phishing a Google Sheets
Exportación de campañas de ransomware a Google Sheets
Exportación de campañas de smishing a Google Sheets
Exportación de campañas de módulos interactivos a Google Sheets
Exportación de campañas de videos a Google Sheets
Exportación de campañas de videogames a Google Sheets
Exportación de campañas de newsletters a Google Sheets
Exportación de campañas de exámenes a Google Sheets
Exportación de campañas de encuestas a Google Sheets

Exportación de campañas de phishing a Google Sheets

Objetivo

Este script consulta el endpoint de campañas de phishing por usuario en la API de SMARTFENSE y exporta los resultados a una hoja de Google Sheets llamada PHISHING.

¿Qué hace este script?

El script automatiza todo el proceso de extracción y carga de datos:

  • se autentica contra la API de SMARTFENSE,

  • consulta el endpoint correspondiente,

  • recorre todas las páginas de resultados,

  • transforma la respuesta en una estructura tabular,

  • y vuelca la información en una hoja de Google Sheets.

Resultado

Al ejecutar el script, se crea o actualiza una hoja llamada PHISHING dentro del spreadsheet activo.

La información queda organizada en formato tabla, lista para su análisis y reutilización.

¿Para qué sirve esta hoja?

La hoja generada funciona como base de datos operativa para análisis y visualización.

Su propósito es que los datos extraídos desde la API puedan utilizarse después en:

  • dashboards de Looker Studio,

  • análisis en Google Sheets,

  • cruces con otras fuentes,

  • reportes operativos,

  • tableros de seguimiento.

En este esquema, el script cumple la función de transformar la información del endpoint en una fuente tabular lista para ser consumida por herramientas de reporting.

Fuente de datos

Endpoint consultado:

/api/v1/users/campaigns/phishing

Tipo de información exportada

La hoja incluye información relacionada con:

  • datos del usuario,

  • campañas de phishing asociadas,

  • actividad del usuario dentro de la campaña,

  • teachable moments,

  • fechas relevantes del proceso,

  • y datos técnicos de la simulación.

Consideraciones

  • El script utiliza autenticación OAuth2.

  • Reutiliza temporalmente el token mediante caché.

  • Recorre automáticamente la paginación del endpoint.

  • Convierte los campos de fecha para que Google Sheets los reconozca correctamente.

  • Limpia y vuelve a generar la hoja en cada ejecución.

  • Si ocurre un error, intenta dejar el detalle reflejado en la propia hoja.

Código

A continuación se incluye el script comentado para referencia técnica.

Phishing.gs

// Configuración específica para la exportación de campañas de phishing.
// Centraliza:
// - la ruta del endpoint a consultar,
// - el nombre de la hoja de destino,
// - la clave y duración del token en caché,
// - los campos que deben interpretarse como fecha,
// - y el formato visual de fecha en Google Sheets.
const PHISHING_CONFIG = {
  endpointPath: '/api/v1/users/campaigns/phishing',
  sheetName: 'PHISHING',
  tokenCacheKey: 'SMARTFENSE_ACCESS_TOKEN',
  tokenCacheSeconds: 300,
  dateFields: [
    'campaign_date',
    'campaign_expiration_date',
    'activity_sent_date',
    'activity_open_date',
    'activity_click_date',
    'activity_post_date',
    'activity_simulation_reported_date',
    'activity_teachable_moment_sent_date',
    'activity_teachable_moment_open_date',
    'activity_teachable_moment_correct_answer_date',
    'activity_teachable_moment_incorrect_answer_date'
  ],
  dateNumberFormat: 'dd/MM/yyyy HH:mm:ss'
};

/**
 * Lee las credenciales del cliente OAuth2 desde Script Properties.
 *
 * Espera encontrar:
 * - SMARTFENSE_CLIENT_ID
 * - SMARTFENSE_CLIENT_SECRET
 *
 * Si falta alguna, corta la ejecución con error.
 */
function phishingGetCredentials_() {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('SMARTFENSE_CLIENT_ID');
  const clientSecret = props.getProperty('SMARTFENSE_CLIENT_SECRET');

  if (!clientId || !clientSecret) {
    throw new Error('Faltan SMARTFENSE_CLIENT_ID o SMARTFENSE_CLIENT_SECRET en Script Properties.');
  }

  return { clientId, clientSecret };
}

/**
 * Recupera un token OAuth2 previamente guardado en caché.
 *
 * Esto evita pedir un token nuevo en cada ejecución
 * mientras siga vigente dentro del tiempo configurado.
 */
function phishingGetCachedToken_() {
  return CacheService.getScriptCache().get(PHISHING_CONFIG.tokenCacheKey);
}

/**
 * Guarda el token OAuth2 en la caché del script.
 *
 * El tiempo de vida del token cacheado se define en:
 * PHISHING_CONFIG.tokenCacheSeconds
 */
function phishingSetCachedToken_(token) {
  CacheService.getScriptCache().put(
    PHISHING_CONFIG.tokenCacheKey,
    token,
    PHISHING_CONFIG.tokenCacheSeconds
  );
}

/**
 * Obtiene un access token OAuth2 usando el flujo client_credentials.
 *
 * Flujo:
 * 1. Intenta reutilizar un token cacheado.
 * 2. Si no existe, lee credenciales desde Script Properties.
 * 3. Llama al endpoint de token configurado en SMARTFENSE_CONFIG.tokenUrl.
 * 4. Valida la respuesta HTTP.
 * 5. Parsea el JSON y extrae access_token.
 * 6. Guarda el token en caché para próximas ejecuciones.
 */
function phishingGetToken_() {
  const cachedToken = phishingGetCachedToken_();
  if (cachedToken) {
    return cachedToken;
  }

  const { clientId, clientSecret } = phishingGetCredentials_();
  const basicAuth = Utilities.base64Encode(clientId + ':' + clientSecret);

  const response = UrlFetchApp.fetch(SMARTFENSE_CONFIG.tokenUrl, {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: 'grant_type=client_credentials',
    headers: {
      Authorization: 'Basic ' + basicAuth,
      Accept: 'application/json',
      'Cache-Control': 'no-cache'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  // Si la API responde fuera del rango 2xx, se informa el error completo.
  if (status < 200 || status >= 300) {
    throw new Error('Error obteniendo token. HTTP ' + status + ' - ' + body);
  }

  let json;
  try {
    json = JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta del token no es JSON válido.');
  }

  // Se valida que la respuesta incluya access_token.
  if (!json.access_token) {
    throw new Error('La respuesta no incluye access_token.');
  }

  phishingSetCachedToken_(json.access_token);
  return json.access_token;
}

/**
 * Valida que una URL de paginación pertenezca a la misma instancia base.
 *
 * Esto agrega una capa de seguridad para evitar seguir URLs inesperadas
 * en el campo "next" de la paginación.
 */
function phishingValidateUrl_(url) {
  if (!url.startsWith(SMARTFENSE_CONFIG.baseUrl + '/')) {
    throw new Error('La URL de paginación no pertenece a la instancia esperada: ' + url);
  }
}

/**
 * Consulta una página del endpoint y devuelve su contenido parseado como JSON.
 *
 * Recibe:
 * - url: URL completa de la página a consultar
 * - token: access token OAuth2
 *
 * También:
 * - valida que la URL sea segura,
 * - ejecuta el GET,
 * - controla errores HTTP,
 * - y asegura que la respuesta sea JSON válido.
 */
function phishingGetPage_(url, token) {
  phishingValidateUrl_(url);

  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + token,
      Accept: 'application/json'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status < 200 || status >= 300) {
    throw new Error('Error consultando página. HTTP ' + status + ' - ' + body);
  }

  try {
    return JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta de la API no es JSON válido.');
  }
}

/**
 * Recorre todas las páginas del endpoint de phishing y acumula todos los resultados.
 *
 * Comportamiento:
 * - obtiene el token,
 * - construye la URL inicial,
 * - sigue el campo "next" mientras exista,
 * - concatena todos los registros encontrados en "results",
 * - y corta si se supera el máximo de páginas definido en SMARTFENSE_CONFIG.maxPages.
 *
 * Devuelve un array con todos los usuarios recuperados desde la API.
 */
function phishingGetResults_() {
  const token = phishingGetToken_();
  let nextUrl = SMARTFENSE_CONFIG.baseUrl + PHISHING_CONFIG.endpointPath;
  let allResults = [];
  let pageCount = 0;

  while (nextUrl) {
    pageCount++;

    if (pageCount > SMARTFENSE_CONFIG.maxPages) {
      throw new Error('Se alcanzó el máximo de páginas permitidas (' + SMARTFENSE_CONFIG.maxPages + ').');
    }

    const page = phishingGetPage_(nextUrl, token);
    const results = Array.isArray(page.results) ? page.results : [];

    allResults = allResults.concat(results);
    nextUrl = page && page.next ? page.next : null;
  }

  return allResults;
}

/**
 * Convierte la estructura anidada de la API en filas planas listas para exportar.
 *
 * Lógica de transformación:
 * - cada usuario aporta sus datos base,
 * - cada campaña relacionada genera una fila independiente,
 * - si el usuario no tiene campañas, se genera igualmente una fila
 *   con los datos del usuario y las columnas de campaña vacías.
 *
 * Resultado:
 * Una fila = un usuario + una campaña relacionada.
 */
function phishingFlattenResults_(results) {
  const rows = [];

  results.forEach(function(user) {
    // Datos base del usuario que se repetirán en cada fila de campaña.
    const userBase = {
      first_name: phishingValueOrEmpty_(user.first_name),
      last_name: phishingValueOrEmpty_(user.last_name),
      email: phishingValueOrEmpty_(user.email),
      groups: phishingNormalizeListValue_(user.groups),
      functional_areas: phishingNormalizeListValue_(user.functional_areas),
      hierarchical_levels: phishingNormalizeListValue_(user.hierarchical_levels)
    };

    const campaigns = Array.isArray(user.related_campaigns) ? user.related_campaigns : [];

    // Si no hay campañas relacionadas, igual se agrega una fila con columnas vacías.
    if (campaigns.length === 0) {
      rows.push(phishingNormalizeDateFields_(Object.assign({}, userBase, phishingEmptyCampaignRow_())));
      return;
    }

    // Si hay campañas, cada campaña se convierte en una fila individual.
    campaigns.forEach(function(campaign) {
      const row = {
        campaign_id: phishingValueOrEmpty_(campaign.campaign_id),
        campaign_mode: phishingValueOrEmpty_(campaign.campaign_mode),
        campaign_name: phishingValueOrEmpty_(campaign.campaign_name),
        campaign_description: phishingValueOrEmpty_(campaign.campaign_description),
        campaign_state: phishingValueOrEmpty_(campaign.campaign_state),
        campaign_date: phishingValueOrEmpty_(campaign.campaign_date),
        campaign_expiration_date: phishingValueOrEmpty_(campaign.campaign_expiration_date),
        campaign_is_test: phishingValueOrEmpty_(campaign.campaign_is_test),
        content_name: phishingValueOrEmpty_(campaign.content_name),
        content_type: phishingValueOrEmpty_(campaign.content_type),
        content_current_state: phishingValueOrEmpty_(campaign.content_current_state),
        content_code: phishingValueOrEmpty_(campaign.content_code),
        activity_sent: phishingValueOrEmpty_(campaign.activity_sent),
        activity_sent_date: phishingValueOrEmpty_(campaign.activity_sent_date),
        activity_open: phishingValueOrEmpty_(campaign.activity_open),
        activity_open_date: phishingValueOrEmpty_(campaign.activity_open_date),
        activity_click: phishingValueOrEmpty_(campaign.activity_click),
        activity_click_date: phishingValueOrEmpty_(campaign.activity_click_date),
        activity_post: phishingValueOrEmpty_(campaign.activity_post),
        activity_post_date: phishingValueOrEmpty_(campaign.activity_post_date),
        activity_simulation_reported: phishingValueOrEmpty_(campaign.activity_simulation_reported),
        activity_simulation_reported_date: phishingValueOrEmpty_(campaign.activity_simulation_reported_date),
        teachable_moment_topic: phishingValueOrEmpty_(campaign.teachable_moment_topic),
        teachable_moment_action: phishingValueOrEmpty_(campaign.teachable_moment_action),
        teachable_moment_moment_type: phishingValueOrEmpty_(campaign.teachable_moment_moment_type),
        activity_teachable_moment_sent: phishingValueOrEmpty_(campaign.activity_teachable_moment_sent),
        activity_teachable_moment_sent_date: phishingValueOrEmpty_(campaign.activity_teachable_moment_sent_date),
        activity_teachable_moment_open: phishingValueOrEmpty_(campaign.activity_teachable_moment_open),
        activity_teachable_moment_open_date: phishingValueOrEmpty_(campaign.activity_teachable_moment_open_date),
        activity_teachable_moment_correct_answer: phishingValueOrEmpty_(campaign.activity_teachable_moment_correct_answer),
        activity_teachable_moment_correct_answer_date: phishingValueOrEmpty_(campaign.activity_teachable_moment_correct_answer_date),
        activity_teachable_moment_incorrect_answer: phishingValueOrEmpty_(campaign.activity_teachable_moment_incorrect_answer),
        activity_teachable_moment_incorrect_answer_date: phishingValueOrEmpty_(campaign.activity_teachable_moment_incorrect_answer_date),
        activity_minutes: phishingValueOrEmpty_(campaign.activity_minutes),
        campaign_random_send: phishingValueOrEmpty_(campaign.campaign_random_send),
        campaign_sample_send: phishingValueOrEmpty_(campaign.campaign_sample_send),
        campaign_dont_allow_password_entry: phishingValueOrEmpty_(campaign.campaign_dont_allow_password_entry),
        campaign_host: phishingValueOrEmpty_(campaign.campaign_host),
        campaign_domain: phishingValueOrEmpty_(campaign.campaign_domain),
        campaign_phishing_url: phishingValueOrEmpty_(campaign.campaign_phishing_url)
      };

      // Antes de guardar la fila, se normalizan los campos de fecha.
      rows.push(phishingNormalizeDateFields_(Object.assign({}, userBase, row)));
    });
  });

  return rows;
}

/**
 * Devuelve una fila vacía con todas las columnas de campaña inicializadas en string vacío.
 *
 * Se usa cuando un usuario no tiene campañas relacionadas, para que la exportación
 * mantenga una estructura tabular consistente.
 */
function phishingEmptyCampaignRow_() {
  return {
    campaign_id: '',
    campaign_mode: '',
    campaign_name: '',
    campaign_description: '',
    campaign_state: '',
    campaign_date: '',
    campaign_expiration_date: '',
    campaign_is_test: '',
    content_name: '',
    content_type: '',
    content_current_state: '',
    content_code: '',
    activity_sent: '',
    activity_sent_date: '',
    activity_open: '',
    activity_open_date: '',
    activity_click: '',
    activity_click_date: '',
    activity_post: '',
    activity_post_date: '',
    activity_simulation_reported: '',
    activity_simulation_reported_date: '',
    teachable_moment_topic: '',
    teachable_moment_action: '',
    teachable_moment_moment_type: '',
    activity_teachable_moment_sent: '',
    activity_teachable_moment_sent_date: '',
    activity_teachable_moment_open: '',
    activity_teachable_moment_open_date: '',
    activity_teachable_moment_correct_answer: '',
    activity_teachable_moment_correct_answer_date: '',
    activity_teachable_moment_incorrect_answer: '',
    activity_teachable_moment_incorrect_answer_date: '',
    activity_minutes: '',
    campaign_random_send: '',
    campaign_sample_send: '',
    campaign_dont_allow_password_entry: '',
    campaign_host: '',
    campaign_domain: '',
    campaign_phishing_url: ''
  };
}

/**
 * Función principal de exportación.
 *
 * Proceso completo:
 * 1. Define el orden de columnas de salida.
 * 2. Obtiene el spreadsheet activo.
 * 3. Descarga todos los datos desde la API.
 * 4. Aplana los resultados para convertirlos en filas.
 * 5. Obtiene o crea la hoja PHISHING.
 * 6. Limpia el contenido anterior.
 * 7. Escribe encabezados.
 * 8. Escribe filas de datos.
 * 9. Aplica formato visual a las columnas de fecha.
 *
 * Manejo de errores:
 * - Si ocurre un problema y la hoja ya existe, la limpia
 *   y deja un mensaje de error en A1:B1.
 * - Luego vuelve a lanzar el error para que también quede visible en ejecución.
 */
function exportPhishingToSheet() {
  const headers = [
    'first_name',
    'last_name',
    'email',
    'groups',
    'functional_areas',
    'hierarchical_levels',
    'campaign_id',
    'campaign_mode',
    'campaign_name',
    'campaign_description',
    'campaign_state',
    'campaign_date',
    'campaign_expiration_date',
    'campaign_is_test',
    'content_name',
    'content_type',
    'content_current_state',
    'content_code',
    'activity_sent',
    'activity_sent_date',
    'activity_open',
    'activity_open_date',
    'activity_click',
    'activity_click_date',
    'activity_post',
    'activity_post_date',
    'activity_simulation_reported',
    'activity_simulation_reported_date',
    'teachable_moment_topic',
    'teachable_moment_action',
    'teachable_moment_moment_type',
    'activity_teachable_moment_sent',
    'activity_teachable_moment_sent_date',
    'activity_teachable_moment_open',
    'activity_teachable_moment_open_date',
    'activity_teachable_moment_correct_answer',
    'activity_teachable_moment_correct_answer_date',
    'activity_teachable_moment_incorrect_answer',
    'activity_teachable_moment_incorrect_answer_date',
    'activity_minutes',
    'campaign_random_send',
    'campaign_sample_send',
    'campaign_dont_allow_password_entry',
    'campaign_host',
    'campaign_domain',
    'campaign_phishing_url'
  ];

  let sheet = null;

  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    if (!ss) {
      throw new Error('No hay un spreadsheet activo. Abrí el script desde Google Sheets.');
    }

    const results = phishingGetResults_();
    const rows = phishingFlattenResults_(results);

    sheet = phishingGetOrCreateSheet_(ss, PHISHING_CONFIG.sheetName);
    sheet.clearContents();

    // Escribe la fila de encabezados.
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

    // Si hay datos, los escribe respetando el orden definido en headers.
    if (rows.length > 0) {
      const values = rows.map(function(row) {
        return headers.map(function(header) {
          return row[header];
        });
      });

      sheet.getRange(2, 1, values.length, headers.length).setValues(values);
      phishingApplyDateFormats_(sheet, headers, values.length);
    }

  } catch (error) {
    // Si algo falla, deja una marca visible en la hoja.
    if (sheet) {
      sheet.clearContents();
      sheet.getRange('A1').setValue('ERROR');
      sheet.getRange('B1').setValue(error.message);
    }
    throw error;
  }
}

/**
 * Devuelve una hoja existente por nombre o la crea si no existe.
 *
 * Esto permite que la exportación funcione tanto en una hoja ya creada
 * como en una ejecución inicial sin estructura previa.
 */
function phishingGetOrCreateSheet_(spreadsheet, sheetName) {
  let sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }

  return sheet;
}

/**
 * Devuelve el valor recibido o string vacío si viene null/undefined.
 *
 * Se usa para evitar celdas con valores nulos o errores al exportar.
 */
function phishingValueOrEmpty_(value) {
  if (value === null || value === undefined) return '';
  return value;
}

/**
 * Normaliza valores tipo lista u objeto a texto.
 *
 * Casos:
 * - null / undefined => ''
 * - array => une elementos con coma y espacio
 * - objeto => JSON.stringify
 * - cualquier otro valor => String(value)
 *
 * Esto es útil para campos como grupos, áreas funcionales o niveles jerárquicos.
 */
function phishingNormalizeListValue_(value) {
  if (value === null || value === undefined) return '';

  if (Array.isArray(value)) {
    return value
      .map(function(item) {
        if (item === null || item === undefined) return '';
        if (typeof item === 'object') return JSON.stringify(item);
        return String(item);
      })
      .filter(function(item) {
        return item !== '';
      })
      .join(', ');
  }

  if (typeof value === 'object') {
    return JSON.stringify(value);
  }

  return String(value);
}

/**
 * Recorre todos los campos de fecha configurados y convierte sus valores
 * al tipo Date cuando el contenido puede interpretarse como fecha válida.
 *
 * Si no puede parsearse, deja string vacío.
 */
function phishingNormalizeDateFields_(row) {
  PHISHING_CONFIG.dateFields.forEach(function(field) {
    row[field] = phishingParseDate_(row[field]);
  });

  return row;
}

/**
 * Intenta convertir distintos formatos de fecha a un objeto Date.
 *
 * Formatos soportados:
 * - dd/MM/yyyy HH:mm:ss.SS
 * - dd/MM/yyyy HH:mm:ss
 * - dd/MM/yyyy HH:mm
 * - yyyy-MM-dd HH:mm:ss(.SSS)
 * - ISO nativo
 *
 * Si no reconoce el formato o la fecha no es válida, devuelve ''.
 *
 * Esto permite que Google Sheets reciba fechas reales
 * y luego pueda aplicarles formato visual correctamente.
 */
function phishingParseDate_(value) {
  if (value === null || value === undefined) return '';
  if (value === '') return '';

  // Si ya es un objeto Date válido, lo reutiliza.
  if (Object.prototype.toString.call(value) === '[object Date]') {
    return isNaN(value.getTime()) ? '' : value;
  }

  const str = String(value).trim();
  if (str === '') return '';

  let match;

  // Formato: dd/MM/yyyy HH:mm:ss.SS o dd/MM/yyyy HH:mm:ss o dd/MM/yyyy HH:mm
  match = str.match(
    /^(\d{2})\/(\d{2})\/(\d{4})(?:\s+(\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const day = Number(match[1]);
    const month = Number(match[2]) - 1;
    const year = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Formato: yyyy-MM-dd HH:mm:ss(.SSS) o con separador "T"
  match = str.match(
    /^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const year = Number(match[1]);
    const month = Number(match[2]) - 1;
    const day = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Último intento: dejar que JavaScript interprete el valor como fecha ISO.
  const nativeDate = new Date(str);
  if (!isNaN(nativeDate.getTime())) {
    return nativeDate;
  }

  return '';
}

/**
 * Aplica formato visual de fecha a las columnas configuradas como dateFields.
 *
 * No modifica los datos, sólo la forma en que se muestran en la hoja.
 * El formato aplicado se define en:
 * PHISHING_CONFIG.dateNumberFormat
 */
function phishingApplyDateFormats_(sheet, headers, rowCount) {
  PHISHING_CONFIG.dateFields.forEach(function(header) {
    const colIndex = headers.indexOf(header) + 1;
    if (colIndex > 0) {
      sheet
        .getRange(2, colIndex, rowCount, 1)
        .setNumberFormat(PHISHING_CONFIG.dateNumberFormat);
    }
  });
}

Exportación de campañas de ransomware a Google Sheets

Objetivo

Este script consulta el endpoint de campañas de ransomware por usuario en la API de SMARTFENSE y exporta los resultados a una hoja de Google Sheets llamada RANSOMWARE.

¿Qué hace este script?

El script automatiza todo el proceso de extracción y carga de datos:

  • se autentica contra la API de SMARTFENSE,

  • consulta el endpoint correspondiente,

  • recorre todas las páginas de resultados,

  • transforma la respuesta en una estructura tabular,

  • y vuelca la información en una hoja de Google Sheets.

Resultado

Al ejecutar el script, se crea o actualiza una hoja llamada RANSOMWARE dentro del spreadsheet activo.

La información queda organizada en formato tabla, lista para su análisis y reutilización.

¿Para qué sirve esta hoja?

La hoja generada funciona como base de datos operativa para análisis y visualización.

Su propósito es que los datos extraídos desde la API puedan utilizarse después en:

  • dashboards de Looker Studio,

  • análisis en Google Sheets,

  • cruces con otras fuentes,

  • reportes operativos,

  • tableros de seguimiento.

En este esquema, el script cumple la función de transformar la información del endpoint en una fuente tabular lista para ser consumida por herramientas de reporting.

Fuente de datos

Endpoint consultado:

/api/v1/users/campaigns/ransomware

Tipo de información exportada

La hoja incluye información relacionada con:

  • datos del usuario,

  • campañas de ransomware asociadas,

  • actividad del usuario dentro de la campaña,

  • descargas e interacción con archivos relacionados,

  • teachable moments,

  • fechas relevantes del proceso,

  • y datos técnicos asociados a la simulación.

Consideraciones

  • El script utiliza autenticación OAuth2.

  • Reutiliza temporalmente el token mediante caché.

  • Recorre automáticamente la paginación del endpoint.

  • Convierte los campos de fecha para que Google Sheets los reconozca correctamente.

  • Limpia y vuelve a generar la hoja en cada ejecución.

  • Si ocurre un error, intenta dejar el detalle reflejado en la propia hoja.

Código

A continuación se incluye el script comentado para referencia técnica.

Ransomware.gs

// Configuración específica para la exportación de campañas de ransomware.
// Centraliza:
// - la ruta del endpoint a consultar,
// - el nombre de la hoja de destino,
// - la clave y duración del token en caché,
// - los campos que deben interpretarse como fecha,
// - y el formato visual de fecha en Google Sheets.
const RANSOMWARE_CONFIG = {
  endpointPath: '/api/v1/users/campaigns/ransomware',
  sheetName: 'RANSOMWARE',
  tokenCacheKey: 'SMARTFENSE_ACCESS_TOKEN',
  tokenCacheSeconds: 300,
  dateFields: [
    'campaign_date',
    'campaign_expiration_date',
    'activity_sent_date',
    'activity_open_date',
    'activity_download_date',
    'activity_download_open_date',
    'activity_simulation_reported_date',
    'activity_teachable_moment_sent_date',
    'activity_teachable_moment_open_date',
    'activity_teachable_moment_correct_answer_date',
    'activity_teachable_moment_incorrect_answer_date',
    'activity_attachment_open_date',
    'activity_encryption_possible_date'
  ],
  dateNumberFormat: 'dd/MM/yyyy HH:mm:ss'
};

/**
 * Lee las credenciales OAuth2 desde Script Properties.
 *
 * Espera encontrar:
 * - SMARTFENSE_CLIENT_ID
 * - SMARTFENSE_CLIENT_SECRET
 *
 * Si alguna no existe, corta la ejecución con error.
 */
function ransomwareGetCredentials_() {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('SMARTFENSE_CLIENT_ID');
  const clientSecret = props.getProperty('SMARTFENSE_CLIENT_SECRET');

  if (!clientId || !clientSecret) {
    throw new Error('Faltan SMARTFENSE_CLIENT_ID o SMARTFENSE_CLIENT_SECRET en Script Properties.');
  }

  return { clientId, clientSecret };
}

/**
 * Recupera un token previamente guardado en caché.
 *
 * Esto evita solicitar un token nuevo en cada ejecución
 * mientras siga vigente dentro del tiempo configurado.
 */
function ransomwareGetCachedToken_() {
  return CacheService.getScriptCache().get(RANSOMWARE_CONFIG.tokenCacheKey);
}

/**
 * Guarda el token OAuth2 en la caché del script.
 *
 * El tiempo de vida del token cacheado se define en:
 * RANSOMWARE_CONFIG.tokenCacheSeconds
 */
function ransomwareSetCachedToken_(token) {
  CacheService.getScriptCache().put(
    RANSOMWARE_CONFIG.tokenCacheKey,
    token,
    RANSOMWARE_CONFIG.tokenCacheSeconds
  );
}

/**
 * Obtiene un access token OAuth2 usando el flujo client_credentials.
 *
 * Flujo:
 * 1. Intenta reutilizar un token cacheado.
 * 2. Si no existe, lee credenciales desde Script Properties.
 * 3. Llama al endpoint de token configurado en SMARTFENSE_CONFIG.tokenUrl.
 * 4. Valida la respuesta HTTP.
 * 5. Parsea el JSON y extrae access_token.
 * 6. Guarda el token en caché para próximas ejecuciones.
 */
function ransomwareGetToken_() {
  const cachedToken = ransomwareGetCachedToken_();
  if (cachedToken) {
    return cachedToken;
  }

  const { clientId, clientSecret } = ransomwareGetCredentials_();
  const basicAuth = Utilities.base64Encode(clientId + ':' + clientSecret);

  const response = UrlFetchApp.fetch(SMARTFENSE_CONFIG.tokenUrl, {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: 'grant_type=client_credentials',
    headers: {
      Authorization: 'Basic ' + basicAuth,
      Accept: 'application/json',
      'Cache-Control': 'no-cache'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  // Si la API responde fuera del rango 2xx, se informa el error completo.
  if (status < 200 || status >= 300) {
    throw new Error('Error obteniendo token. HTTP ' + status + ' - ' + body);
  }

  let json;
  try {
    json = JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta del token no es JSON válido.');
  }

  // Se valida que la respuesta incluya access_token.
  if (!json.access_token) {
    throw new Error('La respuesta no incluye access_token.');
  }

  ransomwareSetCachedToken_(json.access_token);
  return json.access_token;
}

/**
 * Valida que una URL de paginación pertenezca a la misma instancia base.
 *
 * Esto agrega una capa de seguridad para evitar seguir URLs inesperadas
 * en el campo "next" de la paginación.
 */
function ransomwareValidateUrl_(url) {
  if (!url.startsWith(SMARTFENSE_CONFIG.baseUrl + '/')) {
    throw new Error('La URL de paginación no pertenece a la instancia esperada: ' + url);
  }
}

/**
 * Consulta una página del endpoint y devuelve su contenido parseado como JSON.
 *
 * Recibe:
 * - url: URL completa de la página a consultar
 * - token: access token OAuth2
 *
 * También:
 * - valida que la URL sea segura,
 * - ejecuta el GET,
 * - controla errores HTTP,
 * - y asegura que la respuesta sea JSON válido.
 */
function ransomwareGetPage_(url, token) {
  ransomwareValidateUrl_(url);

  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + token,
      Accept: 'application/json'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status < 200 || status >= 300) {
    throw new Error('Error consultando página. HTTP ' + status + ' - ' + body);
  }

  try {
    return JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta de la API no es JSON válido.');
  }
}

/**
 * Recorre todas las páginas del endpoint de ransomware y acumula todos los resultados.
 *
 * Comportamiento:
 * - obtiene el token,
 * - construye la URL inicial,
 * - sigue el campo "next" mientras exista,
 * - concatena todos los registros encontrados en "results",
 * - y corta si se supera el máximo de páginas definido en SMARTFENSE_CONFIG.maxPages.
 *
 * Devuelve un array con todos los usuarios recuperados desde la API.
 */
function ransomwareGetResults_() {
  const token = ransomwareGetToken_();
  let nextUrl = SMARTFENSE_CONFIG.baseUrl + RANSOMWARE_CONFIG.endpointPath;
  let allResults = [];
  let pageCount = 0;

  while (nextUrl) {
    pageCount++;

    if (pageCount > SMARTFENSE_CONFIG.maxPages) {
      throw new Error('Se alcanzó el máximo de páginas permitidas (' + SMARTFENSE_CONFIG.maxPages + ').');
    }

    const page = ransomwareGetPage_(nextUrl, token);
    const results = Array.isArray(page.results) ? page.results : [];

    allResults = allResults.concat(results);
    nextUrl = page && page.next ? page.next : null;
  }

  return allResults;
}

/**
 * Convierte la estructura anidada de la API en filas planas listas para exportar.
 *
 * Lógica de transformación:
 * - cada usuario aporta sus datos base,
 * - cada campaña relacionada genera una fila independiente,
 * - si el usuario no tiene campañas, se genera igualmente una fila
 *   con los datos del usuario y las columnas de campaña vacías.
 *
 * Resultado:
 * Una fila = un usuario + una campaña relacionada.
 */
function ransomwareFlattenResults_(results) {
  const rows = [];

  results.forEach(function(user) {
    // Datos base del usuario que se repetirán en cada fila de campaña.
    const userBase = {
      first_name: ransomwareValueOrEmpty_(user.first_name),
      last_name: ransomwareValueOrEmpty_(user.last_name),
      email: ransomwareValueOrEmpty_(user.email),
      groups: ransomwareNormalizeListValue_(user.groups),
      functional_areas: ransomwareNormalizeListValue_(user.functional_areas),
      hierarchical_levels: ransomwareNormalizeListValue_(user.hierarchical_levels)
    };

    const campaigns = Array.isArray(user.related_campaigns) ? user.related_campaigns : [];

    // Si no hay campañas relacionadas, igual se agrega una fila con columnas vacías.
    if (campaigns.length === 0) {
      rows.push(ransomwareNormalizeDateFields_(Object.assign({}, userBase, ransomwareEmptyCampaignRow_())));
      return;
    }

    // Si hay campañas, cada campaña se convierte en una fila individual.
    campaigns.forEach(function(campaign) {
      const row = {
        campaign_id: ransomwareValueOrEmpty_(campaign.campaign_id),
        campaign_mode: ransomwareValueOrEmpty_(campaign.campaign_mode),
        campaign_name: ransomwareValueOrEmpty_(campaign.campaign_name),
        campaign_description: ransomwareValueOrEmpty_(campaign.campaign_description),
        campaign_state: ransomwareValueOrEmpty_(campaign.campaign_state),
        campaign_date: ransomwareValueOrEmpty_(campaign.campaign_date),
        campaign_expiration_date: ransomwareValueOrEmpty_(campaign.campaign_expiration_date),
        campaign_is_test: ransomwareValueOrEmpty_(campaign.campaign_is_test),
        content_name: ransomwareValueOrEmpty_(campaign.content_name),
        content_type: ransomwareValueOrEmpty_(campaign.content_type),
        content_current_state: ransomwareValueOrEmpty_(campaign.content_current_state),
        content_code: ransomwareValueOrEmpty_(campaign.content_code),
        activity_sent: ransomwareValueOrEmpty_(campaign.activity_sent),
        activity_sent_date: ransomwareValueOrEmpty_(campaign.activity_sent_date),
        activity_open: ransomwareValueOrEmpty_(campaign.activity_open),
        activity_open_date: ransomwareValueOrEmpty_(campaign.activity_open_date),
        activity_download: ransomwareValueOrEmpty_(campaign.activity_download),
        activity_download_date: ransomwareValueOrEmpty_(campaign.activity_download_date),
        activity_download_open: ransomwareValueOrEmpty_(campaign.activity_download_open),
        activity_download_open_date: ransomwareValueOrEmpty_(campaign.activity_download_open_date),
        activity_simulation_reported: ransomwareValueOrEmpty_(campaign.activity_simulation_reported),
        activity_simulation_reported_date: ransomwareValueOrEmpty_(campaign.activity_simulation_reported_date),
        teachable_moment_topic: ransomwareValueOrEmpty_(campaign.teachable_moment_topic),
        teachable_moment_action: ransomwareValueOrEmpty_(campaign.teachable_moment_action),
        teachable_moment_moment_type: ransomwareValueOrEmpty_(campaign.teachable_moment_moment_type),
        activity_teachable_moment_sent: ransomwareValueOrEmpty_(campaign.activity_teachable_moment_sent),
        activity_teachable_moment_sent_date: ransomwareValueOrEmpty_(campaign.activity_teachable_moment_sent_date),
        activity_teachable_moment_open: ransomwareValueOrEmpty_(campaign.activity_teachable_moment_open),
        activity_teachable_moment_open_date: ransomwareValueOrEmpty_(campaign.activity_teachable_moment_open_date),
        activity_teachable_moment_correct_answer: ransomwareValueOrEmpty_(campaign.activity_teachable_moment_correct_answer),
        activity_teachable_moment_correct_answer_date: ransomwareValueOrEmpty_(campaign.activity_teachable_moment_correct_answer_date),
        activity_teachable_moment_incorrect_answer: ransomwareValueOrEmpty_(campaign.activity_teachable_moment_incorrect_answer),
        activity_teachable_moment_incorrect_answer_date: ransomwareValueOrEmpty_(campaign.activity_teachable_moment_incorrect_answer_date),
        campaign_random_send: ransomwareValueOrEmpty_(campaign.campaign_random_send),
        campaign_sample_send: ransomwareValueOrEmpty_(campaign.campaign_sample_send),
        campaign_host: ransomwareValueOrEmpty_(campaign.campaign_host),
        campaign_domain: ransomwareValueOrEmpty_(campaign.campaign_domain),
        campaign_ransomware_url: ransomwareValueOrEmpty_(campaign.campaign_ransomware_url),
        activity_attachment_open: ransomwareValueOrEmpty_(campaign.activity_attachment_open),
        activity_attachment_open_date: ransomwareValueOrEmpty_(campaign.activity_attachment_open_date),
        activity_encryption_possible: ransomwareValueOrEmpty_(campaign.activity_encryption_possible),
        activity_encryption_possible_date: ransomwareValueOrEmpty_(campaign.activity_encryption_possible_date)
      };

      // Antes de guardar la fila, se normalizan los campos de fecha.
      rows.push(ransomwareNormalizeDateFields_(Object.assign({}, userBase, row)));
    });
  });

  return rows;
}

/**
 * Devuelve una fila vacía con todas las columnas de campaña inicializadas como string vacío.
 *
 * Se usa cuando un usuario no tiene campañas relacionadas, para que la exportación
 * mantenga una estructura tabular consistente.
 */
function ransomwareEmptyCampaignRow_() {
  return {
    campaign_id: '',
    campaign_mode: '',
    campaign_name: '',
    campaign_description: '',
    campaign_state: '',
    campaign_date: '',
    campaign_expiration_date: '',
    campaign_is_test: '',
    content_name: '',
    content_type: '',
    content_current_state: '',
    content_code: '',
    activity_sent: '',
    activity_sent_date: '',
    activity_open: '',
    activity_open_date: '',
    activity_download: '',
    activity_download_date: '',
    activity_download_open: '',
    activity_download_open_date: '',
    activity_simulation_reported: '',
    activity_simulation_reported_date: '',
    teachable_moment_topic: '',
    teachable_moment_action: '',
    teachable_moment_moment_type: '',
    activity_teachable_moment_sent: '',
    activity_teachable_moment_sent_date: '',
    activity_teachable_moment_open: '',
    activity_teachable_moment_open_date: '',
    activity_teachable_moment_correct_answer: '',
    activity_teachable_moment_correct_answer_date: '',
    activity_teachable_moment_incorrect_answer: '',
    activity_teachable_moment_incorrect_answer_date: '',
    campaign_random_send: '',
    campaign_sample_send: '',
    campaign_host: '',
    campaign_domain: '',
    campaign_ransomware_url: '',
    activity_attachment_open: '',
    activity_attachment_open_date: '',
    activity_encryption_possible: '',
    activity_encryption_possible_date: ''
  };
}

/**
 * Función principal del proceso de exportación.
 *
 * Flujo:
 * 1. Define el orden de columnas.
 * 2. Obtiene la planilla activa.
 * 3. Descarga todos los datos desde la API.
 * 4. Aplana los resultados.
 * 5. Obtiene o crea la hoja RANSOMWARE.
 * 6. Limpia el contenido previo.
 * 7. Escribe los encabezados.
 * 8. Escribe las filas de datos.
 * 9. Aplica formato a columnas de fecha.
 *
 * Si ocurre un error, intenta dejarlo visible en la hoja
 * antes de volver a lanzarlo.
 */
function exportRansomwareToSheet() {
  const headers = [
    'first_name',
    'last_name',
    'email',
    'groups',
    'functional_areas',
    'hierarchical_levels',
    'campaign_id',
    'campaign_mode',
    'campaign_name',
    'campaign_description',
    'campaign_state',
    'campaign_date',
    'campaign_expiration_date',
    'campaign_is_test',
    'content_name',
    'content_type',
    'content_current_state',
    'content_code',
    'activity_sent',
    'activity_sent_date',
    'activity_open',
    'activity_open_date',
    'activity_download',
    'activity_download_date',
    'activity_download_open',
    'activity_download_open_date',
    'activity_simulation_reported',
    'activity_simulation_reported_date',
    'teachable_moment_topic',
    'teachable_moment_action',
    'teachable_moment_moment_type',
    'activity_teachable_moment_sent',
    'activity_teachable_moment_sent_date',
    'activity_teachable_moment_open',
    'activity_teachable_moment_open_date',
    'activity_teachable_moment_correct_answer',
    'activity_teachable_moment_correct_answer_date',
    'activity_teachable_moment_incorrect_answer',
    'activity_teachable_moment_incorrect_answer_date',
    'campaign_random_send',
    'campaign_sample_send',
    'campaign_host',
    'campaign_domain',
    'campaign_ransomware_url',
    'activity_attachment_open',
    'activity_attachment_open_date',
    'activity_encryption_possible',
    'activity_encryption_possible_date'
  ];

  let sheet = null;

  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    if (!ss) {
      throw new Error('No hay un spreadsheet activo. Abrí el script desde Google Sheets.');
    }

    const results = ransomwareGetResults_();
    const rows = ransomwareFlattenResults_(results);

    sheet = ransomwareGetOrCreateSheet_(ss, RANSOMWARE_CONFIG.sheetName);
    sheet.clearContents();

    // Escribe la fila de encabezados.
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

    // Si hay datos, los escribe respetando el orden definido en headers.
    if (rows.length > 0) {
      const values = rows.map(function(row) {
        return headers.map(function(header) {
          return row[header];
        });
      });

      sheet.getRange(2, 1, values.length, headers.length).setValues(values);
      ransomwareApplyDateFormats_(sheet, headers, values.length);
    }

  } catch (error) {
    // Si algo falla, deja una marca visible en la hoja.
    if (sheet) {
      sheet.clearContents();
      sheet.getRange('A1').setValue('ERROR');
      sheet.getRange('B1').setValue(error.message);
    }
    throw error;
  }
}

/**
 * Busca una hoja por nombre y la crea si no existe.
 */
function ransomwareGetOrCreateSheet_(spreadsheet, sheetName) {
  let sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }

  return sheet;
}

/**
 * Devuelve el valor recibido o una cadena vacía si es null/undefined.
 */
function ransomwareValueOrEmpty_(value) {
  if (value === null || value === undefined) return '';
  return value;
}

/**
 * Convierte arrays u objetos en texto exportable.
 *
 * - null / undefined => ''
 * - array => elementos unidos por coma
 * - objeto => JSON.stringify
 * - otros => String(value)
 */
function ransomwareNormalizeListValue_(value) {
  if (value === null || value === undefined) return '';

  if (Array.isArray(value)) {
    return value
      .map(function(item) {
        if (item === null || item === undefined) return '';
        if (typeof item === 'object') return JSON.stringify(item);
        return String(item);
      })
      .filter(function(item) {
        return item !== '';
      })
      .join(', ');
  }

  if (typeof value === 'object') {
    return JSON.stringify(value);
  }

  return String(value);
}

/**
 * Recorre todos los campos configurados como fecha y los convierte.
 */
function ransomwareNormalizeDateFields_(row) {
  RANSOMWARE_CONFIG.dateFields.forEach(function(field) {
    row[field] = ransomwareParseDate_(row[field]);
  });

  return row;
}

/**
 * Intenta convertir distintos formatos de fecha a Date.
 *
 * Soporta:
 * - dd/MM/yyyy HH:mm:ss.SS
 * - dd/MM/yyyy HH:mm:ss
 * - dd/MM/yyyy HH:mm
 * - yyyy-MM-dd HH:mm:ss(.SSS)
 * - ISO
 *
 * Si no puede interpretarse como fecha válida, devuelve ''.
 */
function ransomwareParseDate_(value) {
  if (value === null || value === undefined) return '';
  if (value === '') return '';

  if (Object.prototype.toString.call(value) === '[object Date]') {
    return isNaN(value.getTime()) ? '' : value;
  }

  const str = String(value).trim();
  if (str === '') return '';

  let match;

  // Formato: dd/MM/yyyy HH:mm:ss.SS o dd/MM/yyyy HH:mm:ss o dd/MM/yyyy HH:mm
  match = str.match(
    /^(\d{2})\/(\d{2})\/(\d{4})(?:\s+(\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const day = Number(match[1]);
    const month = Number(match[2]) - 1;
    const year = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Formato: yyyy-MM-dd HH:mm:ss(.SSS)
  match = str.match(
    /^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const year = Number(match[1]);
    const month = Number(match[2]) - 1;
    const day = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Último intento: interpretación nativa de JavaScript.
  const nativeDate = new Date(str);
  if (!isNaN(nativeDate.getTime())) {
    return nativeDate;
  }

  return '';
}

/**
 * Aplica formato visual a las columnas de fecha de la hoja.
 */
function ransomwareApplyDateFormats_(sheet, headers, rowCount) {
  RANSOMWARE_CONFIG.dateFields.forEach(function(header) {
    const colIndex = headers.indexOf(header) + 1;
    if (colIndex > 0) {
      sheet
        .getRange(2, colIndex, rowCount, 1)
        .setNumberFormat(RANSOMWARE_CONFIG.dateNumberFormat);
    }
  });
}

Exportación de campañas de smishing a Google Sheets

Objetivo

Este script consulta el endpoint de campañas de smishing por usuario en la API de SMARTFENSE y exporta los resultados a una hoja de Google Sheets llamada SMISHING.

¿Qué hace este script?

El script automatiza todo el proceso de extracción y carga de datos:

  • se autentica contra la API de SMARTFENSE,

  • consulta el endpoint correspondiente,

  • recorre todas las páginas de resultados,

  • transforma la respuesta en una estructura tabular,

  • y vuelca la información en una hoja de Google Sheets.

Resultado

Al ejecutar el script, se crea o actualiza una hoja llamada SMISHING dentro del spreadsheet activo.

La información queda organizada en formato tabla, lista para su análisis y reutilización.

¿Para qué sirve esta hoja?

La hoja generada funciona como base de datos operativa para análisis y visualización.

Su propósito es que los datos extraídos desde la API puedan utilizarse después en:

  • dashboards de Looker Studio,

  • análisis en Google Sheets,

  • cruces con otras fuentes,

  • reportes operativos,

  • tableros de seguimiento.

En este esquema, el script cumple la función de transformar la información del endpoint en una fuente tabular lista para ser consumida por herramientas de reporting.

Fuente de datos

Endpoint consultado:

/api/v1/users/campaigns/smishing

Tipo de información exportada

La hoja incluye información relacionada con:

  • datos del usuario,

  • campañas de smishing asociadas,

  • actividad del usuario dentro de la campaña,

  • estado de envío, entrega, clic e interacción posterior,

  • teachable moments,

  • fechas relevantes del proceso,

  • y datos técnicos asociados a la simulación.

Consideraciones

  • El script utiliza autenticación OAuth2.

  • Reutiliza temporalmente el token mediante caché.

  • Recorre automáticamente la paginación del endpoint.

  • Convierte los campos de fecha para que Google Sheets los reconozca correctamente.

  • Limpia y vuelve a generar la hoja en cada ejecución.

  • Si ocurre un error, intenta dejar el detalle reflejado en la propia hoja.

Código

A continuación se incluye el script comentado para referencia técnica.

Smishing.gs

// Configuración específica para la exportación de campañas de smishing.
// Centraliza:
// - la ruta del endpoint a consultar,
// - el nombre de la hoja de destino,
// - la clave y duración del token en caché,
// - los campos que deben interpretarse como fecha,
// - y el formato visual de fecha en Google Sheets.
const SMISHING_CONFIG = {
  endpointPath: '/api/v1/users/campaigns/smishing',
  sheetName: 'SMISHING',
  tokenCacheKey: 'SMARTFENSE_ACCESS_TOKEN',
  tokenCacheSeconds: 300,
  dateFields: [
    'campaign_date',
    'campaign_expiration_date',
    'activity_sent_date',
    'activity_delivered_date',
    'activity_click_date',
    'activity_post_date',
    'activity_teachable_moment_sent_date',
    'activity_teachable_moment_open_date',
    'activity_teachable_moment_correct_answer_date',
    'activity_teachable_moment_incorrect_answer_date'
  ],
  dateNumberFormat: 'dd/MM/yyyy HH:mm:ss'
};

/**
 * Lee las credenciales OAuth2 desde Script Properties.
 *
 * Espera encontrar:
 * - SMARTFENSE_CLIENT_ID
 * - SMARTFENSE_CLIENT_SECRET
 *
 * Si alguna no existe, corta la ejecución con error.
 */
function smishingGetCredentials_() {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('SMARTFENSE_CLIENT_ID');
  const clientSecret = props.getProperty('SMARTFENSE_CLIENT_SECRET');

  if (!clientId || !clientSecret) {
    throw new Error('Faltan SMARTFENSE_CLIENT_ID o SMARTFENSE_CLIENT_SECRET en Script Properties.');
  }

  return { clientId, clientSecret };
}

/**
 * Recupera un token previamente guardado en caché.
 *
 * Esto evita solicitar un token nuevo en cada ejecución
 * mientras siga vigente dentro del tiempo configurado.
 */
function smishingGetCachedToken_() {
  return CacheService.getScriptCache().get(SMISHING_CONFIG.tokenCacheKey);
}

/**
 * Guarda el token OAuth2 en la caché del script.
 *
 * El tiempo de vida del token cacheado se define en:
 * SMISHING_CONFIG.tokenCacheSeconds
 */
function smishingSetCachedToken_(token) {
  CacheService.getScriptCache().put(
    SMISHING_CONFIG.tokenCacheKey,
    token,
    SMISHING_CONFIG.tokenCacheSeconds
  );
}

/**
 * Obtiene un access token OAuth2 usando el flujo client_credentials.
 *
 * Flujo:
 * 1. Intenta reutilizar un token cacheado.
 * 2. Si no existe, lee credenciales desde Script Properties.
 * 3. Llama al endpoint de token configurado en SMARTFENSE_CONFIG.tokenUrl.
 * 4. Valida la respuesta HTTP.
 * 5. Parsea el JSON y extrae access_token.
 * 6. Guarda el token en caché para próximas ejecuciones.
 */
function smishingGetToken_() {
  const cachedToken = smishingGetCachedToken_();
  if (cachedToken) {
    return cachedToken;
  }

  const { clientId, clientSecret } = smishingGetCredentials_();
  const basicAuth = Utilities.base64Encode(clientId + ':' + clientSecret);

  const response = UrlFetchApp.fetch(SMARTFENSE_CONFIG.tokenUrl, {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: 'grant_type=client_credentials',
    headers: {
      Authorization: 'Basic ' + basicAuth,
      Accept: 'application/json',
      'Cache-Control': 'no-cache'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  // Si la API responde fuera del rango 2xx, se informa el error completo.
  if (status < 200 || status >= 300) {
    throw new Error('Error obteniendo token. HTTP ' + status + ' - ' + body);
  }

  let json;
  try {
    json = JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta del token no es JSON válido.');
  }

  // Se valida que la respuesta incluya access_token.
  if (!json.access_token) {
    throw new Error('La respuesta no incluye access_token.');
  }

  smishingSetCachedToken_(json.access_token);
  return json.access_token;
}

/**
 * Valida que una URL de paginación pertenezca a la misma instancia base.
 *
 * Esto agrega una capa de seguridad para evitar seguir URLs inesperadas
 * en el campo "next" de la paginación.
 */
function smishingValidateUrl_(url) {
  if (!url.startsWith(SMARTFENSE_CONFIG.baseUrl + '/')) {
    throw new Error('La URL de paginación no pertenece a la instancia esperada: ' + url);
  }
}

/**
 * Consulta una página del endpoint y devuelve su contenido parseado como JSON.
 *
 * Recibe:
 * - url: URL completa de la página a consultar
 * - token: access token OAuth2
 *
 * También:
 * - valida que la URL sea segura,
 * - ejecuta el GET,
 * - controla errores HTTP,
 * - y asegura que la respuesta sea JSON válido.
 */
function smishingGetPage_(url, token) {
  smishingValidateUrl_(url);

  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + token,
      Accept: 'application/json'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status < 200 || status >= 300) {
    throw new Error('Error consultando página. HTTP ' + status + ' - ' + body);
  }

  try {
    return JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta de la API no es JSON válido.');
  }
}

/**
 * Recorre todas las páginas del endpoint de smishing y devuelve
 * todos los objetos de results en una sola lista.
 *
 * Comportamiento:
 * - obtiene el token,
 * - construye la URL inicial,
 * - sigue el campo "next" mientras exista,
 * - concatena todos los registros encontrados en "results",
 * - y corta si se supera el máximo de páginas definido en SMARTFENSE_CONFIG.maxPages.
 *
 * Devuelve un array con todos los usuarios recuperados desde la API.
 */
function smishingGetResults_() {
  const token = smishingGetToken_();
  let nextUrl = SMARTFENSE_CONFIG.baseUrl + SMISHING_CONFIG.endpointPath;
  let allResults = [];
  let pageCount = 0;

  while (nextUrl) {
    pageCount++;

    if (pageCount > SMARTFENSE_CONFIG.maxPages) {
      throw new Error('Se alcanzó el máximo de páginas permitidas (' + SMARTFENSE_CONFIG.maxPages + ').');
    }

    const page = smishingGetPage_(nextUrl, token);
    const results = Array.isArray(page.results) ? page.results : [];

    allResults = allResults.concat(results);
    nextUrl = page && page.next ? page.next : null;
  }

  return allResults;
}

/**
 * Devuelve una fila vacía con todas las columnas de campaña inicializadas como string vacío.
 *
 * Se usa cuando un usuario no tiene campañas relacionadas, para que la exportación
 * mantenga una estructura tabular consistente.
 */
function smishingEmptyCampaignRow_() {
  return {
    campaign_id: '',
    campaign_mode: '',
    campaign_name: '',
    campaign_description: '',
    campaign_state: '',
    campaign_date: '',
    campaign_expiration_date: '',
    campaign_is_test: '',
    content_name: '',
    content_type: '',
    content_current_state: '',
    content_code: '',
    activity_sent: '',
    activity_sent_date: '',
    activity_delivered: '',
    activity_delivered_date: '',
    activity_click: '',
    activity_click_date: '',
    activity_post: '',
    activity_post_date: '',
    teachable_moment_topic: '',
    teachable_moment_action: '',
    teachable_moment_moment_type: '',
    activity_teachable_moment_sent: '',
    activity_teachable_moment_sent_date: '',
    activity_teachable_moment_open: '',
    activity_teachable_moment_open_date: '',
    activity_teachable_moment_correct_answer: '',
    activity_teachable_moment_correct_answer_date: '',
    activity_teachable_moment_incorrect_answer: '',
    activity_teachable_moment_incorrect_answer_date: '',
    activity_minutes: '',
    campaign_random_send: '',
    campaign_sample_send: '',
    campaign_host: '',
    campaign_domain: '',
    campaign_smishing_url: ''
  };
}

/**
 * Convierte la estructura anidada de la API en filas planas listas para exportar.
 *
 * Lógica de transformación:
 * - cada usuario aporta sus datos base,
 * - cada campaña relacionada genera una fila independiente,
 * - si el usuario no tiene campañas, se genera igualmente una fila
 *   con los datos del usuario y las columnas de campaña vacías.
 *
 * Resultado:
 * Una fila = un usuario + una campaña relacionada.
 */
function smishingFlattenResults_(results) {
  const rows = [];

  results.forEach(function(user) {
    // Datos base del usuario que se repetirán en cada fila de campaña.
    const userBase = {
      first_name: smishingValueOrEmpty_(user.first_name),
      last_name: smishingValueOrEmpty_(user.last_name),
      email: smishingValueOrEmpty_(user.email),
      groups: smishingNormalizeListValue_(user.groups),
      functional_areas: smishingNormalizeListValue_(user.functional_areas),
      hierarchical_levels: smishingNormalizeListValue_(user.hierarchical_levels)
    };

    const campaigns = Array.isArray(user.related_campaigns) ? user.related_campaigns : [];

    // Si no hay campañas relacionadas, igual se agrega una fila con columnas vacías.
    if (campaigns.length === 0) {
      rows.push(smishingNormalizeDateFields_(Object.assign({}, userBase, smishingEmptyCampaignRow_())));
      return;
    }

    // Si hay campañas, cada campaña se convierte en una fila individual.
    campaigns.forEach(function(campaign) {
      const row = {
        campaign_id: smishingValueOrEmpty_(campaign.campaign_id),
        campaign_mode: smishingValueOrEmpty_(campaign.campaign_mode),
        campaign_name: smishingValueOrEmpty_(campaign.campaign_name),
        campaign_description: smishingValueOrEmpty_(campaign.campaign_description),
        campaign_state: smishingValueOrEmpty_(campaign.campaign_state),
        campaign_date: smishingValueOrEmpty_(campaign.campaign_date),
        campaign_expiration_date: smishingValueOrEmpty_(campaign.campaign_expiration_date),
        campaign_is_test: smishingValueOrEmpty_(campaign.campaign_is_test),
        content_name: smishingValueOrEmpty_(campaign.content_name),
        content_type: smishingValueOrEmpty_(campaign.content_type),
        content_current_state: smishingValueOrEmpty_(campaign.content_current_state),
        content_code: smishingValueOrEmpty_(campaign.content_code),
        activity_sent: smishingValueOrEmpty_(campaign.activity_sent),
        activity_sent_date: smishingValueOrEmpty_(campaign.activity_sent_date),
        activity_delivered: smishingValueOrEmpty_(campaign.activity_delivered),
        activity_delivered_date: smishingValueOrEmpty_(campaign.activity_delivered_date),
        activity_click: smishingValueOrEmpty_(campaign.activity_click),
        activity_click_date: smishingValueOrEmpty_(campaign.activity_click_date),
        activity_post: smishingValueOrEmpty_(campaign.activity_post),
        activity_post_date: smishingValueOrEmpty_(campaign.activity_post_date),
        teachable_moment_topic: smishingValueOrEmpty_(campaign.teachable_moment_topic),
        teachable_moment_action: smishingValueOrEmpty_(campaign.teachable_moment_action),
        teachable_moment_moment_type: smishingValueOrEmpty_(campaign.teachable_moment_moment_type),
        activity_teachable_moment_sent: smishingValueOrEmpty_(campaign.activity_teachable_moment_sent),
        activity_teachable_moment_sent_date: smishingValueOrEmpty_(campaign.activity_teachable_moment_sent_date),
        activity_teachable_moment_open: smishingValueOrEmpty_(campaign.activity_teachable_moment_open),
        activity_teachable_moment_open_date: smishingValueOrEmpty_(campaign.activity_teachable_moment_open_date),
        activity_teachable_moment_correct_answer: smishingValueOrEmpty_(campaign.activity_teachable_moment_correct_answer),
        activity_teachable_moment_correct_answer_date: smishingValueOrEmpty_(campaign.activity_teachable_moment_correct_answer_date),
        activity_teachable_moment_incorrect_answer: smishingValueOrEmpty_(campaign.activity_teachable_moment_incorrect_answer),
        activity_teachable_moment_incorrect_answer_date: smishingValueOrEmpty_(campaign.activity_teachable_moment_incorrect_answer_date),
        activity_minutes: smishingValueOrEmpty_(campaign.activity_minutes),
        campaign_random_send: smishingValueOrEmpty_(campaign.campaign_random_send),
        campaign_sample_send: smishingValueOrEmpty_(campaign.campaign_sample_send),
        campaign_host: smishingValueOrEmpty_(campaign.campaign_host),
        campaign_domain: smishingValueOrEmpty_(campaign.campaign_domain),
        campaign_smishing_url: smishingValueOrEmpty_(campaign.campaign_smishing_url)
      };

      // Antes de guardar la fila, se normalizan los campos de fecha.
      rows.push(smishingNormalizeDateFields_(Object.assign({}, userBase, row)));
    });
  });

  return rows;
}

/**
 * Función principal del proceso de exportación.
 *
 * Flujo:
 * 1. Define el orden de columnas.
 * 2. Obtiene la planilla activa.
 * 3. Descarga todos los datos desde la API.
 * 4. Aplana los resultados.
 * 5. Obtiene o crea la hoja SMISHING.
 * 6. Limpia el contenido previo.
 * 7. Escribe los encabezados.
 * 8. Escribe las filas de datos.
 * 9. Aplica formato a columnas de fecha.
 *
 * Si ocurre un error, intenta dejarlo visible en la hoja
 * antes de volver a lanzarlo.
 */
function exportSmishingToSheet() {
  const headers = [
    'first_name',
    'last_name',
    'email',
    'groups',
    'functional_areas',
    'hierarchical_levels',
    'campaign_id',
    'campaign_mode',
    'campaign_name',
    'campaign_description',
    'campaign_state',
    'campaign_date',
    'campaign_expiration_date',
    'campaign_is_test',
    'content_name',
    'content_type',
    'content_current_state',
    'content_code',
    'activity_sent',
    'activity_sent_date',
    'activity_delivered',
    'activity_delivered_date',
    'activity_click',
    'activity_click_date',
    'activity_post',
    'activity_post_date',
    'teachable_moment_topic',
    'teachable_moment_action',
    'teachable_moment_moment_type',
    'activity_teachable_moment_sent',
    'activity_teachable_moment_sent_date',
    'activity_teachable_moment_open',
    'activity_teachable_moment_open_date',
    'activity_teachable_moment_correct_answer',
    'activity_teachable_moment_correct_answer_date',
    'activity_teachable_moment_incorrect_answer',
    'activity_teachable_moment_incorrect_answer_date',
    'activity_minutes',
    'campaign_random_send',
    'campaign_sample_send',
    'campaign_host',
    'campaign_domain',
    'campaign_smishing_url'
  ];

  let sheet = null;

  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    if (!ss) {
      throw new Error('No hay un spreadsheet activo. Abrí el script desde Google Sheets.');
    }

    const results = smishingGetResults_();
    const rows = smishingFlattenResults_(results);

    sheet = smishingGetOrCreateSheet_(ss, SMISHING_CONFIG.sheetName);
    sheet.clearContents();

    // Escribe la fila de encabezados.
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

    // Si hay datos, los escribe respetando el orden definido en headers.
    if (rows.length > 0) {
      const values = rows.map(function(row) {
        return headers.map(function(header) {
          return row[header];
        });
      });

      sheet.getRange(2, 1, values.length, headers.length).setValues(values);
      smishingApplyDateFormats_(sheet, headers, values.length);
    }

  } catch (error) {
    // Si algo falla, deja una marca visible en la hoja.
    if (sheet) {
      sheet.clearContents();
      sheet.getRange('A1').setValue('ERROR');
      sheet.getRange('B1').setValue(error.message);
    }
    throw error;
  }
}

/**
 * Busca una hoja por nombre y la crea si no existe.
 */
function smishingGetOrCreateSheet_(spreadsheet, sheetName) {
  let sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }

  return sheet;
}

/**
 * Devuelve el valor recibido o una cadena vacía si es null/undefined.
 */
function smishingValueOrEmpty_(value) {
  if (value === null || value === undefined) return '';
  return value;
}

/**
 * Convierte arrays u objetos en texto exportable.
 *
 * - null / undefined => ''
 * - array => elementos unidos por coma
 * - objeto => JSON.stringify
 * - otros => String(value)
 */
function smishingNormalizeListValue_(value) {
  if (value === null || value === undefined) return '';

  if (Array.isArray(value)) {
    return value
      .map(function(item) {
        if (item === null || item === undefined) return '';
        if (typeof item === 'object') return JSON.stringify(item);
        return String(item);
      })
      .filter(function(item) {
        return item !== '';
      })
      .join(', ');
  }

  if (typeof value === 'object') {
    return JSON.stringify(value);
  }

  return String(value);
}

/**
 * Recorre todos los campos configurados como fecha y los convierte.
 */
function smishingNormalizeDateFields_(row) {
  SMISHING_CONFIG.dateFields.forEach(function(field) {
    row[field] = smishingParseDate_(row[field]);
  });

  return row;
}

/**
 * Intenta convertir distintos formatos de fecha a Date.
 *
 * Soporta:
 * - dd/MM/yyyy HH:mm:ss.SS
 * - dd/MM/yyyy HH:mm:ss
 * - dd/MM/yyyy HH:mm
 * - yyyy-MM-dd HH:mm:ss(.SSS)
 * - ISO
 *
 * Si no puede interpretarse como fecha válida, devuelve ''.
 */
function smishingParseDate_(value) {
  if (value === null || value === undefined) return '';
  if (value === '') return '';

  if (Object.prototype.toString.call(value) === '[object Date]') {
    return isNaN(value.getTime()) ? '' : value;
  }

  const str = String(value).trim();
  if (str === '') return '';

  let match;

  // Formato: dd/MM/yyyy HH:mm:ss.SS o dd/MM/yyyy HH:mm:ss o dd/MM/yyyy HH:mm
  match = str.match(
    /^(\d{2})\/(\d{2})\/(\d{4})(?:\s+(\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const day = Number(match[1]);
    const month = Number(match[2]) - 1;
    const year = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Formato: yyyy-MM-dd HH:mm:ss(.SSS)
  match = str.match(
    /^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const year = Number(match[1]);
    const month = Number(match[2]) - 1;
    const day = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Último intento: interpretación nativa de JavaScript.
  const nativeDate = new Date(str);
  if (!isNaN(nativeDate.getTime())) {
    return nativeDate;
  }

  return '';
}

/**
 * Aplica formato visual a las columnas de fecha de la hoja.
 */
function smishingApplyDateFormats_(sheet, headers, rowCount) {
  SMISHING_CONFIG.dateFields.forEach(function(header) {
    const colIndex = headers.indexOf(header) + 1;
    if (colIndex > 0) {
      sheet
        .getRange(2, colIndex, rowCount, 1)
        .setNumberFormat(SMISHING_CONFIG.dateNumberFormat);
    }
  });
}

Exportación de campañas de módulos interactivos a Google Sheets

Objetivo

Este script consulta el endpoint de campañas de módulos interactivos por usuario en la API de SMARTFENSE y exporta los resultados a una hoja de Google Sheets llamada TRAININGS.

¿Qué hace este script?

El script automatiza todo el proceso de extracción y carga de datos:

  • se autentica contra la API de SMARTFENSE,

  • consulta el endpoint correspondiente,

  • recorre todas las páginas de resultados,

  • transforma la respuesta en una estructura tabular,

  • y vuelca la información en una hoja de Google Sheets.

Resultado

Al ejecutar el script, se crea o actualiza una hoja llamada TRAININGS dentro del spreadsheet activo.

La información queda organizada en formato tabla, lista para su análisis y reutilización.

¿Para qué sirve esta hoja?

La hoja generada funciona como base de datos operativa para análisis y visualización.

Su propósito es que los datos extraídos desde la API puedan utilizarse después en:

  • dashboards de Looker Studio,

  • análisis en Google Sheets,

  • cruces con otras fuentes,

  • reportes operativos,

  • tableros de seguimiento.

En este esquema, el script cumple la función de transformar la información del endpoint en una fuente tabular lista para ser consumida por herramientas de reporting.

Fuente de datos

Endpoint consultado:

/api/v1/users/campaigns/trainings

Tipo de información exportada

La hoja incluye información relacionada con:

  • datos del usuario,

  • campañas de training asociadas,

  • estado de envío, inicio y finalización,

  • información de exámenes y resultados,

  • calificación final y tiempo dedicado,

  • y fechas relevantes del proceso.

Consideraciones

  • El script utiliza autenticación OAuth2.

  • Reutiliza temporalmente el token mediante caché.

  • Recorre automáticamente la paginación del endpoint.

  • Convierte los campos de fecha para que Google Sheets los reconozca correctamente.

  • Limpia y vuelve a generar la hoja en cada ejecución.

  • Si ocurre un error, intenta dejar el detalle reflejado en la propia hoja.

Código

A continuación se incluye el script comentado para referencia técnica.

Trainings.gs

// Configuración específica para la exportación de campañas de trainings.
// Centraliza:
// - la ruta del endpoint a consultar,
// - el nombre de la hoja de destino,
// - la clave y duración del token en caché,
// - los campos que deben interpretarse como fecha,
// - y el formato visual de fecha en Google Sheets.
const TRAININGS_CONFIG = {
  endpointPath: '/api/v1/users/campaigns/trainings',
  sheetName: 'TRAININGS',
  tokenCacheKey: 'SMARTFENSE_ACCESS_TOKEN',
  tokenCacheSeconds: 300,
  dateFields: [
    'campaign_date',
    'campaign_expiration_date',
    'activity_sent_date',
    'activity_started_date',
    'activity_completed_date',
    'result_date'
  ],
  dateNumberFormat: 'dd/MM/yyyy HH:mm:ss'
};

/**
 * Lee las credenciales OAuth2 desde Script Properties.
 *
 * Espera encontrar:
 * - SMARTFENSE_CLIENT_ID
 * - SMARTFENSE_CLIENT_SECRET
 *
 * Si alguna no existe, corta la ejecución con error.
 */
function trainingsGetCredentials_() {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('SMARTFENSE_CLIENT_ID');
  const clientSecret = props.getProperty('SMARTFENSE_CLIENT_SECRET');

  if (!clientId || !clientSecret) {
    throw new Error('Faltan SMARTFENSE_CLIENT_ID o SMARTFENSE_CLIENT_SECRET en Script Properties.');
  }

  return { clientId, clientSecret };
}

/**
 * Recupera un token previamente guardado en caché.
 *
 * Esto evita solicitar un token nuevo en cada ejecución
 * mientras siga vigente dentro del tiempo configurado.
 */
function trainingsGetCachedToken_() {
  return CacheService.getScriptCache().get(TRAININGS_CONFIG.tokenCacheKey);
}

/**
 * Guarda el token OAuth2 en la caché del script.
 *
 * El tiempo de vida del token cacheado se define en:
 * TRAININGS_CONFIG.tokenCacheSeconds
 */
function trainingsSetCachedToken_(token) {
  CacheService.getScriptCache().put(
    TRAININGS_CONFIG.tokenCacheKey,
    token,
    TRAININGS_CONFIG.tokenCacheSeconds
  );
}

/**
 * Obtiene un access token OAuth2 usando el flujo client_credentials.
 *
 * Flujo:
 * 1. Intenta reutilizar un token cacheado.
 * 2. Si no existe, lee credenciales desde Script Properties.
 * 3. Llama al endpoint de token configurado en SMARTFENSE_CONFIG.tokenUrl.
 * 4. Valida la respuesta HTTP.
 * 5. Parsea el JSON y extrae access_token.
 * 6. Guarda el token en caché para próximas ejecuciones.
 */
function trainingsGetToken_() {
  const cachedToken = trainingsGetCachedToken_();
  if (cachedToken) {
    return cachedToken;
  }

  const { clientId, clientSecret } = trainingsGetCredentials_();
  const basicAuth = Utilities.base64Encode(clientId + ':' + clientSecret);

  const response = UrlFetchApp.fetch(SMARTFENSE_CONFIG.tokenUrl, {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: 'grant_type=client_credentials',
    headers: {
      Authorization: 'Basic ' + basicAuth,
      Accept: 'application/json',
      'Cache-Control': 'no-cache'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  // Si la API responde fuera del rango 2xx, se informa el error completo.
  if (status < 200 || status >= 300) {
    throw new Error('Error obteniendo token. HTTP ' + status + ' - ' + body);
  }

  let json;
  try {
    json = JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta del token no es JSON válido.');
  }

  // Se valida que la respuesta incluya access_token.
  if (!json.access_token) {
    throw new Error('La respuesta no incluye access_token.');
  }

  trainingsSetCachedToken_(json.access_token);
  return json.access_token;
}

/**
 * Valida que una URL de paginación pertenezca a la instancia esperada.
 *
 * Esto agrega una capa de seguridad para evitar seguir URLs inesperadas
 * en el campo "next" de la paginación.
 */
function trainingsValidateUrl_(url) {
  if (!url.startsWith(SMARTFENSE_CONFIG.baseUrl + '/')) {
    throw new Error('La URL de paginación no pertenece a la instancia esperada: ' + url);
  }
}

/**
 * Consulta una página del endpoint y devuelve su contenido parseado como JSON.
 *
 * Recibe:
 * - url: URL completa de la página a consultar
 * - token: access token OAuth2
 *
 * También:
 * - valida que la URL sea segura,
 * - ejecuta el GET,
 * - controla errores HTTP,
 * - y asegura que la respuesta sea JSON válido.
 */
function trainingsGetPage_(url, token) {
  trainingsValidateUrl_(url);

  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + token,
      Accept: 'application/json'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status < 200 || status >= 300) {
    throw new Error('Error consultando página. HTTP ' + status + ' - ' + body);
  }

  try {
    return JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta de la API no es JSON válido.');
  }
}

/**
 * Recorre todas las páginas del endpoint de trainings y devuelve
 * todos los objetos de results en una sola lista.
 *
 * Comportamiento:
 * - obtiene el token,
 * - construye la URL inicial,
 * - sigue el campo "next" mientras exista,
 * - concatena todos los registros encontrados en "results",
 * - y corta si se supera el máximo de páginas definido en SMARTFENSE_CONFIG.maxPages.
 *
 * Devuelve un array con todos los usuarios recuperados desde la API.
 */
function trainingsGetResults_() {
  const token = trainingsGetToken_();
  let nextUrl = SMARTFENSE_CONFIG.baseUrl + TRAININGS_CONFIG.endpointPath;
  let allResults = [];
  let pageCount = 0;

  while (nextUrl) {
    pageCount++;

    if (pageCount > SMARTFENSE_CONFIG.maxPages) {
      throw new Error('Se alcanzó el máximo de páginas permitidas (' + SMARTFENSE_CONFIG.maxPages + ').');
    }

    const page = trainingsGetPage_(nextUrl, token);
    const results = Array.isArray(page.results) ? page.results : [];

    allResults = allResults.concat(results);
    nextUrl = page && page.next ? page.next : null;
  }

  return allResults;
}

/**
 * Devuelve una fila vacía con todas las columnas de campaña inicializadas como string vacío.
 *
 * Se usa cuando un usuario no tiene campañas relacionadas, para que la exportación
 * mantenga una estructura tabular consistente.
 */
function trainingsEmptyCampaignRow_() {
  return {
    campaign_id: '',
    campaign_mode: '',
    campaign_name: '',
    campaign_description: '',
    campaign_state: '',
    campaign_date: '',
    campaign_expiration_date: '',
    campaign_is_test: '',
    content_name: '',
    content_type: '',
    content_current_state: '',
    content_code: '',
    activity_sent: '',
    activity_sent_date: '',
    notify_user_on_creation: '',
    user_notification_on_creation: '',
    activity_started: '',
    activity_started_date: '',
    activity_completed: '',
    activity_completed_date: '',
    id_exam: '',
    exam_content_name: '',
    result: '',
    result_date: '',
    final_grade: '',
    dedicated_time: ''
  };
}

/**
 * Convierte la estructura anidada de la API en filas planas listas para exportar.
 *
 * Lógica de transformación:
 * - cada usuario aporta sus datos base,
 * - cada campaña relacionada genera una fila independiente,
 * - si el usuario no tiene campañas, se genera igualmente una fila
 *   con los datos del usuario y las columnas de campaña vacías.
 *
 * Resultado:
 * Una fila = un usuario + una campaña relacionada.
 */
function trainingsFlattenResults_(results) {
  const rows = [];

  results.forEach(function(user) {
    // Datos base del usuario que se repetirán en cada fila de campaña.
    const userBase = {
      first_name: trainingsValueOrEmpty_(user.first_name),
      last_name: trainingsValueOrEmpty_(user.last_name),
      email: trainingsValueOrEmpty_(user.email),
      groups: trainingsNormalizeListValue_(user.groups),
      functional_areas: trainingsNormalizeListValue_(user.functional_areas),
      hierarchical_levels: trainingsNormalizeListValue_(user.hierarchical_levels)
    };

    const campaigns = Array.isArray(user.related_campaigns) ? user.related_campaigns : [];

    // Si no hay campañas relacionadas, igual se agrega una fila con columnas vacías.
    if (campaigns.length === 0) {
      rows.push(trainingsNormalizeDateFields_(Object.assign({}, userBase, trainingsEmptyCampaignRow_())));
      return;
    }

    // Si hay campañas, cada campaña se convierte en una fila individual.
    campaigns.forEach(function(campaign) {
      const row = {
        campaign_id: trainingsValueOrEmpty_(campaign.campaign_id),
        campaign_mode: trainingsValueOrEmpty_(campaign.campaign_mode),
        campaign_name: trainingsValueOrEmpty_(campaign.campaign_name),
        campaign_description: trainingsValueOrEmpty_(campaign.campaign_description),
        campaign_state: trainingsValueOrEmpty_(campaign.campaign_state),
        campaign_date: trainingsValueOrEmpty_(campaign.campaign_date),
        campaign_expiration_date: trainingsValueOrEmpty_(campaign.campaign_expiration_date),
        campaign_is_test: trainingsValueOrEmpty_(campaign.campaign_is_test),
        content_name: trainingsValueOrEmpty_(campaign.content_name),
        content_type: trainingsValueOrEmpty_(campaign.content_type),
        content_current_state: trainingsValueOrEmpty_(campaign.content_current_state),
        content_code: trainingsValueOrEmpty_(campaign.content_code),
        activity_sent: trainingsValueOrEmpty_(campaign.activity_sent),
        activity_sent_date: trainingsValueOrEmpty_(campaign.activity_sent_date),
        notify_user_on_creation: trainingsValueOrEmpty_(campaign.notify_user_on_creation),
        user_notification_on_creation: trainingsValueOrEmpty_(campaign.user_notification_on_creation),
        activity_started: trainingsValueOrEmpty_(campaign.activity_started),
        activity_started_date: trainingsValueOrEmpty_(campaign.activity_started_date),
        activity_completed: trainingsValueOrEmpty_(campaign.activity_completed),
        activity_completed_date: trainingsValueOrEmpty_(campaign.activity_completed_date),
        id_exam: trainingsValueOrEmpty_(campaign.id_exam),
        exam_content_name: trainingsValueOrEmpty_(campaign.exam_content_name),
        result: trainingsValueOrEmpty_(campaign.result),
        result_date: trainingsValueOrEmpty_(campaign.result_date),
        final_grade: trainingsValueOrEmpty_(campaign.final_grade),
        dedicated_time: trainingsValueOrEmpty_(campaign.dedicated_time)
      };

      // Antes de guardar la fila, se normalizan los campos de fecha.
      rows.push(trainingsNormalizeDateFields_(Object.assign({}, userBase, row)));
    });
  });

  return rows;
}

/**
 * Función principal del proceso de exportación.
 *
 * Flujo:
 * 1. Define el orden de columnas.
 * 2. Obtiene la planilla activa.
 * 3. Descarga todos los datos desde la API.
 * 4. Aplana los resultados.
 * 5. Obtiene o crea la hoja TRAININGS.
 * 6. Limpia el contenido previo.
 * 7. Escribe los encabezados.
 * 8. Escribe las filas de datos.
 * 9. Aplica formato a columnas de fecha.
 *
 * Si ocurre un error, intenta dejarlo visible en la hoja
 * antes de volver a lanzarlo.
 */
function exportTrainingsToSheet() {
  const headers = [
    'first_name',
    'last_name',
    'email',
    'groups',
    'functional_areas',
    'hierarchical_levels',
    'campaign_id',
    'campaign_mode',
    'campaign_name',
    'campaign_description',
    'campaign_state',
    'campaign_date',
    'campaign_expiration_date',
    'campaign_is_test',
    'content_name',
    'content_type',
    'content_current_state',
    'content_code',
    'activity_sent',
    'activity_sent_date',
    'notify_user_on_creation',
    'user_notification_on_creation',
    'activity_started',
    'activity_started_date',
    'activity_completed',
    'activity_completed_date',
    'id_exam',
    'exam_content_name',
    'result',
    'result_date',
    'final_grade',
    'dedicated_time'
  ];

  let sheet = null;

  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    if (!ss) {
      throw new Error('No hay un spreadsheet activo. Abrí el script desde Google Sheets.');
    }

    const results = trainingsGetResults_();
    const rows = trainingsFlattenResults_(results);

    sheet = trainingsGetOrCreateSheet_(ss, TRAININGS_CONFIG.sheetName);
    sheet.clearContents();

    // Escribe la fila de encabezados.
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

    // Si hay datos, los escribe respetando el orden definido en headers.
    if (rows.length > 0) {
      const values = rows.map(function(row) {
        return headers.map(function(header) {
          return row[header];
        });
      });

      sheet.getRange(2, 1, values.length, headers.length).setValues(values);
      trainingsApplyDateFormats_(sheet, headers, values.length);
    }

  } catch (error) {
    // Si algo falla, deja una marca visible en la hoja.
    if (sheet) {
      sheet.clearContents();
      sheet.getRange('A1').setValue('ERROR');
      sheet.getRange('B1').setValue(error.message);
    }
    throw error;
  }
}

/**
 * Busca una hoja por nombre y la crea si no existe.
 */
function trainingsGetOrCreateSheet_(spreadsheet, sheetName) {
  let sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }

  return sheet;
}

/**
 * Devuelve el valor recibido o una cadena vacía si es null/undefined.
 */
function trainingsValueOrEmpty_(value) {
  if (value === null || value === undefined) return '';
  return value;
}

/**
 * Convierte arrays u objetos en texto exportable.
 *
 * - null / undefined => ''
 * - array => elementos unidos por coma
 * - objeto => JSON.stringify
 * - otros => String(value)
 */
function trainingsNormalizeListValue_(value) {
  if (value === null || value === undefined) return '';

  if (Array.isArray(value)) {
    return value
      .map(function(item) {
        if (item === null || item === undefined) return '';
        if (typeof item === 'object') return JSON.stringify(item);
        return String(item);
      })
      .filter(function(item) {
        return item !== '';
      })
      .join(', ');
  }

  if (typeof value === 'object') {
    return JSON.stringify(value);
  }

  return String(value);
}

/**
 * Recorre todos los campos configurados como fecha y los convierte.
 */
function trainingsNormalizeDateFields_(row) {
  TRAININGS_CONFIG.dateFields.forEach(function(field) {
    row[field] = trainingsParseDate_(row[field]);
  });

  return row;
}

/**
 * Intenta convertir distintos formatos de fecha a Date.
 *
 * Soporta:
 * - dd/MM/yyyy HH:mm:ss.SS
 * - dd/MM/yyyy HH:mm:ss
 * - dd/MM/yyyy HH:mm
 * - yyyy-MM-dd HH:mm:ss(.SSS)
 * - ISO
 *
 * Si no puede interpretarse como fecha válida, devuelve ''.
 */
function trainingsParseDate_(value) {
  if (value === null || value === undefined) return '';
  if (value === '') return '';

  if (Object.prototype.toString.call(value) === '[object Date]') {
    return isNaN(value.getTime()) ? '' : value;
  }

  const str = String(value).trim();
  if (str === '') return '';

  let match;

  // Formato: dd/MM/yyyy HH:mm:ss.SS o dd/MM/yyyy HH:mm:ss o dd/MM/yyyy HH:mm
  match = str.match(
    /^(\d{2})\/(\d{2})\/(\d{4})(?:\s+(\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const day = Number(match[1]);
    const month = Number(match[2]) - 1;
    const year = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Formato: yyyy-MM-dd HH:mm:ss(.SSS)
  match = str.match(
    /^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const year = Number(match[1]);
    const month = Number(match[2]) - 1;
    const day = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Último intento: interpretación nativa de JavaScript.
  const nativeDate = new Date(str);
  if (!isNaN(nativeDate.getTime())) {
    return nativeDate;
  }

  return '';
}

/**
 * Aplica formato visual a las columnas de fecha de la hoja.
 */
function trainingsApplyDateFormats_(sheet, headers, rowCount) {
  TRAININGS_CONFIG.dateFields.forEach(function(header) {
    const colIndex = headers.indexOf(header) + 1;
    if (colIndex > 0) {
      sheet
        .getRange(2, colIndex, rowCount, 1)
        .setNumberFormat(TRAININGS_CONFIG.dateNumberFormat);
    }
  });
}

Exportación de campañas de videos a Google Sheets

Objetivo

Este script consulta el endpoint de campañas de videos por usuario en la API de SMARTFENSE y exporta los resultados a una hoja de Google Sheets llamada VIDEOS.

¿Qué hace este script?

El script automatiza todo el proceso de extracción y carga de datos:

  • se autentica contra la API de SMARTFENSE,

  • consulta el endpoint correspondiente,

  • recorre todas las páginas de resultados,

  • transforma la respuesta en una estructura tabular,

  • y vuelca la información en una hoja de Google Sheets.

Resultado

Al ejecutar el script, se crea o actualiza una hoja llamada VIDEOS dentro del spreadsheet activo.

La información queda organizada en formato tabla, lista para su análisis y reutilización.

¿Para qué sirve esta hoja?

La hoja generada funciona como base de datos operativa para análisis y visualización.

Su propósito es que los datos extraídos desde la API puedan utilizarse después en:

  • dashboards de Looker Studio,

  • análisis en Google Sheets,

  • cruces con otras fuentes,

  • reportes operativos,

  • tableros de seguimiento.

En este esquema, el script cumple la función de transformar la información del endpoint en una fuente tabular lista para ser consumida por herramientas de reporting.

Fuente de datos

Endpoint consultado:

/api/v1/users/campaigns/videos

Tipo de información exportada

La hoja incluye información relacionada con:

  • datos del usuario,

  • campañas de videos asociadas,

  • estado de envío, inicio y finalización,

  • información de exámenes y resultados,

  • calificación final,

  • y fechas relevantes del proceso.

Consideraciones

  • El script utiliza autenticación OAuth2.

  • Reutiliza temporalmente el token mediante caché.

  • Recorre automáticamente la paginación del endpoint.

  • Convierte los campos de fecha para que Google Sheets los reconozca correctamente.

  • Limpia y vuelve a generar la hoja en cada ejecución.

  • Si ocurre un error, intenta dejar el detalle reflejado en la propia hoja.

Código

A continuación se incluye el script comentado para referencia técnica.

Videos.gs

// Configuración específica para la exportación de campañas de videos.
// Centraliza:
// - la ruta del endpoint a consultar,
// - el nombre de la hoja de destino,
// - la clave y duración del token en caché,
// - los campos que deben interpretarse como fecha,
// - y el formato visual de fecha en Google Sheets.
const VIDEOS_CONFIG = {
  endpointPath: '/api/v1/users/campaigns/videos',
  sheetName: 'VIDEOS',
  tokenCacheKey: 'SMARTFENSE_ACCESS_TOKEN',
  tokenCacheSeconds: 300,
  dateFields: [
    'campaign_date',
    'campaign_expiration_date',
    'activity_sent_date',
    'activity_started_date',
    'activity_completed_date',
    'result_date'
  ],
  dateNumberFormat: 'dd/MM/yyyy HH:mm:ss'
};

/**
 * Lee las credenciales OAuth2 desde Script Properties.
 *
 * Espera encontrar:
 * - SMARTFENSE_CLIENT_ID
 * - SMARTFENSE_CLIENT_SECRET
 *
 * Si alguna no existe, corta la ejecución con error.
 */
function videosGetCredentials_() {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('SMARTFENSE_CLIENT_ID');
  const clientSecret = props.getProperty('SMARTFENSE_CLIENT_SECRET');

  if (!clientId || !clientSecret) {
    throw new Error('Faltan SMARTFENSE_CLIENT_ID o SMARTFENSE_CLIENT_SECRET en Script Properties.');
  }

  return { clientId, clientSecret };
}

/**
 * Recupera un token previamente guardado en caché.
 *
 * Esto evita solicitar un token nuevo en cada ejecución
 * mientras siga vigente dentro del tiempo configurado.
 */
function videosGetCachedToken_() {
  return CacheService.getScriptCache().get(VIDEOS_CONFIG.tokenCacheKey);
}

/**
 * Guarda el token OAuth2 en la caché del script.
 *
 * El tiempo de vida del token cacheado se define en:
 * VIDEOS_CONFIG.tokenCacheSeconds
 */
function videosSetCachedToken_(token) {
  CacheService.getScriptCache().put(
    VIDEOS_CONFIG.tokenCacheKey,
    token,
    VIDEOS_CONFIG.tokenCacheSeconds
  );
}

/**
 * Obtiene un access token OAuth2 usando el flujo client_credentials.
 *
 * Flujo:
 * 1. Intenta reutilizar un token cacheado.
 * 2. Si no existe, lee credenciales desde Script Properties.
 * 3. Llama al endpoint de token configurado en SMARTFENSE_CONFIG.tokenUrl.
 * 4. Valida la respuesta HTTP.
 * 5. Parsea el JSON y extrae access_token.
 * 6. Guarda el token en caché para próximas ejecuciones.
 */
function videosGetToken_() {
  const cachedToken = videosGetCachedToken_();
  if (cachedToken) {
    return cachedToken;
  }

  const { clientId, clientSecret } = videosGetCredentials_();
  const basicAuth = Utilities.base64Encode(clientId + ':' + clientSecret);

  const response = UrlFetchApp.fetch(SMARTFENSE_CONFIG.tokenUrl, {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: 'grant_type=client_credentials',
    headers: {
      Authorization: 'Basic ' + basicAuth,
      Accept: 'application/json',
      'Cache-Control': 'no-cache'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  // Si la API responde fuera del rango 2xx, se informa el error completo.
  if (status < 200 || status >= 300) {
    throw new Error('Error obteniendo token. HTTP ' + status + ' - ' + body);
  }

  let json;
  try {
    json = JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta del token no es JSON válido.');
  }

  // Se valida que la respuesta incluya access_token.
  if (!json.access_token) {
    throw new Error('La respuesta no incluye access_token.');
  }

  videosSetCachedToken_(json.access_token);
  return json.access_token;
}

/**
 * Valida que una URL de paginación pertenezca a la instancia esperada.
 *
 * Esto agrega una capa de seguridad para evitar seguir URLs inesperadas
 * en el campo "next" de la paginación.
 */
function videosValidateUrl_(url) {
  if (!url.startsWith(SMARTFENSE_CONFIG.baseUrl + '/')) {
    throw new Error('La URL de paginación no pertenece a la instancia esperada: ' + url);
  }
}

/**
 * Consulta una página del endpoint y devuelve su contenido parseado como JSON.
 *
 * Recibe:
 * - url: URL completa de la página a consultar
 * - token: access token OAuth2
 *
 * También:
 * - valida que la URL sea segura,
 * - ejecuta el GET,
 * - controla errores HTTP,
 * - y asegura que la respuesta sea JSON válido.
 */
function videosGetPage_(url, token) {
  videosValidateUrl_(url);

  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + token,
      Accept: 'application/json'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status < 200 || status >= 300) {
    throw new Error('Error consultando página. HTTP ' + status + ' - ' + body);
  }

  try {
    return JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta de la API no es JSON válido.');
  }
}

/**
 * Recorre todas las páginas del endpoint de videos y devuelve
 * todos los objetos de results en una sola lista.
 *
 * Comportamiento:
 * - obtiene el token,
 * - construye la URL inicial,
 * - sigue el campo "next" mientras exista,
 * - concatena todos los registros encontrados en "results",
 * - y corta si se supera el máximo de páginas definido en SMARTFENSE_CONFIG.maxPages.
 *
 * Devuelve un array con todos los usuarios recuperados desde la API.
 */
function videosGetResults_() {
  const token = videosGetToken_();
  let nextUrl = SMARTFENSE_CONFIG.baseUrl + VIDEOS_CONFIG.endpointPath;
  let allResults = [];
  let pageCount = 0;

  while (nextUrl) {
    pageCount++;

    if (pageCount > SMARTFENSE_CONFIG.maxPages) {
      throw new Error('Se alcanzó el máximo de páginas permitidas (' + SMARTFENSE_CONFIG.maxPages + ').');
    }

    const page = videosGetPage_(nextUrl, token);
    const results = Array.isArray(page.results) ? page.results : [];

    allResults = allResults.concat(results);
    nextUrl = page && page.next ? page.next : null;
  }

  return allResults;
}

/**
 * Devuelve una fila vacía con todas las columnas de campaña inicializadas como string vacío.
 *
 * Se usa cuando un usuario no tiene campañas relacionadas, para que la exportación
 * mantenga una estructura tabular consistente.
 */
function videosEmptyCampaignRow_() {
  return {
    campaign_id: '',
    campaign_mode: '',
    campaign_name: '',
    campaign_description: '',
    campaign_state: '',
    campaign_date: '',
    campaign_expiration_date: '',
    campaign_is_test: '',
    content_name: '',
    content_type: '',
    content_current_state: '',
    content_code: '',
    activity_sent: '',
    activity_sent_date: '',
    activity_started: '',
    activity_started_date: '',
    activity_completed: '',
    activity_completed_date: '',
    id_exam: '',
    exam_content_name: '',
    result: '',
    result_date: '',
    final_grade: ''
  };
}

/**
 * Convierte la estructura anidada de la API en filas planas listas para exportar.
 *
 * Lógica de transformación:
 * - cada usuario aporta sus datos base,
 * - cada campaña relacionada genera una fila independiente,
 * - si el usuario no tiene campañas, se genera igualmente una fila
 *   con los datos del usuario y las columnas de campaña vacías.
 *
 * Resultado:
 * Una fila = un usuario + una campaña relacionada.
 */
function videosFlattenResults_(results) {
  const rows = [];

  results.forEach(function(user) {
    // Datos base del usuario que se repetirán en cada fila de campaña.
    const userBase = {
      first_name: videosValueOrEmpty_(user.first_name),
      last_name: videosValueOrEmpty_(user.last_name),
      email: videosValueOrEmpty_(user.email),
      groups: videosNormalizeListValue_(user.groups),
      functional_areas: videosNormalizeListValue_(user.functional_areas),
      hierarchical_levels: videosNormalizeListValue_(user.hierarchical_levels)
    };

    const campaigns = Array.isArray(user.related_campaigns) ? user.related_campaigns : [];

    // Si no hay campañas relacionadas, igual se agrega una fila con columnas vacías.
    if (campaigns.length === 0) {
      rows.push(videosNormalizeDateFields_(Object.assign({}, userBase, videosEmptyCampaignRow_())));
      return;
    }

    // Si hay campañas, cada campaña se convierte en una fila individual.
    campaigns.forEach(function(campaign) {
      const row = {
        campaign_id: videosValueOrEmpty_(campaign.campaign_id),
        campaign_mode: videosValueOrEmpty_(campaign.campaign_mode),
        campaign_name: videosValueOrEmpty_(campaign.campaign_name),
        campaign_description: videosValueOrEmpty_(campaign.campaign_description),
        campaign_state: videosValueOrEmpty_(campaign.campaign_state),
        campaign_date: videosValueOrEmpty_(campaign.campaign_date),
        campaign_expiration_date: videosValueOrEmpty_(campaign.campaign_expiration_date),
        campaign_is_test: videosValueOrEmpty_(campaign.campaign_is_test),
        content_name: videosValueOrEmpty_(campaign.content_name),
        content_type: videosValueOrEmpty_(campaign.content_type),
        content_current_state: videosValueOrEmpty_(campaign.content_current_state),
        content_code: videosValueOrEmpty_(campaign.content_code),
        activity_sent: videosValueOrEmpty_(campaign.activity_sent),
        activity_sent_date: videosValueOrEmpty_(campaign.activity_sent_date),
        activity_started: videosValueOrEmpty_(campaign.activity_started),
        activity_started_date: videosValueOrEmpty_(campaign.activity_started_date),
        activity_completed: videosValueOrEmpty_(campaign.activity_completed),
        activity_completed_date: videosValueOrEmpty_(campaign.activity_completed_date),
        id_exam: videosValueOrEmpty_(campaign.id_exam),
        exam_content_name: videosValueOrEmpty_(campaign.exam_content_name),
        result: videosValueOrEmpty_(campaign.result),
        result_date: videosValueOrEmpty_(campaign.result_date),
        final_grade: videosValueOrEmpty_(campaign.final_grade)
      };

      // Antes de guardar la fila, se normalizan los campos de fecha.
      rows.push(videosNormalizeDateFields_(Object.assign({}, userBase, row)));
    });
  });

  return rows;
}

/**
 * Función principal del proceso de exportación.
 *
 * Flujo:
 * 1. Define el orden de columnas.
 * 2. Obtiene la planilla activa.
 * 3. Descarga todos los datos desde la API.
 * 4. Aplana los resultados.
 * 5. Obtiene o crea la hoja VIDEOS.
 * 6. Limpia el contenido previo.
 * 7. Escribe los encabezados.
 * 8. Escribe las filas de datos.
 * 9. Aplica formato a columnas de fecha.
 *
 * Si ocurre un error, intenta dejarlo visible en la hoja
 * antes de volver a lanzarlo.
 */
function exportVideosToSheet() {
  const headers = [
    'first_name',
    'last_name',
    'email',
    'groups',
    'functional_areas',
    'hierarchical_levels',
    'campaign_id',
    'campaign_mode',
    'campaign_name',
    'campaign_description',
    'campaign_state',
    'campaign_date',
    'campaign_expiration_date',
    'campaign_is_test',
    'content_name',
    'content_type',
    'content_current_state',
    'content_code',
    'activity_sent',
    'activity_sent_date',
    'activity_started',
    'activity_started_date',
    'activity_completed',
    'activity_completed_date',
    'id_exam',
    'exam_content_name',
    'result',
    'result_date',
    'final_grade'
  ];

  let sheet = null;

  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    if (!ss) {
      throw new Error('No hay un spreadsheet activo. Abrí el script desde Google Sheets.');
    }

    const results = videosGetResults_();
    const rows = videosFlattenResults_(results);

    sheet = videosGetOrCreateSheet_(ss, VIDEOS_CONFIG.sheetName);
    sheet.clearContents();

    // Escribe la fila de encabezados.
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

    // Si hay datos, los escribe respetando el orden definido en headers.
    if (rows.length > 0) {
      const values = rows.map(function(row) {
        return headers.map(function(header) {
          return row[header];
        });
      });

      sheet.getRange(2, 1, values.length, headers.length).setValues(values);
      videosApplyDateFormats_(sheet, headers, values.length);
    }

  } catch (error) {
    // Si algo falla, deja una marca visible en la hoja.
    if (sheet) {
      sheet.clearContents();
      sheet.getRange('A1').setValue('ERROR');
      sheet.getRange('B1').setValue(error.message);
    }
    throw error;
  }
}

/**
 * Busca una hoja por nombre y la crea si no existe.
 */
function videosGetOrCreateSheet_(spreadsheet, sheetName) {
  let sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }

  return sheet;
}

/**
 * Devuelve el valor recibido o una cadena vacía si es null/undefined.
 */
function videosValueOrEmpty_(value) {
  if (value === null || value === undefined) return '';
  return value;
}

/**
 * Convierte arrays u objetos en texto exportable.
 *
 * - null / undefined => ''
 * - array => elementos unidos por coma
 * - objeto => JSON.stringify
 * - otros => String(value)
 */
function videosNormalizeListValue_(value) {
  if (value === null || value === undefined) return '';

  if (Array.isArray(value)) {
    return value
      .map(function(item) {
        if (item === null || item === undefined) return '';
        if (typeof item === 'object') return JSON.stringify(item);
        return String(item);
      })
      .filter(function(item) {
        return item !== '';
      })
      .join(', ');
  }

  if (typeof value === 'object') {
    return JSON.stringify(value);
  }

  return String(value);
}

/**
 * Recorre todos los campos configurados como fecha y los convierte.
 */
function videosNormalizeDateFields_(row) {
  VIDEOS_CONFIG.dateFields.forEach(function(field) {
    row[field] = videosParseDate_(row[field]);
  });

  return row;
}

/**
 * Intenta convertir distintos formatos de fecha a Date.
 *
 * Soporta:
 * - dd/MM/yyyy HH:mm:ss.SS
 * - dd/MM/yyyy HH:mm:ss
 * - dd/MM/yyyy HH:mm
 * - yyyy-MM-dd HH:mm:ss(.SSS)
 * - ISO
 *
 * Si no puede interpretarse como fecha válida, devuelve ''.
 */
function videosParseDate_(value) {
  if (value === null || value === undefined) return '';
  if (value === '') return '';

  if (Object.prototype.toString.call(value) === '[object Date]') {
    return isNaN(value.getTime()) ? '' : value;
  }

  const str = String(value).trim();
  if (str === '') return '';

  let match;

  // Formato: dd/MM/yyyy HH:mm:ss.SS o dd/MM/yyyy HH:mm:ss o dd/MM/yyyy HH:mm
  match = str.match(
    /^(\d{2})\/(\d{2})\/(\d{4})(?:\s+(\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const day = Number(match[1]);
    const month = Number(match[2]) - 1;
    const year = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Formato: yyyy-MM-dd HH:mm:ss(.SSS)
  match = str.match(
    /^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const year = Number(match[1]);
    const month = Number(match[2]) - 1;
    const day = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Último intento: interpretación nativa de JavaScript.
  const nativeDate = new Date(str);
  if (!isNaN(nativeDate.getTime())) {
    return nativeDate;
  }

  return '';
}

/**
 * Aplica formato visual a las columnas de fecha de la hoja.
 */
function videosApplyDateFormats_(sheet, headers, rowCount) {
  VIDEOS_CONFIG.dateFields.forEach(function(header) {
    const colIndex = headers.indexOf(header) + 1;
    if (colIndex > 0) {
      sheet
        .getRange(2, colIndex, rowCount, 1)
        .setNumberFormat(VIDEOS_CONFIG.dateNumberFormat);
    }
  });
}

Exportación de campañas de videogames a Google Sheets

Objetivo

Este script consulta el endpoint de campañas de videogames por usuario en la API de SMARTFENSE y exporta los resultados a una hoja de Google Sheets llamada VIDEOGAMES.

¿Qué hace este script?

El script automatiza todo el proceso de extracción y carga de datos:

  • se autentica contra la API de SMARTFENSE,

  • consulta el endpoint correspondiente,

  • recorre todas las páginas de resultados,

  • transforma la respuesta en una estructura tabular,

  • y vuelca la información en una hoja de Google Sheets.

Resultado

Al ejecutar el script, se crea o actualiza una hoja llamada VIDEOGAMES dentro del spreadsheet activo.

La información queda organizada en formato tabla, lista para su análisis y reutilización.

¿Para qué sirve esta hoja?

La hoja generada funciona como base de datos operativa para análisis y visualización.

Su propósito es que los datos extraídos desde la API puedan utilizarse después en:

  • dashboards de Looker Studio,

  • análisis en Google Sheets,

  • cruces con otras fuentes,

  • reportes operativos,

  • tableros de seguimiento.

En este esquema, el script cumple la función de transformar la información del endpoint en una fuente tabular lista para ser consumida por herramientas de reporting.

Fuente de datos

Endpoint consultado:

/api/v1/users/campaigns/videogames

Tipo de información exportada

La hoja incluye información relacionada con:

  • datos del usuario,

  • campañas de videogames asociadas,

  • estado de envío, inicio y finalización,

  • configuración de notificación al usuario,

  • y fechas relevantes del proceso.

Consideraciones

  • El script utiliza autenticación OAuth2.

  • Reutiliza temporalmente el token mediante caché.

  • Recorre automáticamente la paginación del endpoint.

  • Convierte los campos de fecha para que Google Sheets los reconozca correctamente.

  • Limpia y vuelve a generar la hoja en cada ejecución.

  • Si ocurre un error, intenta dejar el detalle reflejado en la propia hoja.

Código

A continuación se incluye el script comentado para referencia técnica.

Videogames.gs

// Configuración específica para la exportación de campañas de videogames.
// Centraliza:
// - la ruta del endpoint a consultar,
// - el nombre de la hoja de destino,
// - la clave y duración del token en caché,
// - los campos que deben interpretarse como fecha,
// - y el formato visual de fecha en Google Sheets.
const VIDEOGAMES_CONFIG = {
  endpointPath: '/api/v1/users/campaigns/videogames',
  sheetName: 'VIDEOGAMES',
  tokenCacheKey: 'SMARTFENSE_ACCESS_TOKEN',
  tokenCacheSeconds: 300,
  dateFields: [
    'campaign_date',
    'campaign_expiration_date',
    'activity_sent_date',
    'activity_started_date',
    'activity_completed_date'
  ],
  dateNumberFormat: 'dd/MM/yyyy HH:mm:ss'
};

/**
 * Lee las credenciales OAuth2 desde Script Properties.
 *
 * Espera encontrar:
 * - SMARTFENSE_CLIENT_ID
 * - SMARTFENSE_CLIENT_SECRET
 *
 * Si alguna no existe, corta la ejecución con error.
 */
function videogamesGetCredentials_() {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('SMARTFENSE_CLIENT_ID');
  const clientSecret = props.getProperty('SMARTFENSE_CLIENT_SECRET');

  if (!clientId || !clientSecret) {
    throw new Error('Faltan SMARTFENSE_CLIENT_ID o SMARTFENSE_CLIENT_SECRET en Script Properties.');
  }

  return { clientId, clientSecret };
}

/**
 * Recupera un token previamente guardado en caché.
 *
 * Esto evita solicitar un token nuevo en cada ejecución
 * mientras siga vigente dentro del tiempo configurado.
 */
function videogamesGetCachedToken_() {
  return CacheService.getScriptCache().get(VIDEOGAMES_CONFIG.tokenCacheKey);
}

/**
 * Guarda el token OAuth2 en la caché del script.
 *
 * El tiempo de vida del token cacheado se define en:
 * VIDEOGAMES_CONFIG.tokenCacheSeconds
 */
function videogamesSetCachedToken_(token) {
  CacheService.getScriptCache().put(
    VIDEOGAMES_CONFIG.tokenCacheKey,
    token,
    VIDEOGAMES_CONFIG.tokenCacheSeconds
  );
}

/**
 * Obtiene un access token OAuth2 usando el flujo client_credentials.
 *
 * Flujo:
 * 1. Intenta reutilizar un token cacheado.
 * 2. Si no existe, lee credenciales desde Script Properties.
 * 3. Llama al endpoint de token configurado en SMARTFENSE_CONFIG.tokenUrl.
 * 4. Valida la respuesta HTTP.
 * 5. Parsea el JSON y extrae access_token.
 * 6. Guarda el token en caché para próximas ejecuciones.
 */
function videogamesGetToken_() {
  const cachedToken = videogamesGetCachedToken_();
  if (cachedToken) {
    return cachedToken;
  }

  const { clientId, clientSecret } = videogamesGetCredentials_();
  const basicAuth = Utilities.base64Encode(clientId + ':' + clientSecret);

  const response = UrlFetchApp.fetch(SMARTFENSE_CONFIG.tokenUrl, {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: 'grant_type=client_credentials',
    headers: {
      Authorization: 'Basic ' + basicAuth,
      Accept: 'application/json',
      'Cache-Control': 'no-cache'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  // Si la API responde fuera del rango 2xx, se informa el error completo.
  if (status < 200 || status >= 300) {
    throw new Error('Error obteniendo token. HTTP ' + status + ' - ' + body);
  }

  let json;
  try {
    json = JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta del token no es JSON válido.');
  }

  // Se valida que la respuesta incluya access_token.
  if (!json.access_token) {
    throw new Error('La respuesta no incluye access_token.');
  }

  videogamesSetCachedToken_(json.access_token);
  return json.access_token;
}

/**
 * Valida que una URL de paginación pertenezca a la misma instancia.
 *
 * Esto agrega una capa de seguridad para evitar seguir URLs inesperadas
 * en el campo "next" de la paginación.
 */
function videogamesValidateUrl_(url) {
  if (!url.startsWith(SMARTFENSE_CONFIG.baseUrl + '/')) {
    throw new Error('La URL de paginación no pertenece a la instancia esperada: ' + url);
  }
}

/**
 * Consulta una página del endpoint y devuelve su contenido parseado como JSON.
 *
 * Recibe:
 * - url: URL completa de la página a consultar
 * - token: access token OAuth2
 *
 * También:
 * - valida que la URL sea segura,
 * - ejecuta el GET,
 * - controla errores HTTP,
 * - y asegura que la respuesta sea JSON válido.
 */
function videogamesGetPage_(url, token) {
  videogamesValidateUrl_(url);

  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + token,
      Accept: 'application/json'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status < 200 || status >= 300) {
    throw new Error('Error consultando página. HTTP ' + status + ' - ' + body);
  }

  try {
    return JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta de la API no es JSON válido.');
  }
}

/**
 * Recorre todas las páginas del endpoint de videogames y devuelve
 * todos los objetos de results en una sola lista.
 *
 * Comportamiento:
 * - obtiene el token,
 * - construye la URL inicial,
 * - sigue el campo "next" mientras exista,
 * - concatena todos los registros encontrados en "results",
 * - y corta si se supera el máximo de páginas definido en SMARTFENSE_CONFIG.maxPages.
 *
 * Devuelve un array con todos los usuarios recuperados desde la API.
 */
function videogamesGetResults_() {
  const token = videogamesGetToken_();
  let nextUrl = SMARTFENSE_CONFIG.baseUrl + VIDEOGAMES_CONFIG.endpointPath;
  let allResults = [];
  let pageCount = 0;

  while (nextUrl) {
    pageCount++;

    if (pageCount > SMARTFENSE_CONFIG.maxPages) {
      throw new Error('Se alcanzó el máximo de páginas permitidas (' + SMARTFENSE_CONFIG.maxPages + ').');
    }

    const page = videogamesGetPage_(nextUrl, token);
    const results = Array.isArray(page.results) ? page.results : [];

    allResults = allResults.concat(results);
    nextUrl = page && page.next ? page.next : null;
  }

  return allResults;
}

/**
 * Devuelve una fila vacía con todas las columnas de campaña inicializadas como string vacío.
 *
 * Se usa cuando un usuario no tiene campañas relacionadas, para que la exportación
 * mantenga una estructura tabular consistente.
 */
function videogamesEmptyCampaignRow_() {
  return {
    campaign_id: '',
    campaign_mode: '',
    campaign_name: '',
    campaign_description: '',
    campaign_state: '',
    campaign_date: '',
    campaign_expiration_date: '',
    campaign_is_test: '',
    content_name: '',
    content_type: '',
    content_current_state: '',
    content_code: '',
    activity_sent: '',
    activity_sent_date: '',
    notify_user_on_creation: '',
    user_notification_on_creation: '',
    activity_started: '',
    activity_started_date: '',
    activity_completed: '',
    activity_completed_date: ''
  };
}

/**
 * Convierte la estructura anidada de la API en filas planas listas para exportar.
 *
 * Lógica de transformación:
 * - cada usuario aporta sus datos base,
 * - cada campaña relacionada genera una fila independiente,
 * - si el usuario no tiene campañas, se genera igualmente una fila
 *   con los datos del usuario y las columnas de campaña vacías.
 *
 * Resultado:
 * Una fila = un usuario + una campaña relacionada.
 */
function videogamesFlattenResults_(results) {
  const rows = [];

  results.forEach(function(user) {
    // Datos base del usuario que se repetirán en cada fila de campaña.
    const userBase = {
      first_name: videogamesValueOrEmpty_(user.first_name),
      last_name: videogamesValueOrEmpty_(user.last_name),
      email: videogamesValueOrEmpty_(user.email),
      groups: videogamesNormalizeListValue_(user.groups),
      functional_areas: videogamesNormalizeListValue_(user.functional_areas),
      hierarchical_levels: videogamesNormalizeListValue_(user.hierarchical_levels)
    };

    const campaigns = Array.isArray(user.related_campaigns) ? user.related_campaigns : [];

    // Si no hay campañas relacionadas, igual se agrega una fila con columnas vacías.
    if (campaigns.length === 0) {
      rows.push(videogamesNormalizeDateFields_(Object.assign({}, userBase, videogamesEmptyCampaignRow_())));
      return;
    }

    // Si hay campañas, cada campaña se convierte en una fila individual.
    campaigns.forEach(function(campaign) {
      const row = {
        campaign_id: videogamesValueOrEmpty_(campaign.campaign_id),
        campaign_mode: videogamesValueOrEmpty_(campaign.campaign_mode),
        campaign_name: videogamesValueOrEmpty_(campaign.campaign_name),
        campaign_description: videogamesValueOrEmpty_(campaign.campaign_description),
        campaign_state: videogamesValueOrEmpty_(campaign.campaign_state),
        campaign_date: videogamesValueOrEmpty_(campaign.campaign_date),
        campaign_expiration_date: videogamesValueOrEmpty_(campaign.campaign_expiration_date),
        campaign_is_test: videogamesValueOrEmpty_(campaign.campaign_is_test),
        content_name: videogamesValueOrEmpty_(campaign.content_name),
        content_type: videogamesValueOrEmpty_(campaign.content_type),
        content_current_state: videogamesValueOrEmpty_(campaign.content_current_state),
        content_code: videogamesValueOrEmpty_(campaign.content_code),
        activity_sent: videogamesValueOrEmpty_(campaign.activity_sent),
        activity_sent_date: videogamesValueOrEmpty_(campaign.activity_sent_date),
        notify_user_on_creation: videogamesValueOrEmpty_(campaign.notify_user_on_creation),
        user_notification_on_creation: videogamesValueOrEmpty_(campaign.user_notification_on_creation),
        activity_started: videogamesValueOrEmpty_(campaign.activity_started),
        activity_started_date: videogamesValueOrEmpty_(campaign.activity_started_date),
        activity_completed: videogamesValueOrEmpty_(campaign.activity_completed),
        activity_completed_date: videogamesValueOrEmpty_(campaign.activity_completed_date)
      };

      // Antes de guardar la fila, se normalizan los campos de fecha.
      rows.push(videogamesNormalizeDateFields_(Object.assign({}, userBase, row)));
    });
  });

  return rows;
}

/**
 * Función principal del proceso de exportación.
 *
 * Flujo:
 * 1. Define el orden de columnas.
 * 2. Obtiene la planilla activa.
 * 3. Descarga todos los datos desde la API.
 * 4. Aplana los resultados.
 * 5. Obtiene o crea la hoja VIDEOGAMES.
 * 6. Limpia el contenido previo.
 * 7. Escribe los encabezados.
 * 8. Escribe las filas de datos.
 * 9. Aplica formato a columnas de fecha.
 *
 * Si ocurre un error, intenta dejarlo visible en la hoja
 * antes de volver a lanzarlo.
 */
function exportVideogamesToSheet() {
  const headers = [
    'first_name',
    'last_name',
    'email',
    'groups',
    'functional_areas',
    'hierarchical_levels',
    'campaign_id',
    'campaign_mode',
    'campaign_name',
    'campaign_description',
    'campaign_state',
    'campaign_date',
    'campaign_expiration_date',
    'campaign_is_test',
    'content_name',
    'content_type',
    'content_current_state',
    'content_code',
    'activity_sent',
    'activity_sent_date',
    'notify_user_on_creation',
    'user_notification_on_creation',
    'activity_started',
    'activity_started_date',
    'activity_completed',
    'activity_completed_date'
  ];

  let sheet = null;

  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    if (!ss) {
      throw new Error('No hay un spreadsheet activo. Abrí el script desde Google Sheets.');
    }

    const results = videogamesGetResults_();
    const rows = videogamesFlattenResults_(results);

    sheet = videogamesGetOrCreateSheet_(ss, VIDEOGAMES_CONFIG.sheetName);
    sheet.clearContents();

    // Escribe la fila de encabezados.
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

    // Si hay datos, los escribe respetando el orden definido en headers.
    if (rows.length > 0) {
      const values = rows.map(function(row) {
        return headers.map(function(header) {
          return row[header];
        });
      });

      sheet.getRange(2, 1, values.length, headers.length).setValues(values);
      videogamesApplyDateFormats_(sheet, headers, values.length);
    }

  } catch (error) {
    // Si algo falla, deja una marca visible en la hoja.
    if (sheet) {
      sheet.clearContents();
      sheet.getRange('A1').setValue('ERROR');
      sheet.getRange('B1').setValue(error.message);
    }
    throw error;
  }
}

/**
 * Busca una hoja por nombre y la crea si no existe.
 */
function videogamesGetOrCreateSheet_(spreadsheet, sheetName) {
  let sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }

  return sheet;
}

/**
 * Devuelve el valor recibido o una cadena vacía si es null/undefined.
 */
function videogamesValueOrEmpty_(value) {
  if (value === null || value === undefined) return '';
  return value;
}

/**
 * Convierte arrays u objetos en texto exportable.
 *
 * - null / undefined => ''
 * - array => elementos unidos por coma
 * - objeto => JSON.stringify
 * - otros => String(value)
 */
function videogamesNormalizeListValue_(value) {
  if (value === null || value === undefined) return '';

  if (Array.isArray(value)) {
    return value
      .map(function(item) {
        if (item === null || item === undefined) return '';
        if (typeof item === 'object') return JSON.stringify(item);
        return String(item);
      })
      .filter(function(item) {
        return item !== '';
      })
      .join(', ');
  }

  if (typeof value === 'object') {
    return JSON.stringify(value);
  }

  return String(value);
}

/**
 * Recorre todos los campos configurados como fecha y los convierte.
 */
function videogamesNormalizeDateFields_(row) {
  VIDEOGAMES_CONFIG.dateFields.forEach(function(field) {
    row[field] = videogamesParseDate_(row[field]);
  });

  return row;
}

/**
 * Intenta convertir distintos formatos de fecha a Date.
 *
 * Soporta:
 * - dd/MM/yyyy HH:mm:ss.SS
 * - dd/MM/yyyy HH:mm:ss
 * - dd/MM/yyyy HH:mm
 * - yyyy-MM-dd HH:mm:ss(.SSS)
 * - ISO
 *
 * Si no puede interpretarse como fecha válida, devuelve ''.
 */
function videogamesParseDate_(value) {
  if (value === null || value === undefined) return '';
  if (value === '') return '';

  if (Object.prototype.toString.call(value) === '[object Date]') {
    return isNaN(value.getTime()) ? '' : value;
  }

  const str = String(value).trim();
  if (str === '') return '';

  let match;

  // Formato: dd/MM/yyyy HH:mm:ss.SS o dd/MM/yyyy HH:mm:ss o dd/MM/yyyy HH:mm
  match = str.match(
    /^(\d{2})\/(\d{2})\/(\d{4})(?:\s+(\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const day = Number(match[1]);
    const month = Number(match[2]) - 1;
    const year = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Formato: yyyy-MM-dd HH:mm:ss(.SSS)
  match = str.match(
    /^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const year = Number(match[1]);
    const month = Number(match[2]) - 1;
    const day = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Último intento: interpretación nativa de JavaScript.
  const nativeDate = new Date(str);
  if (!isNaN(nativeDate.getTime())) {
    return nativeDate;
  }

  return '';
}

/**
 * Aplica formato visual a las columnas de fecha de la hoja.
 */
function videogamesApplyDateFormats_(sheet, headers, rowCount) {
  VIDEOGAMES_CONFIG.dateFields.forEach(function(header) {
    const colIndex = headers.indexOf(header) + 1;
    if (colIndex > 0) {
      sheet
        .getRange(2, colIndex, rowCount, 1)
        .setNumberFormat(VIDEOGAMES_CONFIG.dateNumberFormat);
    }
  });
}

Exportación de campañas de newsletters a Google Sheets

Objetivo

Este script consulta el endpoint de campañas de newsletters por usuario en la API de SMARTFENSE y exporta los resultados a una hoja de Google Sheets llamada NEWSLETTERS.

¿Qué hace este script?

El script automatiza todo el proceso de extracción y carga de datos:

  • se autentica contra la API de SMARTFENSE,

  • consulta el endpoint correspondiente,

  • recorre todas las páginas de resultados,

  • transforma la respuesta en una estructura tabular,

  • y vuelca la información en una hoja de Google Sheets.

Resultado

Al ejecutar el script, se crea o actualiza una hoja llamada NEWSLETTERS dentro del spreadsheet activo.

La información queda organizada en formato tabla, lista para su análisis y reutilización.

¿Para qué sirve esta hoja?

La hoja generada funciona como base de datos operativa para análisis y visualización.

Su propósito es que los datos extraídos desde la API puedan utilizarse después en:

  • dashboards de Looker Studio,

  • análisis en Google Sheets,

  • cruces con otras fuentes,

  • reportes operativos,

  • tableros de seguimiento.

En este esquema, el script cumple la función de transformar la información del endpoint en una fuente tabular lista para ser consumida por herramientas de reporting.

Fuente de datos

Endpoint consultado:

/api/v1/users/campaigns/newsletters

Tipo de información exportada

La hoja incluye información relacionada con:

  • datos del usuario,

  • campañas de newsletters asociadas,

  • estado de envío y apertura,

  • respuestas correctas e incorrectas,

  • y fechas relevantes del proceso.

Consideraciones

  • El script utiliza autenticación OAuth2.

  • Reutiliza temporalmente el token mediante caché.

  • Recorre automáticamente la paginación del endpoint.

  • Convierte los campos de fecha para que Google Sheets los reconozca correctamente.

  • Limpia y vuelve a generar la hoja en cada ejecución.

  • Si ocurre un error, intenta dejar el detalle reflejado en la propia hoja.

Código

A continuación se incluye el script comentado para referencia técnica.

Newsletters.gs

// Configuración específica para la exportación de campañas de newsletters.
// Centraliza:
// - la ruta del endpoint a consultar,
// - el nombre de la hoja de destino,
// - la clave y duración del token en caché,
// - los campos que deben interpretarse como fecha,
// - y el formato visual de fecha en Google Sheets.
const NEWSLETTERS_CONFIG = {
  endpointPath: '/api/v1/users/campaigns/newsletters',
  sheetName: 'NEWSLETTERS',
  tokenCacheKey: 'SMARTFENSE_ACCESS_TOKEN',
  tokenCacheSeconds: 300,
  dateFields: [
    'campaign_date',
    'campaign_expiration_date',
    'activity_sent_date',
    'activity_open_date',
    'correct_answer_date',
    'incorrect_answer_date'
  ],
  dateNumberFormat: 'dd/MM/yyyy HH:mm:ss'
};

/**
 * Lee las credenciales OAuth2 desde Script Properties.
 *
 * Espera encontrar:
 * - SMARTFENSE_CLIENT_ID
 * - SMARTFENSE_CLIENT_SECRET
 *
 * Si alguna no existe, corta la ejecución con error.
 */
function newslettersGetCredentials_() {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('SMARTFENSE_CLIENT_ID');
  const clientSecret = props.getProperty('SMARTFENSE_CLIENT_SECRET');

  if (!clientId || !clientSecret) {
    throw new Error('Faltan SMARTFENSE_CLIENT_ID o SMARTFENSE_CLIENT_SECRET en Script Properties.');
  }

  return { clientId, clientSecret };
}

/**
 * Recupera un token previamente guardado en caché.
 *
 * Esto evita solicitar un token nuevo en cada ejecución
 * mientras siga vigente dentro del tiempo configurado.
 */
function newslettersGetCachedToken_() {
  return CacheService.getScriptCache().get(NEWSLETTERS_CONFIG.tokenCacheKey);
}

/**
 * Guarda el token OAuth2 en la caché del script.
 *
 * El tiempo de vida del token cacheado se define en:
 * NEWSLETTERS_CONFIG.tokenCacheSeconds
 */
function newslettersSetCachedToken_(token) {
  CacheService.getScriptCache().put(
    NEWSLETTERS_CONFIG.tokenCacheKey,
    token,
    NEWSLETTERS_CONFIG.tokenCacheSeconds
  );
}

/**
 * Obtiene un access token OAuth2 usando el flujo client_credentials.
 *
 * Flujo:
 * 1. Intenta reutilizar un token cacheado.
 * 2. Si no existe, lee credenciales desde Script Properties.
 * 3. Llama al endpoint de token configurado en SMARTFENSE_CONFIG.tokenUrl.
 * 4. Valida la respuesta HTTP.
 * 5. Parsea el JSON y extrae access_token.
 * 6. Guarda el token en caché para próximas ejecuciones.
 */
function newslettersGetToken_() {
  const cachedToken = newslettersGetCachedToken_();
  if (cachedToken) {
    return cachedToken;
  }

  const { clientId, clientSecret } = newslettersGetCredentials_();
  const basicAuth = Utilities.base64Encode(clientId + ':' + clientSecret);

  const response = UrlFetchApp.fetch(SMARTFENSE_CONFIG.tokenUrl, {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: 'grant_type=client_credentials',
    headers: {
      Authorization: 'Basic ' + basicAuth,
      Accept: 'application/json',
      'Cache-Control': 'no-cache'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  // Si la API responde fuera del rango 2xx, se informa el error completo.
  if (status < 200 || status >= 300) {
    throw new Error('Error obteniendo token. HTTP ' + status + ' - ' + body);
  }

  let json;
  try {
    json = JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta del token no es JSON válido.');
  }

  // Se valida que la respuesta incluya access_token.
  if (!json.access_token) {
    throw new Error('La respuesta no incluye access_token.');
  }

  newslettersSetCachedToken_(json.access_token);
  return json.access_token;
}

/**
 * Valida que una URL de paginación pertenezca a la misma instancia.
 *
 * Esto agrega una capa de seguridad para evitar seguir URLs inesperadas
 * en el campo "next" de la paginación.
 */
function newslettersValidateUrl_(url) {
  if (!url.startsWith(SMARTFENSE_CONFIG.baseUrl + '/')) {
    throw new Error('La URL de paginación no pertenece a la instancia esperada: ' + url);
  }
}

/**
 * Consulta una página del endpoint y devuelve su contenido parseado como JSON.
 *
 * Recibe:
 * - url: URL completa de la página a consultar
 * - token: access token OAuth2
 *
 * También:
 * - valida que la URL sea segura,
 * - ejecuta el GET,
 * - controla errores HTTP,
 * - y asegura que la respuesta sea JSON válido.
 */
function newslettersGetPage_(url, token) {
  newslettersValidateUrl_(url);

  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + token,
      Accept: 'application/json'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status < 200 || status >= 300) {
    throw new Error('Error consultando página. HTTP ' + status + ' - ' + body);
  }

  try {
    return JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta de la API no es JSON válido.');
  }
}

/**
 * Recorre todas las páginas del endpoint de newsletters y devuelve
 * todos los objetos de results en una sola lista.
 *
 * Comportamiento:
 * - obtiene el token,
 * - construye la URL inicial,
 * - sigue el campo "next" mientras exista,
 * - concatena todos los registros encontrados en "results",
 * - y corta si se supera el máximo de páginas definido en SMARTFENSE_CONFIG.maxPages.
 *
 * Devuelve un array con todos los usuarios recuperados desde la API.
 */
function newslettersGetResults_() {
  const token = newslettersGetToken_();
  let nextUrl = SMARTFENSE_CONFIG.baseUrl + NEWSLETTERS_CONFIG.endpointPath;
  let allResults = [];
  let pageCount = 0;

  while (nextUrl) {
    pageCount++;

    if (pageCount > SMARTFENSE_CONFIG.maxPages) {
      throw new Error('Se alcanzó el máximo de páginas permitidas (' + SMARTFENSE_CONFIG.maxPages + ').');
    }

    const page = newslettersGetPage_(nextUrl, token);
    const results = Array.isArray(page.results) ? page.results : [];

    allResults = allResults.concat(results);
    nextUrl = page && page.next ? page.next : null;
  }

  return allResults;
}

/**
 * Devuelve una fila vacía con todas las columnas de campaña inicializadas como string vacío.
 *
 * Se usa cuando un usuario no tiene campañas relacionadas, para que la exportación
 * mantenga una estructura tabular consistente.
 */
function newslettersEmptyCampaignRow_() {
  return {
    campaign_id: '',
    campaign_mode: '',
    campaign_name: '',
    campaign_description: '',
    campaign_state: '',
    campaign_date: '',
    campaign_expiration_date: '',
    campaign_is_test: '',
    content_name: '',
    content_type: '',
    content_current_state: '',
    content_code: '',
    activity_sent: '',
    activity_sent_date: '',
    activity_open: '',
    activity_open_date: '',
    correct_answer: '',
    correct_answer_date: '',
    incorrect_answer: '',
    incorrect_answer_date: ''
  };
}

/**
 * Convierte la estructura anidada de la API en filas planas listas para exportar.
 *
 * Lógica de transformación:
 * - cada usuario aporta sus datos base,
 * - cada campaña relacionada genera una fila independiente,
 * - si el usuario no tiene campañas, se genera igualmente una fila
 *   con los datos del usuario y las columnas de campaña vacías.
 *
 * Resultado:
 * Una fila = un usuario + una campaña relacionada.
 */
function newslettersFlattenResults_(results) {
  const rows = [];

  results.forEach(function(user) {
    // Datos base del usuario que se repetirán en cada fila de campaña.
    const userBase = {
      first_name: newslettersValueOrEmpty_(user.first_name),
      last_name: newslettersValueOrEmpty_(user.last_name),
      email: newslettersValueOrEmpty_(user.email),
      groups: newslettersNormalizeListValue_(user.groups),
      functional_areas: newslettersNormalizeListValue_(user.functional_areas),
      hierarchical_levels: newslettersNormalizeListValue_(user.hierarchical_levels)
    };

    const campaigns = Array.isArray(user.related_campaigns) ? user.related_campaigns : [];

    // Si no hay campañas relacionadas, igual se agrega una fila con columnas vacías.
    if (campaigns.length === 0) {
      rows.push(newslettersNormalizeDateFields_(Object.assign({}, userBase, newslettersEmptyCampaignRow_())));
      return;
    }

    // Si hay campañas, cada campaña se convierte en una fila individual.
    campaigns.forEach(function(campaign) {
      const row = {
        campaign_id: newslettersValueOrEmpty_(campaign.campaign_id),
        campaign_mode: newslettersValueOrEmpty_(campaign.campaign_mode),
        campaign_name: newslettersValueOrEmpty_(campaign.campaign_name),
        campaign_description: newslettersValueOrEmpty_(campaign.campaign_description),
        campaign_state: newslettersValueOrEmpty_(campaign.campaign_state),
        campaign_date: newslettersValueOrEmpty_(campaign.campaign_date),
        campaign_expiration_date: newslettersValueOrEmpty_(campaign.campaign_expiration_date),
        campaign_is_test: newslettersValueOrEmpty_(campaign.campaign_is_test),
        content_name: newslettersValueOrEmpty_(campaign.content_name),
        content_type: newslettersValueOrEmpty_(campaign.content_type),
        content_current_state: newslettersValueOrEmpty_(campaign.content_current_state),
        content_code: newslettersValueOrEmpty_(campaign.content_code),
        activity_sent: newslettersValueOrEmpty_(campaign.activity_sent),
        activity_sent_date: newslettersValueOrEmpty_(campaign.activity_sent_date),
        activity_open: newslettersValueOrEmpty_(campaign.activity_open),
        activity_open_date: newslettersValueOrEmpty_(campaign.activity_open_date),
        correct_answer: newslettersValueOrEmpty_(campaign.correct_answer),
        correct_answer_date: newslettersValueOrEmpty_(campaign.correct_answer_date),
        incorrect_answer: newslettersValueOrEmpty_(campaign.incorrect_answer),
        incorrect_answer_date: newslettersValueOrEmpty_(campaign.incorrect_answer_date)
      };

      // Antes de guardar la fila, se normalizan los campos de fecha.
      rows.push(newslettersNormalizeDateFields_(Object.assign({}, userBase, row)));
    });
  });

  return rows;
}

/**
 * Función principal del proceso de exportación.
 *
 * Flujo:
 * 1. Define el orden de columnas.
 * 2. Obtiene la planilla activa.
 * 3. Descarga todos los datos desde la API.
 * 4. Aplana los resultados.
 * 5. Obtiene o crea la hoja NEWSLETTERS.
 * 6. Limpia el contenido previo.
 * 7. Escribe los encabezados.
 * 8. Escribe las filas de datos.
 * 9. Aplica formato a columnas de fecha.
 *
 * Si ocurre un error, intenta dejarlo visible en la hoja
 * antes de volver a lanzarlo.
 */
function exportNewslettersToSheet() {
  const headers = [
    'first_name',
    'last_name',
    'email',
    'groups',
    'functional_areas',
    'hierarchical_levels',
    'campaign_id',
    'campaign_mode',
    'campaign_name',
    'campaign_description',
    'campaign_state',
    'campaign_date',
    'campaign_expiration_date',
    'campaign_is_test',
    'content_name',
    'content_type',
    'content_current_state',
    'content_code',
    'activity_sent',
    'activity_sent_date',
    'activity_open',
    'activity_open_date',
    'correct_answer',
    'correct_answer_date',
    'incorrect_answer',
    'incorrect_answer_date'
  ];

  let sheet = null;

  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    if (!ss) {
      throw new Error('No hay un spreadsheet activo. Abrí el script desde Google Sheets.');
    }

    const results = newslettersGetResults_();
    const rows = newslettersFlattenResults_(results);

    sheet = newslettersGetOrCreateSheet_(ss, NEWSLETTERS_CONFIG.sheetName);
    sheet.clearContents();

    // Escribe la fila de encabezados.
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

    // Si hay datos, los escribe respetando el orden definido en headers.
    if (rows.length > 0) {
      const values = rows.map(function(row) {
        return headers.map(function(header) {
          return row[header];
        });
      });

      sheet.getRange(2, 1, values.length, headers.length).setValues(values);
      newslettersApplyDateFormats_(sheet, headers, values.length);
    }

  } catch (error) {
    // Si algo falla, deja una marca visible en la hoja.
    if (sheet) {
      sheet.clearContents();
      sheet.getRange('A1').setValue('ERROR');
      sheet.getRange('B1').setValue(error.message);
    }
    throw error;
  }
}

/**
 * Busca una hoja por nombre y la crea si no existe.
 */
function newslettersGetOrCreateSheet_(spreadsheet, sheetName) {
  let sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }

  return sheet;
}

/**
 * Devuelve el valor recibido o una cadena vacía si es null/undefined.
 */
function newslettersValueOrEmpty_(value) {
  if (value === null || value === undefined) return '';
  return value;
}

/**
 * Convierte arrays u objetos en texto exportable.
 *
 * - null / undefined => ''
 * - array => elementos unidos por coma
 * - objeto => JSON.stringify
 * - otros => String(value)
 */
function newslettersNormalizeListValue_(value) {
  if (value === null || value === undefined) return '';

  if (Array.isArray(value)) {
    return value
      .map(function(item) {
        if (item === null || item === undefined) return '';
        if (typeof item === 'object') return JSON.stringify(item);
        return String(item);
      })
      .filter(function(item) {
        return item !== '';
      })
      .join(', ');
  }

  if (typeof value === 'object') {
    return JSON.stringify(value);
  }

  return String(value);
}

/**
 * Recorre todos los campos configurados como fecha y los convierte.
 */
function newslettersNormalizeDateFields_(row) {
  NEWSLETTERS_CONFIG.dateFields.forEach(function(field) {
    row[field] = newslettersParseDate_(row[field]);
  });

  return row;
}

/**
 * Intenta convertir distintos formatos de fecha a Date.
 *
 * Soporta:
 * - dd/MM/yyyy HH:mm:ss.SS
 * - dd/MM/yyyy HH:mm:ss
 * - dd/MM/yyyy HH:mm
 * - yyyy-MM-dd HH:mm:ss(.SSS)
 * - ISO
 *
 * Si no puede interpretarse como fecha válida, devuelve ''.
 */
function newslettersParseDate_(value) {
  if (value === null || value === undefined) return '';
  if (value === '') return '';

  if (Object.prototype.toString.call(value) === '[object Date]') {
    return isNaN(value.getTime()) ? '' : value;
  }

  const str = String(value).trim();
  if (str === '') return '';

  let match;

  // Formato: dd/MM/yyyy HH:mm:ss.SS o dd/MM/yyyy HH:mm:ss o dd/MM/yyyy HH:mm
  match = str.match(
    /^(\d{2})\/(\d{2})\/(\d{4})(?:\s+(\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const day = Number(match[1]);
    const month = Number(match[2]) - 1;
    const year = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Formato: yyyy-MM-dd HH:mm:ss(.SSS)
  match = str.match(
    /^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const year = Number(match[1]);
    const month = Number(match[2]) - 1;
    const day = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Último intento: interpretación nativa de JavaScript.
  const nativeDate = new Date(str);
  if (!isNaN(nativeDate.getTime())) {
    return nativeDate;
  }

  return '';
}

/**
 * Aplica formato visual a las columnas de fecha de la hoja.
 */
function newslettersApplyDateFormats_(sheet, headers, rowCount) {
  NEWSLETTERS_CONFIG.dateFields.forEach(function(header) {
    const colIndex = headers.indexOf(header) + 1;
    if (colIndex > 0) {
      sheet
        .getRange(2, colIndex, rowCount, 1)
        .setNumberFormat(NEWSLETTERS_CONFIG.dateNumberFormat);
    }
  });
}

Exportación de campañas de exámenes a Google Sheets

Objetivo

Este script consulta el endpoint de campañas de exámenes por usuario en la API de SMARTFENSE y exporta los resultados a una hoja de Google Sheets llamada EXAMS.

¿Qué hace este script?

El script automatiza todo el proceso de extracción y carga de datos:

  • se autentica contra la API de SMARTFENSE,

  • consulta el endpoint correspondiente,

  • recorre todas las páginas de resultados,

  • transforma la respuesta en una estructura tabular,

  • y vuelca la información en una hoja de Google Sheets.

Resultado

Al ejecutar el script, se crea o actualiza una hoja llamada EXAMS dentro del spreadsheet activo.

La información queda organizada en formato tabla, lista para su análisis y reutilización.

¿Para qué sirve esta hoja?

La hoja generada funciona como base de datos operativa para análisis y visualización.

Su propósito es que los datos extraídos desde la API puedan utilizarse después en:

  • dashboards de Looker Studio,

  • análisis en Google Sheets,

  • cruces con otras fuentes,

  • reportes operativos,

  • tableros de seguimiento.

En este esquema, el script cumple la función de transformar la información del endpoint en una fuente tabular lista para ser consumida por herramientas de reporting.

Fuente de datos

Endpoint consultado:

/api/v1/users/campaigns/exams

Tipo de información exportada

La hoja incluye información relacionada con:

  • datos del usuario,

  • campañas de exámenes asociadas,

  • configuración del examen,

  • porcentaje de aprobación y tiempo disponible,

  • intentos y orden aleatorio,

  • estado de inicio, aprobación o desaprobación,

  • tiempo dedicado,

  • y fechas relevantes del proceso.

Consideraciones

  • El script utiliza autenticación OAuth2.

  • Reutiliza temporalmente el token mediante caché.

  • Recorre automáticamente la paginación del endpoint.

  • Convierte los campos de fecha para que Google Sheets los reconozca correctamente.

  • Limpia y vuelve a generar la hoja en cada ejecución.

  • Si ocurre un error, intenta dejar el detalle reflejado en la propia hoja.

Código

A continuación se incluye el script comentado para referencia técnica.

Examenes.gs

// Configuración específica para la exportación de campañas de exams.
// Centraliza:
// - la ruta del endpoint a consultar,
// - el nombre de la hoja de destino,
// - la clave y duración del token en caché,
// - los campos que deben interpretarse como fecha,
// - y el formato visual de fecha en Google Sheets.
const EXAMS_CONFIG = {
  endpointPath: '/api/v1/users/campaigns/exams',
  sheetName: 'EXAMS',
  tokenCacheKey: 'SMARTFENSE_ACCESS_TOKEN',
  tokenCacheSeconds: 300,
  dateFields: [
    'campaign_date',
    'campaign_expiration_date',
    'activity_sent_date',
    'activity_started_date',
    'activity_passed_date',
    'activity_failed_date'
  ],
  dateNumberFormat: 'dd/MM/yyyy HH:mm:ss'
};

/**
 * Lee las credenciales OAuth2 desde Script Properties.
 *
 * Espera encontrar:
 * - SMARTFENSE_CLIENT_ID
 * - SMARTFENSE_CLIENT_SECRET
 *
 * Si alguna no existe, corta la ejecución con error.
 */
function examsGetCredentials_() {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('SMARTFENSE_CLIENT_ID');
  const clientSecret = props.getProperty('SMARTFENSE_CLIENT_SECRET');

  if (!clientId || !clientSecret) {
    throw new Error('Faltan SMARTFENSE_CLIENT_ID o SMARTFENSE_CLIENT_SECRET en Script Properties.');
  }

  return { clientId, clientSecret };
}

/**
 * Recupera un token previamente guardado en caché.
 *
 * Esto evita solicitar un token nuevo en cada ejecución
 * mientras siga vigente dentro del tiempo configurado.
 */
function examsGetCachedToken_() {
  return CacheService.getScriptCache().get(EXAMS_CONFIG.tokenCacheKey);
}

/**
 * Guarda el token OAuth2 en la caché del script.
 *
 * El tiempo de vida del token cacheado se define en:
 * EXAMS_CONFIG.tokenCacheSeconds
 */
function examsSetCachedToken_(token) {
  CacheService.getScriptCache().put(
    EXAMS_CONFIG.tokenCacheKey,
    token,
    EXAMS_CONFIG.tokenCacheSeconds
  );
}

/**
 * Obtiene un access token OAuth2 usando el flujo client_credentials.
 *
 * Flujo:
 * 1. Intenta reutilizar un token cacheado.
 * 2. Si no existe, lee credenciales desde Script Properties.
 * 3. Llama al endpoint de token configurado en SMARTFENSE_CONFIG.tokenUrl.
 * 4. Valida la respuesta HTTP.
 * 5. Parsea el JSON y extrae access_token.
 * 6. Guarda el token en caché para próximas ejecuciones.
 */
function examsGetToken_() {
  const cachedToken = examsGetCachedToken_();
  if (cachedToken) {
    return cachedToken;
  }

  const { clientId, clientSecret } = examsGetCredentials_();
  const basicAuth = Utilities.base64Encode(clientId + ':' + clientSecret);

  const response = UrlFetchApp.fetch(SMARTFENSE_CONFIG.tokenUrl, {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: 'grant_type=client_credentials',
    headers: {
      Authorization: 'Basic ' + basicAuth,
      Accept: 'application/json',
      'Cache-Control': 'no-cache'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  // Si la API responde fuera del rango 2xx, se informa el error completo.
  if (status < 200 || status >= 300) {
    throw new Error('Error obteniendo token. HTTP ' + status + ' - ' + body);
  }

  let json;
  try {
    json = JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta del token no es JSON válido.');
  }

  // Se valida que la respuesta incluya access_token.
  if (!json.access_token) {
    throw new Error('La respuesta no incluye access_token.');
  }

  examsSetCachedToken_(json.access_token);
  return json.access_token;
}

/**
 * Valida que una URL de paginación pertenezca a la misma instancia.
 *
 * Esto agrega una capa de seguridad para evitar seguir URLs inesperadas
 * en el campo "next" de la paginación.
 */
function examsValidateUrl_(url) {
  if (!url.startsWith(SMARTFENSE_CONFIG.baseUrl + '/')) {
    throw new Error('La URL de paginación no pertenece a la instancia esperada: ' + url);
  }
}

/**
 * Consulta una página del endpoint y devuelve su contenido parseado como JSON.
 *
 * Recibe:
 * - url: URL completa de la página a consultar
 * - token: access token OAuth2
 *
 * También:
 * - valida que la URL sea segura,
 * - ejecuta el GET,
 * - controla errores HTTP,
 * - y asegura que la respuesta sea JSON válido.
 */
function examsGetPage_(url, token) {
  examsValidateUrl_(url);

  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + token,
      Accept: 'application/json'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status < 200 || status >= 300) {
    throw new Error('Error consultando página. HTTP ' + status + ' - ' + body);
  }

  try {
    return JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta de la API no es JSON válido.');
  }
}

/**
 * Recorre todas las páginas del endpoint de exams y devuelve
 * todos los objetos de results en una sola lista.
 *
 * Comportamiento:
 * - obtiene el token,
 * - construye la URL inicial,
 * - sigue el campo "next" mientras exista,
 * - concatena todos los registros encontrados en "results",
 * - y corta si se supera el máximo de páginas definido en SMARTFENSE_CONFIG.maxPages.
 *
 * Devuelve un array con todos los usuarios recuperados desde la API.
 */
function examsGetResults_() {
  const token = examsGetToken_();
  let nextUrl = SMARTFENSE_CONFIG.baseUrl + EXAMS_CONFIG.endpointPath;
  let allResults = [];
  let pageCount = 0;

  while (nextUrl) {
    pageCount++;

    if (pageCount > SMARTFENSE_CONFIG.maxPages) {
      throw new Error('Se alcanzó el máximo de páginas permitidas (' + SMARTFENSE_CONFIG.maxPages + ').');
    }

    const page = examsGetPage_(nextUrl, token);
    const results = Array.isArray(page.results) ? page.results : [];

    allResults = allResults.concat(results);
    nextUrl = page && page.next ? page.next : null;
  }

  return allResults;
}

/**
 * Devuelve una fila vacía con todas las columnas de campaña inicializadas como string vacío.
 *
 * Se usa cuando un usuario no tiene campañas relacionadas, para que la exportación
 * mantenga una estructura tabular consistente.
 */
function examsEmptyCampaignRow_() {
  return {
    campaign_id: '',
    campaign_mode: '',
    campaign_name: '',
    campaign_description: '',
    campaign_state: '',
    campaign_date: '',
    campaign_expiration_date: '',
    campaign_is_test: '',
    content_name: '',
    content_type: '',
    content_current_state: '',
    content_code: '',
    activity_sent: '',
    activity_sent_date: '',
    percentage_to_approve: '',
    minutes_availables: '',
    show_feedback: '',
    notify_user_on_creation: '',
    user_notification_on_creation: '',
    random_order: '',
    attempts: '',
    activity_started: '',
    activity_started_date: '',
    activity_passed: '',
    activity_passed_date: '',
    activity_failed: '',
    activity_failed_date: '',
    dedicated_time: ''
  };
}

/**
 * Convierte la estructura anidada de la API en filas planas listas para exportar.
 *
 * Lógica de transformación:
 * - cada usuario aporta sus datos base,
 * - cada campaña relacionada genera una fila independiente,
 * - si el usuario no tiene campañas, se genera igualmente una fila
 *   con los datos del usuario y las columnas de campaña vacías.
 *
 * Resultado:
 * Una fila = un usuario + una campaña relacionada.
 */
function examsFlattenResults_(results) {
  const rows = [];

  results.forEach(function(user) {
    // Datos base del usuario que se repetirán en cada fila de campaña.
    const userBase = {
      first_name: examsValueOrEmpty_(user.first_name),
      last_name: examsValueOrEmpty_(user.last_name),
      email: examsValueOrEmpty_(user.email),
      groups: examsNormalizeListValue_(user.groups),
      functional_areas: examsNormalizeListValue_(user.functional_areas),
      hierarchical_levels: examsNormalizeListValue_(user.hierarchical_levels)
    };

    const campaigns = Array.isArray(user.related_campaigns) ? user.related_campaigns : [];

    // Si no hay campañas relacionadas, igual se agrega una fila con columnas vacías.
    if (campaigns.length === 0) {
      rows.push(examsNormalizeDateFields_(Object.assign({}, userBase, examsEmptyCampaignRow_())));
      return;
    }

    // Si hay campañas, cada campaña se convierte en una fila individual.
    campaigns.forEach(function(campaign) {
      const row = {
        campaign_id: examsValueOrEmpty_(campaign.campaign_id),
        campaign_mode: examsValueOrEmpty_(campaign.campaign_mode),
        campaign_name: examsValueOrEmpty_(campaign.campaign_name),
        campaign_description: examsValueOrEmpty_(campaign.campaign_description),
        campaign_state: examsValueOrEmpty_(campaign.campaign_state),
        campaign_date: examsValueOrEmpty_(campaign.campaign_date),
        campaign_expiration_date: examsValueOrEmpty_(campaign.campaign_expiration_date),
        campaign_is_test: examsValueOrEmpty_(campaign.campaign_is_test),
        content_name: examsValueOrEmpty_(campaign.content_name),
        content_type: examsValueOrEmpty_(campaign.content_type),
        content_current_state: examsValueOrEmpty_(campaign.content_current_state),
        content_code: examsValueOrEmpty_(campaign.content_code),
        activity_sent: examsValueOrEmpty_(campaign.activity_sent),
        activity_sent_date: examsValueOrEmpty_(campaign.activity_sent_date),
        percentage_to_approve: examsValueOrEmpty_(campaign.percentage_to_approve),
        minutes_availables: examsValueOrEmpty_(campaign.minutes_availables),
        show_feedback: examsValueOrEmpty_(campaign.show_feedback),
        notify_user_on_creation: examsValueOrEmpty_(campaign.notify_user_on_creation),
        user_notification_on_creation: examsValueOrEmpty_(campaign.user_notification_on_creation),
        random_order: examsValueOrEmpty_(campaign.random_order),
        attempts: examsValueOrEmpty_(campaign.attempts),
        activity_started: examsValueOrEmpty_(campaign.activity_started),
        activity_started_date: examsValueOrEmpty_(campaign.activity_started_date),
        activity_passed: examsValueOrEmpty_(campaign.activity_passed),
        activity_passed_date: examsValueOrEmpty_(campaign.activity_passed_date),
        activity_failed: examsValueOrEmpty_(campaign.activity_failed),
        activity_failed_date: examsValueOrEmpty_(campaign.activity_failed_date),
        dedicated_time: examsValueOrEmpty_(campaign.dedicated_time)
      };

      // Antes de guardar la fila, se normalizan los campos de fecha.
      rows.push(examsNormalizeDateFields_(Object.assign({}, userBase, row)));
    });
  });

  return rows;
}

/**
 * Función principal del proceso de exportación.
 *
 * Flujo:
 * 1. Define el orden de columnas.
 * 2. Obtiene la planilla activa.
 * 3. Descarga todos los datos desde la API.
 * 4. Aplana los resultados.
 * 5. Obtiene o crea la hoja EXAMS.
 * 6. Limpia el contenido previo.
 * 7. Escribe los encabezados.
 * 8. Escribe las filas de datos.
 * 9. Aplica formato a columnas de fecha.
 *
 * Si ocurre un error, intenta dejarlo visible en la hoja
 * antes de volver a lanzarlo.
 */
function exportExamsToSheet() {
  const headers = [
    'first_name',
    'last_name',
    'email',
    'groups',
    'functional_areas',
    'hierarchical_levels',
    'campaign_id',
    'campaign_mode',
    'campaign_name',
    'campaign_description',
    'campaign_state',
    'campaign_date',
    'campaign_expiration_date',
    'campaign_is_test',
    'content_name',
    'content_type',
    'content_current_state',
    'content_code',
    'activity_sent',
    'activity_sent_date',
    'percentage_to_approve',
    'minutes_availables',
    'show_feedback',
    'notify_user_on_creation',
    'user_notification_on_creation',
    'random_order',
    'attempts',
    'activity_started',
    'activity_started_date',
    'activity_passed',
    'activity_passed_date',
    'activity_failed',
    'activity_failed_date',
    'dedicated_time'
  ];

  let sheet = null;

  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    if (!ss) {
      throw new Error('No hay un spreadsheet activo. Abrí el script desde Google Sheets.');
    }

    const results = examsGetResults_();
    const rows = examsFlattenResults_(results);

    sheet = examsGetOrCreateSheet_(ss, EXAMS_CONFIG.sheetName);
    sheet.clearContents();

    // Escribe la fila de encabezados.
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

    // Si hay datos, los escribe respetando el orden definido en headers.
    if (rows.length > 0) {
      const values = rows.map(function(row) {
        return headers.map(function(header) {
          return row[header];
        });
      });

      sheet.getRange(2, 1, values.length, headers.length).setValues(values);
      examsApplyDateFormats_(sheet, headers, values.length);
    }

  } catch (error) {
    // Si algo falla, deja una marca visible en la hoja.
    if (sheet) {
      sheet.clearContents();
      sheet.getRange('A1').setValue('ERROR');
      sheet.getRange('B1').setValue(error.message);
    }
    throw error;
  }
}

/**
 * Busca una hoja por nombre y la crea si no existe.
 */
function examsGetOrCreateSheet_(spreadsheet, sheetName) {
  let sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }

  return sheet;
}

/**
 * Devuelve el valor recibido o una cadena vacía si es null/undefined.
 */
function examsValueOrEmpty_(value) {
  if (value === null || value === undefined) return '';
  return value;
}

/**
 * Convierte arrays u objetos en texto exportable.
 *
 * - null / undefined => ''
 * - array => elementos unidos por coma
 * - objeto => JSON.stringify
 * - otros => String(value)
 */
function examsNormalizeListValue_(value) {
  if (value === null || value === undefined) return '';

  if (Array.isArray(value)) {
    return value
      .map(function(item) {
        if (item === null || item === undefined) return '';
        if (typeof item === 'object') return JSON.stringify(item);
        return String(item);
      })
      .filter(function(item) {
        return item !== '';
      })
      .join(', ');
  }

  if (typeof value === 'object') {
    return JSON.stringify(value);
  }

  return String(value);
}

/**
 * Recorre todos los campos configurados como fecha y los convierte.
 */
function examsNormalizeDateFields_(row) {
  EXAMS_CONFIG.dateFields.forEach(function(field) {
    row[field] = examsParseDate_(row[field]);
  });

  return row;
}

/**
 * Intenta convertir distintos formatos de fecha a Date.
 *
 * Soporta:
 * - dd/MM/yyyy HH:mm:ss.SS
 * - dd/MM/yyyy HH:mm:ss
 * - dd/MM/yyyy HH:mm
 * - yyyy-MM-dd HH:mm:ss(.SSS)
 * - ISO
 *
 * Si no puede interpretarse como fecha válida, devuelve ''.
 */
function examsParseDate_(value) {
  if (value === null || value === undefined) return '';
  if (value === '') return '';

  if (Object.prototype.toString.call(value) === '[object Date]') {
    return isNaN(value.getTime()) ? '' : value;
  }

  const str = String(value).trim();
  if (str === '') return '';

  let match;

  // Formato: dd/MM/yyyy HH:mm:ss.SS o dd/MM/yyyy HH:mm:ss o dd/MM/yyyy HH:mm
  match = str.match(
    /^(\d{2})\/(\d{2})\/(\d{4})(?:\s+(\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const day = Number(match[1]);
    const month = Number(match[2]) - 1;
    const year = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Formato: yyyy-MM-dd HH:mm:ss(.SSS)
  match = str.match(
    /^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const year = Number(match[1]);
    const month = Number(match[2]) - 1;
    const day = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Último intento: interpretación nativa de JavaScript.
  const nativeDate = new Date(str);
  if (!isNaN(nativeDate.getTime())) {
    return nativeDate;
  }

  return '';
}

/**
 * Aplica formato visual a las columnas de fecha de la hoja.
 */
function examsApplyDateFormats_(sheet, headers, rowCount) {
  EXAMS_CONFIG.dateFields.forEach(function(header) {
    const colIndex = headers.indexOf(header) + 1;
    if (colIndex > 0) {
      sheet
        .getRange(2, colIndex, rowCount, 1)
        .setNumberFormat(EXAMS_CONFIG.dateNumberFormat);
    }
  });
}

Exportación de campañas de encuestas a Google Sheets

Objetivo

Este script consulta el endpoint de campañas de encuestas por usuario en la API de SMARTFENSE y exporta los resultados a una hoja de Google Sheets llamada SURVEYS.

¿Qué hace este script?

El script automatiza todo el proceso de extracción y carga de datos:

  • se autentica contra la API de SMARTFENSE,

  • consulta el endpoint correspondiente,

  • recorre todas las páginas de resultados,

  • transforma la respuesta en una estructura tabular,

  • y vuelca la información en una hoja de Google Sheets.

Resultado

Al ejecutar el script, se crea o actualiza una hoja llamada SURVEYS dentro del spreadsheet activo.

La información queda organizada en formato tabla, lista para su análisis y reutilización.

¿Para qué sirve esta hoja?

La hoja generada funciona como base de datos operativa para análisis y visualización.

Su propósito es que los datos extraídos desde la API puedan utilizarse después en:

  • dashboards de Looker Studio,

  • análisis en Google Sheets,

  • cruces con otras fuentes,

  • reportes operativos,

  • tableros de seguimiento.

En este esquema, el script cumple la función de transformar la información del endpoint en una fuente tabular lista para ser consumida por herramientas de reporting.

Fuente de datos

Endpoint consultado:

/api/v1/users/campaigns/surveys

Tipo de información exportada

La hoja incluye información relacionada con:

  • datos del usuario,

  • campañas de encuestas asociadas,

  • estado de envío, inicio y finalización,

  • configuración de notificación al usuario,

  • modalidad anónima,

  • tiempo dedicado,

  • y fechas relevantes del proceso.

Consideraciones

  • El script utiliza autenticación OAuth2.

  • Reutiliza temporalmente el token mediante caché.

  • Recorre automáticamente la paginación del endpoint.

  • Convierte los campos de fecha para que Google Sheets los reconozca correctamente.

  • Limpia y vuelve a generar la hoja en cada ejecución.

  • Si ocurre un error, intenta dejar el detalle reflejado en la propia hoja.

Código

A continuación se incluye el script comentado para referencia técnica.

Encuestas.gs

// Configuración específica para la exportación de campañas de surveys.
// Centraliza:
// - la ruta del endpoint a consultar,
// - el nombre de la hoja de destino,
// - la clave y duración del token en caché,
// - los campos que deben interpretarse como fecha,
// - y el formato visual de fecha en Google Sheets.
const SURVEYS_CONFIG = {
  endpointPath: '/api/v1/users/campaigns/surveys',
  sheetName: 'SURVEYS',
  tokenCacheKey: 'SMARTFENSE_ACCESS_TOKEN',
  tokenCacheSeconds: 300,
  dateFields: [
    'campaign_date',
    'campaign_expiration_date',
    'activity_sent_date',
    'activity_started_date',
    'activity_completed_date'
  ],
  dateNumberFormat: 'dd/MM/yyyy HH:mm:ss'
};

/**
 * Lee las credenciales OAuth2 desde Script Properties.
 *
 * Espera encontrar:
 * - SMARTFENSE_CLIENT_ID
 * - SMARTFENSE_CLIENT_SECRET
 *
 * Si alguna no existe, corta la ejecución con error.
 */
function surveysGetCredentials_() {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('SMARTFENSE_CLIENT_ID');
  const clientSecret = props.getProperty('SMARTFENSE_CLIENT_SECRET');

  if (!clientId || !clientSecret) {
    throw new Error('Faltan SMARTFENSE_CLIENT_ID o SMARTFENSE_CLIENT_SECRET en Script Properties.');
  }

  return { clientId, clientSecret };
}

/**
 * Recupera un token previamente guardado en caché.
 *
 * Esto evita solicitar un token nuevo en cada ejecución
 * mientras siga vigente dentro del tiempo configurado.
 */
function surveysGetCachedToken_() {
  return CacheService.getScriptCache().get(SURVEYS_CONFIG.tokenCacheKey);
}

/**
 * Guarda el token OAuth2 en la caché del script.
 *
 * El tiempo de vida del token cacheado se define en:
 * SURVEYS_CONFIG.tokenCacheSeconds
 */
function surveysSetCachedToken_(token) {
  CacheService.getScriptCache().put(
    SURVEYS_CONFIG.tokenCacheKey,
    token,
    SURVEYS_CONFIG.tokenCacheSeconds
  );
}

/**
 * Obtiene un access token OAuth2 usando el flujo client_credentials.
 *
 * Flujo:
 * 1. Intenta reutilizar un token cacheado.
 * 2. Si no existe, lee credenciales desde Script Properties.
 * 3. Llama al endpoint de token configurado en SMARTFENSE_CONFIG.tokenUrl.
 * 4. Valida la respuesta HTTP.
 * 5. Parsea el JSON y extrae access_token.
 * 6. Guarda el token en caché para próximas ejecuciones.
 */
function surveysGetToken_() {
  const cachedToken = surveysGetCachedToken_();
  if (cachedToken) {
    return cachedToken;
  }

  const { clientId, clientSecret } = surveysGetCredentials_();
  const basicAuth = Utilities.base64Encode(clientId + ':' + clientSecret);

  const response = UrlFetchApp.fetch(SMARTFENSE_CONFIG.tokenUrl, {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: 'grant_type=client_credentials',
    headers: {
      Authorization: 'Basic ' + basicAuth,
      Accept: 'application/json',
      'Cache-Control': 'no-cache'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  // Si la API responde fuera del rango 2xx, se informa el error completo.
  if (status < 200 || status >= 300) {
    throw new Error('Error obteniendo token. HTTP ' + status + ' - ' + body);
  }

  let json;
  try {
    json = JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta del token no es JSON válido.');
  }

  // Se valida que la respuesta incluya access_token.
  if (!json.access_token) {
    throw new Error('La respuesta no incluye access_token.');
  }

  surveysSetCachedToken_(json.access_token);
  return json.access_token;
}

/**
 * Valida que una URL de paginación pertenezca a la misma instancia.
 *
 * Esto agrega una capa de seguridad para evitar seguir URLs inesperadas
 * en el campo "next" de la paginación.
 */
function surveysValidateUrl_(url) {
  if (!url.startsWith(SMARTFENSE_CONFIG.baseUrl + '/')) {
    throw new Error('La URL de paginación no pertenece a la instancia esperada: ' + url);
  }
}

/**
 * Consulta una página del endpoint y devuelve su contenido parseado como JSON.
 *
 * Recibe:
 * - url: URL completa de la página a consultar
 * - token: access token OAuth2
 *
 * También:
 * - valida que la URL sea segura,
 * - ejecuta el GET,
 * - controla errores HTTP,
 * - y asegura que la respuesta sea JSON válido.
 */
function surveysGetPage_(url, token) {
  surveysValidateUrl_(url);

  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + token,
      Accept: 'application/json'
    },
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status < 200 || status >= 300) {
    throw new Error('Error consultando página. HTTP ' + status + ' - ' + body);
  }

  try {
    return JSON.parse(body);
  } catch (e) {
    throw new Error('La respuesta de la API no es JSON válido.');
  }
}

/**
 * Recorre todas las páginas del endpoint de surveys y devuelve
 * todos los objetos de results en una sola lista.
 *
 * Comportamiento:
 * - obtiene el token,
 * - construye la URL inicial,
 * - sigue el campo "next" mientras exista,
 * - concatena todos los registros encontrados en "results",
 * - y corta si se supera el máximo de páginas definido en SMARTFENSE_CONFIG.maxPages.
 *
 * Devuelve un array con todos los usuarios recuperados desde la API.
 */
function surveysGetResults_() {
  const token = surveysGetToken_();
  let nextUrl = SMARTFENSE_CONFIG.baseUrl + SURVEYS_CONFIG.endpointPath;
  let allResults = [];
  let pageCount = 0;

  while (nextUrl) {
    pageCount++;

    if (pageCount > SMARTFENSE_CONFIG.maxPages) {
      throw new Error('Se alcanzó el máximo de páginas permitidas (' + SMARTFENSE_CONFIG.maxPages + ').');
    }

    const page = surveysGetPage_(nextUrl, token);
    const results = Array.isArray(page.results) ? page.results : [];

    allResults = allResults.concat(results);
    nextUrl = page && page.next ? page.next : null;
  }

  return allResults;
}

/**
 * Devuelve una fila vacía con todas las columnas de campaña inicializadas como string vacío.
 *
 * Se usa cuando un usuario no tiene campañas relacionadas, para que la exportación
 * mantenga una estructura tabular consistente.
 */
function surveysEmptyCampaignRow_() {
  return {
    campaign_id: '',
    campaign_mode: '',
    campaign_name: '',
    campaign_description: '',
    campaign_state: '',
    campaign_date: '',
    campaign_expiration_date: '',
    campaign_is_test: '',
    content_name: '',
    content_type: '',
    content_current_state: '',
    content_code: '',
    activity_sent: '',
    activity_sent_date: '',
    activity_started: '',
    activity_started_date: '',
    notify_user_on_creation: '',
    user_notification_on_creation: '',
    activity_completed: '',
    activity_completed_date: '',
    anonymous: '',
    dedicated_time: ''
  };
}

/**
 * Convierte la estructura anidada de la API en filas planas listas para exportar.
 *
 * Lógica de transformación:
 * - cada usuario aporta sus datos base,
 * - cada campaña relacionada genera una fila independiente,
 * - si el usuario no tiene campañas, se genera igualmente una fila
 *   con los datos del usuario y las columnas de campaña vacías.
 *
 * Resultado:
 * Una fila = un usuario + una campaña relacionada.
 */
function surveysFlattenResults_(results) {
  const rows = [];

  results.forEach(function(user) {
    // Datos base del usuario que se repetirán en cada fila de campaña.
    const userBase = {
      first_name: surveysValueOrEmpty_(user.first_name),
      last_name: surveysValueOrEmpty_(user.last_name),
      email: surveysValueOrEmpty_(user.email),
      groups: surveysNormalizeListValue_(user.groups),
      functional_areas: surveysNormalizeListValue_(user.functional_areas),
      hierarchical_levels: surveysNormalizeListValue_(user.hierarchical_levels)
    };

    const campaigns = Array.isArray(user.related_campaigns) ? user.related_campaigns : [];

    // Si no hay campañas relacionadas, igual se agrega una fila con columnas vacías.
    if (campaigns.length === 0) {
      rows.push(surveysNormalizeDateFields_(Object.assign({}, userBase, surveysEmptyCampaignRow_())));
      return;
    }

    // Si hay campañas, cada campaña se convierte en una fila individual.
    campaigns.forEach(function(campaign) {
      const row = {
        campaign_id: surveysValueOrEmpty_(campaign.campaign_id),
        campaign_mode: surveysValueOrEmpty_(campaign.campaign_mode),
        campaign_name: surveysValueOrEmpty_(campaign.campaign_name),
        campaign_description: surveysValueOrEmpty_(campaign.campaign_description),
        campaign_state: surveysValueOrEmpty_(campaign.campaign_state),
        campaign_date: surveysValueOrEmpty_(campaign.campaign_date),
        campaign_expiration_date: surveysValueOrEmpty_(campaign.campaign_expiration_date),
        campaign_is_test: surveysValueOrEmpty_(campaign.campaign_is_test),
        content_name: surveysValueOrEmpty_(campaign.content_name),
        content_type: surveysValueOrEmpty_(campaign.content_type),
        content_current_state: surveysValueOrEmpty_(campaign.content_current_state),
        content_code: surveysValueOrEmpty_(campaign.content_code),
        activity_sent: surveysValueOrEmpty_(campaign.activity_sent),
        activity_sent_date: surveysValueOrEmpty_(campaign.activity_sent_date),
        activity_started: surveysValueOrEmpty_(campaign.activity_started),
        activity_started_date: surveysValueOrEmpty_(campaign.activity_started_date),
        notify_user_on_creation: surveysValueOrEmpty_(campaign.notify_user_on_creation),
        user_notification_on_creation: surveysValueOrEmpty_(campaign.user_notification_on_creation),
        activity_completed: surveysValueOrEmpty_(campaign.activity_completed),
        activity_completed_date: surveysValueOrEmpty_(campaign.activity_completed_date),
        anonymous: surveysValueOrEmpty_(campaign.anonymous),
        dedicated_time: surveysValueOrEmpty_(campaign.dedicated_time)
      };

      // Antes de guardar la fila, se normalizan los campos de fecha.
      rows.push(surveysNormalizeDateFields_(Object.assign({}, userBase, row)));
    });
  });

  return rows;
}

/**
 * Función principal del proceso de exportación.
 *
 * Flujo:
 * 1. Define el orden de columnas.
 * 2. Obtiene la planilla activa.
 * 3. Descarga todos los datos desde la API.
 * 4. Aplana los resultados.
 * 5. Obtiene o crea la hoja SURVEYS.
 * 6. Limpia el contenido previo.
 * 7. Escribe los encabezados.
 * 8. Escribe las filas de datos.
 * 9. Aplica formato a columnas de fecha.
 *
 * Si ocurre un error, intenta dejarlo visible en la hoja
 * antes de volver a lanzarlo.
 */
function exportSurveysToSheet() {
  const headers = [
    'first_name',
    'last_name',
    'email',
    'groups',
    'functional_areas',
    'hierarchical_levels',
    'campaign_id',
    'campaign_mode',
    'campaign_name',
    'campaign_description',
    'campaign_state',
    'campaign_date',
    'campaign_expiration_date',
    'campaign_is_test',
    'content_name',
    'content_type',
    'content_current_state',
    'content_code',
    'activity_sent',
    'activity_sent_date',
    'activity_started',
    'activity_started_date',
    'notify_user_on_creation',
    'user_notification_on_creation',
    'activity_completed',
    'activity_completed_date',
    'anonymous',
    'dedicated_time'
  ];

  let sheet = null;

  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    if (!ss) {
      throw new Error('No hay un spreadsheet activo. Abrí el script desde Google Sheets.');
    }

    const results = surveysGetResults_();
    const rows = surveysFlattenResults_(results);

    sheet = surveysGetOrCreateSheet_(ss, SURVEYS_CONFIG.sheetName);
    sheet.clearContents();

    // Escribe la fila de encabezados.
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

    // Si hay datos, los escribe respetando el orden definido en headers.
    if (rows.length > 0) {
      const values = rows.map(function(row) {
        return headers.map(function(header) {
          return row[header];
        });
      });

      sheet.getRange(2, 1, values.length, headers.length).setValues(values);
      surveysApplyDateFormats_(sheet, headers, values.length);
    }

  } catch (error) {
    // Si algo falla, deja una marca visible en la hoja.
    if (sheet) {
      sheet.clearContents();
      sheet.getRange('A1').setValue('ERROR');
      sheet.getRange('B1').setValue(error.message);
    }
    throw error;
  }
}

/**
 * Busca una hoja por nombre y la crea si no existe.
 */
function surveysGetOrCreateSheet_(spreadsheet, sheetName) {
  let sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }

  return sheet;
}

/**
 * Devuelve el valor recibido o una cadena vacía si es null/undefined.
 */
function surveysValueOrEmpty_(value) {
  if (value === null || value === undefined) return '';
  return value;
}

/**
 * Convierte arrays u objetos en texto exportable.
 *
 * - null / undefined => ''
 * - array => elementos unidos por coma
 * - objeto => JSON.stringify
 * - otros => String(value)
 */
function surveysNormalizeListValue_(value) {
  if (value === null || value === undefined) return '';

  if (Array.isArray(value)) {
    return value
      .map(function(item) {
        if (item === null || item === undefined) return '';
        if (typeof item === 'object') return JSON.stringify(item);
        return String(item);
      })
      .filter(function(item) {
        return item !== '';
      })
      .join(', ');
  }

  if (typeof value === 'object') {
    return JSON.stringify(value);
  }

  return String(value);
}

/**
 * Recorre todos los campos configurados como fecha y los convierte.
 */
function surveysNormalizeDateFields_(row) {
  SURVEYS_CONFIG.dateFields.forEach(function(field) {
    row[field] = surveysParseDate_(row[field]);
  });

  return row;
}

/**
 * Intenta convertir distintos formatos de fecha a Date.
 *
 * Soporta:
 * - dd/MM/yyyy HH:mm:ss.SS
 * - dd/MM/yyyy HH:mm:ss
 * - dd/MM/yyyy HH:mm
 * - yyyy-MM-dd HH:mm:ss(.SSS)
 * - ISO
 *
 * Si no puede interpretarse como fecha válida, devuelve ''.
 */
function surveysParseDate_(value) {
  if (value === null || value === undefined) return '';
  if (value === '') return '';

  if (Object.prototype.toString.call(value) === '[object Date]') {
    return isNaN(value.getTime()) ? '' : value;
  }

  const str = String(value).trim();
  if (str === '') return '';

  let match;

  // Formato: dd/MM/yyyy HH:mm:ss.SS o dd/MM/yyyy HH:mm:ss o dd/MM/yyyy HH:mm
  match = str.match(
    /^(\d{2})\/(\d{2})\/(\d{4})(?:\s+(\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const day = Number(match[1]);
    const month = Number(match[2]) - 1;
    const year = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Formato: yyyy-MM-dd HH:mm:ss(.SSS)
  match = str.match(
    /^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2}))?(?:\.(\d{1,3}))?)?$/
  );
  if (match) {
    const year = Number(match[1]);
    const month = Number(match[2]) - 1;
    const day = Number(match[3]);
    const hour = Number(match[4] || 0);
    const minute = Number(match[5] || 0);
    const second = Number(match[6] || 0);

    let ms = Number(match[7] || 0);
    if (match[7]) {
      if (match[7].length === 1) ms = ms * 100;
      if (match[7].length === 2) ms = ms * 10;
    }

    return new Date(year, month, day, hour, minute, second, ms);
  }

  // Último intento: interpretación nativa de JavaScript.
  const nativeDate = new Date(str);
  if (!isNaN(nativeDate.getTime())) {
    return nativeDate;
  }

  return '';
}

/**
 * Aplica formato visual a las columnas de fecha de la hoja.
 */
function surveysApplyDateFormats_(sheet, headers, rowCount) {
  SURVEYS_CONFIG.dateFields.forEach(function(header) {
    const colIndex = headers.indexOf(header) + 1;
    if (colIndex > 0) {
      sheet
        .getRange(2, colIndex, rowCount, 1)
        .setNumberFormat(SURVEYS_CONFIG.dateNumberFormat);
    }
  });
}

 

Relacionada con

Artículos relacionados