HomeSugerenciasEjemplos
imagen

Búsquedas sobre Google Sheets con Google Apps Script

Buscar datos en una hoja de cálculo de google a partir de un parámetro ingresado en un formulario HTML.

Que vamos a usar

  • Google Sheets: Que sera nuestra fuente de datos y donde haremos las búsquedas.
  • Google Site: Nos permitirá generar de forma rápida y simple un sitio web publico ( o privado en caso de quererlo así para usuarios de GSuite ) donde "insertaremos" nuestro formulario de consulta.
  • Google Apps Script:

"...un lenguaje de scripting para el desarrollo de aplicaciones ligeras en la plataforma G Suite, basado en basa en Javascript" - ver más

Google Apps Script nos permitirá crear un script simple que actúe como intermediario entre el formulario web y la hoja de cálculo, recibiendo los campos de búsqueda, realizando la búsqueda, y retornando los resultados para ser mostrados en el frontend.

Requisitos

  • Una hoja de cálculo con nuestros datos.
  • Un sitio creado con google site.
  • acceso a la consola de Google Apps Script.

Caso de uso

  • Usuario ingresa su ID en un campo de texto.
  • Presiona el botón buscar.
  • Se imprime en pantalla la información del usuario.

Paso a paso

Hoja de cálculo

  • Crear una hoja de cálculo (google sheets) con la siguiente estructura

    userId email phone
    90900900-1 user.1@mail.com 56977778881
    90900900-2 user.2@mail.com 56977778882
    90900900-3 user.3@mail.com 56977778883
  • Generar el link para compartir, que tenga acceso a leer la hoja de cálculo, obtendrás algo como esto: https://docs.google.com/spreadsheets/d/1T6vf-i6y0w0UlXf8fX-EW-uVc1NzHYSSwbru1nv6_2iY5o/edit?usp=sharing. Para este ejemplo usamos la opción Cualquiera con el vínculo puede ver, pero según sea el caso, podrías restringir el acceso a un usuario especifico, pero más adelante, cuando el script se ejecute, deberá ejecutarse como ese usuario, para que el script tenga acceso a los datos.

Google Apps Script

  • Ingresamos a https://script.google.com
  • Damos click en nuevo proyecto, se creara un proyecto nuevo y contendrá un archivo Código.gs con una función vacía.
  • Reemplazaremos el contenido de ese archivo, por lo siguiente
  • Importante reemplazar la URL de tu hoja de cálculo y el nombre de la hoja donde estarán los datos.
// Archivo Código.gs

// URL que genera Google Sheet al solicitar link para compartir
var myGoogleSheetURL =
  "https://docs.google.com/spreadsheets/d/1T6vf-i6y0w0UlXf8fX-EW-uVc1NzHYSSwbru1nv6_2iY5o/edit?usp=sharing";

// Nombre de la hoja donde estan nuestros datos
var myGoogleSheetName = "Hoja 1";

/**
 * Función de inicialización ( la primera que se ejecuta )
 */
function doGet() {
  // Muestra el HTML
  return HtmlService.createHtmlOutputFromFile("formulario");
}

/**
 * Funcion que sera llamada al hacer click en el boton del formulario
 * @param form formulario recibido desde el frotend
 */
function receiveForm(form) {
  // Obtenemos el valor del campo userId
  var userId = form.userId;

  // Llama a la funcion para buscar datos, pasandole el parametro
  return searchData(userId);
}

/**
 * Busca en la hoja de calculos los datos del usuario
 * @param userId ID del usuario que queremos buscar
 */
function searchData(userId) {
  // Obtiene la hoja de calculos
  var sheet = getSpreadSheet(myGoogleSheetURL, myGoogleSheetName);

  // Recorre todas las filas buscando la coincidencia del userID con el valor
  // de la columna 0
  var rowsResult = sheet
    .getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn())
    .getValues()
    .filter(function (row) {
      return row[0] === userId;
    });

  // Obtiene el resultado
  var firstRow = rowsResult[0];

  // Parsea cada uno de los campos
  var user = {
    id: firstRow[0],
    email: firstRow[1],
    phone: firstRow[2],
  };

  // Serializa el OBJ para enviar la respuesta al frontend
  var result = JSON.stringify(user);
  return result;
}

/**
 * Obtiene una hoja de calculo según la URL y nombre de la hoja especificados
 */
function getSpreadSheet(url, sheetName) {
  var ss = SpreadsheetApp.openByUrl(url);
  var sheet = ss.getSheetByName(sheetName);
  return sheet;
}
  • le damos click a archivo > guardar, nos pedirá un nombre para el nuevo proyecto, y guardamos.
  • Ahora crearemos nuestro formulario html, archivo > nuevo > archivo html, y ponemos el nombre formulario (sin el .html, la extensión se agregara automáticamente).
  • Se abrirá el archivo recién creado (formulario.html) con una estructura básica html, reemplazamos el contenido por el siguiente:
<!-- archivo formulario.html -->
<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
    <style>
      :root {
        --primary-color: #4caf50;
      }
      body {
        text-align: center;
        font-family: 'Helvetica', 'Arial', sans-serif;
      }
      input {
        font-size: 16px;
      }
      input[type="text"] {
        border: none;
        border-bottom: 2px solid var(--primary-color);
        margin: 10px;
        padding: 10px;
        text-align: center;
      }
      input[type="button"] {
        background-color: var(--primary-color);
        border: none;
        color: white;
        padding: 16px 32px;
        text-decoration: none;
        margin: 10px 2px;
        cursor: pointer;
      }
      input[type="button"]:disabled {
        background-color: #dadada;
      }
    </style>
    <title>Test Page</title>
  </head>

  <body>
    <!-- Inicio del formulario-->
    <form action="#">
      <input
        id="inputUserId"
        type="text"
        value=""
        name="userId"
        placeholder="ID del usuario"
        onfocus="clearDiv()"
      />
      <br />

      <input
        id="btnSend"
        type="button"
        onClick="formSubmit()"
        value="Consultar"
      />
    </form>
    <!-- Fin del formulario-->

    <!-- div donde se mostraran los resultados -->
    <div id="divResponse"></div>
  </body>
  <script type="text/javascript">
    // Función que se ejecuta al presionar el boton del formulario
    function formSubmit() {
      document.getElementById("divResponse").innerHTML = "Buscando...";

      // Llama a la funcion receiveForm de nuestro script
      google.script.run
        .withSuccessHandler(onSuccess)
        .withFailureHandler(onFailure)
        .receiveForm(document.forms[0]);

      // Desabilita los inputs y el btn mientra se hace la llamada
      disableInputs();
    }

    // Función que se ejecuta cuando la busqueda ocurre con exito
    function onSuccess(response) {
      // Parsea la respuesta
      var user = JSON.parse(response);

      // Carga el campo email de la respuesta en el div
      document.getElementById("divResponse").innerHTML = user.email;

      // Habilita el campo y boton
      enableInputs();
    }

    // Función que se ejecuta cuando la busqueda ocurre con ERROR
    function onFailure() {
      // Pone mensaje en div
      document.getElementById("divResponse").innerHTML = "Datos no encontrados";
      enableInputs();
    }

    // Desabilita boton y campo de texto
    function disableInputs() {
      document.getElementById("btnSend").disabled = true;
      document.getElementById("inputUserId").disabled = true;
    }

    // Habilita boton y campo de texto
    function enableInputs() {
      document.getElementById("btnSend").disabled = false;
      document.getElementById("inputUserId").disabled = false;
    }

    function clearDiv() {
      document.getElementById("divResponse").innerHTML = "";
    }
  </script>
</html>
  • guardamos los cambios en archivo > guardar
  • Ahora vamos a publicar > implementar como aplicación web

  • Se abrirá una ventana con el título Deploy as web app (o su equivalente al español) con tres secciones

    • Project version : Acá, es importante que selecciones nuevo para que se publiquen los nuevos cambios que acabas de guardar, y en la caja de descripción puedes poner los detalles de los cambios que vas a desplegar, para que tengas un registro más ordenado, pero poner este detalle es opcional.
    • Execute the app as : Con qué autorización se debe ejecutar la aplicación, su cuenta (la del desarrollador) o la cuenta del usuario que visita la aplicación (user accessing the web app). Seleccionaremos Mi cuenta
    • Who has access to the app : Quién tiene acceso a la aplicación: Selecciona a quién se le debe permitir ver el formulario. Las opciones varían según el tipo de cuenta que tenga, pero pueden incluir:
    • "Solo yo",
    • "cualquier miembro de su dominio,
    • "Cualquiera" (con una cuenta de Google)

    • "Cualquier persona, incluso anónima".

      Como queremos que nuestro formulario sea publico y todos puedan acceder a el, usaremos Cualquier persona, incluso anónima, pero tú puedes configurar estos permisos según tu caso particular.

  • Le damos click al botón deploy ( o su equivalente en español) para "desplegar" ( publicar ) nuestro proyecto.

  • Veras un nuevo cuadro de diálogo con un mensaje que indica que tu proyecto se implementó con éxito como una aplicación web.

    Este cuadro de diálogo proporciona dos URL importantes para su aplicación:

    • El primero está etiquetado como Current web app URL (URL de la aplicación web actual) y termina en /exec. Esta URL es para la versión publicada de tu aplicación, basada en la última versión que guardó e implementó. La versión de producción y la que los usuarios ven y usan.
    • El segundo es el enlace etiquetado como latest code ( código más reciente) y termina en /dev. Solo los usuarios que tienen acceso de edición a la secuencia de comandos pueden acceder a esta URL. Esta instancia de la aplicación siempre ejecuta el código guardado más recientemente, no necesariamente una versión formal, y está destinada a pruebas rápidas durante el desarrollo, así puedes probar los cambios en el proyecto sin tener que hacer un "deploy" y afectar a los usuarios de "producción"

    Ya puedes usar ambas URL, la Current web app URL y la latest code que puedes pegar en una nueva pestaña de tu navegador y si todo salio bien, deberías ver el contenido del archivo formulario.html publicado.

    Documentación oficial

Sitio web

Ahora que ya tenemos nuestra fuente de datos ( la hoja de cálculo de google) y tenemos nuestro formulario que la puede consultar, intentaremos "insertar" el formulario en un sitio web.

Para este ejemplo usaremos un sitio web generado con google site.

  • En google sites vamos a crear un nuevo sitio web, aplicaremos el diseño que queramos.

  • Luego insertaremos una URL externa en el sitio, al momento de realizar este post, los pasos para ello son:

    • Estando en el editor del sitio web
    • A la derecha, haz clic en Insertar y luego Insertar.
    • Introduce la URL que quieras insertar.
    • Haz clic en Insertar.
    • Para publicar los cambios, haz clic en Publicar, arriba a la derecha.

    Para más detalles puedes visitar la documentación oficial en la sección Añadir contenido de otro sitio web

  • Guarda los cambios, y publica.
  • Si estas usando google site de pago, recuerda configurar quién puede ver tu sitio web. Puedes revisar la documentación oficial en la sección quién puede ver tu sitio web.

Listo! Ya tenemos un formulario web que se comunica con google sheets y obtiene datos de ella.