Google Apps Script, parte 6: Buscar filas en una hoja de cálculos

Ferchu Google Apps Script, Google Drive, Html, Javascript, Vuejs

Vamos a continuar con nuestro ejemplo. En esta ocasión mostraremos la información que hemos guardado. Tendremos una lista completa de todos los ítem del menú, pero también un buscador que nos permita encontrar registros.

En una publicación pasada habíamos creado una función con GAS, para mostrar todas las categorías:

function getCategorias(){
  
  let ws = SS.getSheetByName("Categorias");
  
  let data = ws.getRange(
    2, 
    1, 
    ws.getLastRow() - 1, 
    1
  ).getValues();
      
  return data;
  
}

Como ya sabemos recuperar todos los registros de la hoja de categorías, podemos escribir una función similar para hacer lo propio con la hoja del menú. Vamos a agregarla a nuestro archivos .gs:

function getMenu(){
  
  let ws = SS.getSheetByName("Menu");
  
  let data = ws.getRange(
    2, 
    1,
    ws.getLastRow() - 1, 
    4
  ).getValues();
      
  return data;
    
}

Notar que el tercer parámetro es 4 (cuatro) Esto se debe a que como dije en una publicación anterior, este parámetro cuenta la cantidad de columnas de izquierda a derecha. En este caso, nosotros queremos recuperar las cuatro primeras: el ID, el nombre, la descripción y la categoría del ítem.

¡Listo! Con esto ya podemos acceder a los ítems del menú, así que ahora vamos a agregar al frontend dos cosas. Dentro de data un array vacío, que posteriormente va a llenarse con la información que va recuperar del menú completo o filtrado:

menu: []

Y dentro de methods, la acción que va a recuperar todo el menú completo:

getMenu: function(){
                    let that = this;
                    that.menu = [];
                    google.script.run.withSuccessHandler(function(menu){
                        that.menu = [];
                        for(let i = 0; i < menu.length; i++)
                        {
                            that.menu.push({
                                ID:  menu[i][0],
                                nombre: menu[i][1],
                                descripcion: menu[i][2],
                                categoria: menu[i][3]
                            });
                        }
                    }).getMenu();
}

Una cosa que vale aclarar aquí es que, la función que hicimos antes nos va a traer un array de arrays, es decir algo como esto:

[
	["1601229551655-76", "Agua mineral sin gas", "Agua sin gas", "Bebidas"],
	["1601160650000-62", "Ensalada César", "Croutons, lechuga romana y tomate", "Ensaladas"],
	["1601160613399-45", "Pizza grande de muzzarella", "Muzzarella, oregano y aceitunas verdes", "Pizzas"],
	["1601229451944-36", "Ensalda de frutas", "Banana, manzana y naranja", "Postres"],
	["1601229485750-56", "Flan casero", "Flan con dulce de leche", "Postres"],
	["1601160634457-32", "Sandwich Milanesa completo", "Carne, huevo, tomate y lechuga", "Sandwiches"],
	["1601229404976-83", "Sandwich Lomito completo", "Lomo, huevo y jamón", "Sandwiches"]
]

A mí me gusta más trabajar con arrays de objetos (JSON), por eso formateo esta información:

for(let i = 0; i < menu.length; i++)
                        {
                            that.menu.push({
                                ID:  menu[i][0],
                                nombre: menu[i][1],
                                descripcion: menu[i][2],
                                categoria: menu[i][3]
                            });
}

Para que finalmente quede algo como esto:

[ 
	{ "ID": "1601229551655-76", "nombre": "Agua mineral sin gas", "descripcion": "Agua sin gas", "categoria": "Bebidas" }, 
	{ "ID": "1601160650000-62", "nombre": "Ensalada César", "descripcion": "Croutons, lechuga romana y tomate", "categoria": "Ensaladas" }, 
	{ "ID": "1601160613399-45", "nombre": "Pizza grande de muzzarella", "descripcion": "Muzzarella, oregano y aceitunas verdes", "categoria": "Pizzas" }, 
	{ "ID": "1601229451944-36", "nombre": "Ensalda de frutas", "descripcion": "Banana, manzana y naranja", "categoria": "Postres" }, 
	{ "ID": "1601229485750-56", "nombre": "Flan casero", "descripcion": "Flan con dulce de leche", "categoria": "Postres" }, 
	{ "ID": "1601160634457-32", "nombre": "Sandwich Milanesa completo", "descripcion": "Carne, huevo, tomate y lechuga", "categoria": "Sandwiches" }, 
	{ "ID": "1601229404976-83", "nombre": "Sandwich Lomito completo", "descripcion": "Lomo, huevo y jamón", "categoria": "Sandwiches" } 
]

Pero esto es sólo un paso más, cada uno puede resolverlo como prefiera.

Método indexOf() de un String

Una cosa que es importante entender, es que si bien el código que escribimos en GAS es casi idéntico a Javascript, hay cosas que no funcionan.

Por ejemplo en Javascript existe un método includes(), el cual nos permite buscar coincidencias dentro una un string. El problema es que este método no puede utilizarse en GAS, no está definido o mejor dicho no existe.

Buscando un poco en la web, encontré que una forma de reemplazar el mismo mediante el método indexOf(). Por ejemplo:

let texto = "Porque da vuelta la rueda, Porque no te detenés";
let texto_busqueda = "rueda";
let resultado = texto.indexOf(texto_busqueda);

Este método lo que hace es buscar una coincidencia dentro de una cadena de texto. Devuelve el número del caracter donde la encontró, comenzando por 0 (cero) En este caso nos va a devolver 20, porque encuentra la cadena «rueda» a partir del caracter 20 del texto. Si no encontrase coincidencias entonces el resultado sería -1.

Sin embargo, al igual que el método includes(), el método indexOf() es case sensitive. Esto quiere decir que va a diferenciar entre minúsculas y mayúsculas.

Por ejemplo:

let texto = "Porque da vuelta la rueda, Porque no te detenés";
let texto_busqueda = "RUEDA";
  
let resultado = texto.indexOf(texto_busqueda);

En este caso el resultado va a ser -1. La solución entonces es transformar ambas cadenas con el método toLowerCase() para evaluar todo como minúsculas y no tener el problema del case sensitive.

Vamos a crear una función para buscar y la agregamos a nuestro código GAS:

function buscarEnTexto(p_texto, p_texto_busqueda){
  return p_texto.toLowerCase().indexOf(p_texto_busqueda.toLowerCase()) > -1;
}

Esto nos devuelve true si encontró coincidencias y false si no fue así.

Por último vamos a agregar la función para buscar registros en el menú: 

function getMenuFiltrado(p_texto_busqueda){
  
  let menu = getMenu();
  let menu_filtrado = [];
  
  for(let i = 0; i < menu.length; i++){
    if(buscarEnTexto(menu[i][1], p_texto_busqueda) || buscarEnTexto(menu[i][2], p_texto_busqueda) || buscarEnTexto(menu[i][3], p_texto_busqueda)){
      menu_filtrado.push(menu[i]);
    }
  }
    
  return menu_filtrado;
  
}

Como podemos ver en el código, recuperamos el menú de la función getMenu() y buscamos coincidencias en las columnas 1, 2 y 3; que son las que corresponden a nombre, descripción y categoría respectivamente.

Bueno, ahora sólo nos falta agregar en el frontend el texto que va a viajar al parámetro de getMenuFiltrado() para buscar dentro del mismo. Así que dentro de data:

texto_busqueda: ''

Después el método para realizar la búsqueda filtrada:

getMenuFiltrado: function(){
                    let that = this;
                    that.menu = [];
                    google.script.run.withSuccessHandler(function(menu_filtrado){
                        that.menu = [];
                        if(menu_filtrado.length > 0){
                            for(let i = 0; i < menu_filtrado.length; i++)
                            {
                                that.menu.push({
                                    ID:  menu_filtrado[i][0],
                                    nombre: menu_filtrado[i][1],
                                    descripcion: menu_filtrado[i][2],
                                    categoria: menu_filtrado[i][3]
                                });
                            }
                         }else{
                             alert('La búsqueda no ha traído resultado');
                         }
                    }).getMenuFiltrado(that.texto_busqueda);
}

También vamos a agregar el texto de búsqueda, y las acciones para recuperar el menú dentro del código HTML, que lo reemplazamos por el código anterior de la fila de búsqueda:

<tr>
                    <td colspan="3">
                        <input type="text" class="form-control" placeholder="Buscar" v-model="texto_busqueda" />
                    </td>
                    <td>
                        <button type="button" class="btn btn-secondary" v-on:click="getMenuFiltrado()"> Buscar </button>
                        <button type="button" class="btn btn-secondary" v-on:click="getMenu()"> Traer todo </button>
                    </td>
</tr>

Y finalmente el HTML que se genera dinámicamente, tanto de recuperar el menú, como filtrar el mismo:

<tr v-for="item in menu">
                    <td>
                        <input type="text" class="form-control" placeholder="Ingrese el nombre" v-model="item.nombre" />
                    </td>
                    <td>
                        <input type="text" class="form-control" placeholder="Ingrese una descripción" v-model="item.descripcion" />
                    </td>
                    <td>
                        <select class="form-control" v-model="item.categoria">
                            <option value=""> Ingrese la categoría </option>
                            <option v-for="c in categorias" v-bind:value="c[0]"> {{ c[0] }} </option>
                        </select>
                    </td>
                    <td>
                        <button type="button" class="btn btn-primary"> Modificar </button>
                        <button type="button" class="btn btn-danger"> Eliminar </button>
                    </td>
</tr>

Teniendo en cuenta nuestra hoja de cálculos «Menu»:

Podemos traer el menú completo:

O bien, buscar:

El código GAS quedará de este modo:

const SS = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/[ID-DE-TU-HOJA-DE-CALCULOS]/edit#gid=0");

function doGet() {
  let layout = HtmlService.createTemplateFromFile("layout");
  return layout.evaluate();
}

function getCategorias(){
  
  let ws = SS.getSheetByName("Categorias");
  
  let data = ws.getRange(
    2, 
    1, 
    ws.getLastRow() - 1, 
    1
  ).getValues();
      
  return data;
  
}

function addItemMenu(p_item){
  
  let ws = SS.getSheetByName("Menu");
  
  let date = new Date();
  
  ws.appendRow([
	getIdDinamico(),
    p_item.nombre,
    p_item.descripcion,
    p_item.categoria,
    date,
    date
  ]);
  
}
    
function getIdDinamico(){
        
    return new Date().getTime() + '-' + (Math.floor(Math.random() * 100) + 1);
    
}

function getMenu(){
  
  let ws = SS.getSheetByName("Menu");
  
  let data = ws.getRange(
    2, 
    1,
    ws.getLastRow() - 1, 
    4
  ).getValues();
      
  return data;
    
}

function getMenuFiltrado(p_texto_busqueda){
  
  let menu = getMenu();
  let menu_filtrado = [];
  
  for(let i = 0; i < menu.length; i++){
    if(buscarEnTexto(menu[i][1], p_texto_busqueda) || buscarEnTexto(menu[i][2], p_texto_busqueda) || buscarEnTexto(menu[i][3], p_texto_busqueda)){
      menu_filtrado.push(menu[i]);
    }
  }
    
  return menu_filtrado;
  
}

function buscarEnTexto(p_texto, p_texto_busqueda){
  return p_texto.toLowerCase().indexOf(p_texto_busqueda.toLowerCase()) > -1;
}

Mientras que el HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">    
    <title> Prueba de Google App Script </title>    
    <!-- Bootstrap -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <!-- Vue.js -->
    <script src="https://cdn.jsdelivr.net/npm/vue@2.5.16/dist/vue.js"></script>
  </head>
  <body>
    
    <div id="app" class="container">
        
        <h1> Menú </h1>
        
        <table class="table">
            <thead>
                <tr>
                    <th> Nombre </th>
                    <th> Descripción </th>
                    <th> Categoría </th>
                    <th> </th>
                </tr>
            </thead>
            <tbody>                                
                <tr>
                    <td>
                        <input type="text" class="form-control" placeholder="Ingrese el nombre" v-model="item_menu_temp.nombre" />
                    </td>
                    <td>
                        <input type="text" class="form-control" placeholder="Ingrese una descripción" v-model="item_menu_temp.descripcion" />
                    </td>
                    <td>
                        <select class="form-control" v-model="item_menu_temp.categoria">
                            <option value=""> Ingrese la categoría </option>
                            <option v-for="c in categorias" v-bind:value="c[0]"> {{ c[0] }} </option>
                        </select>
                    </td>
                    <td>
                        <button type="button" class="btn btn-primary" v-on:click="addItemMenu()" v-bind:disabled="!validarItemMenu(item_menu_temp)"> Agregar </button>
                    </td>
                </tr>
                <tr>
                    <td colspan="3">
                        <input type="text" class="form-control" placeholder="Buscar" v-model="texto_busqueda" />
                    </td>
                    <td>
                        <button type="button" class="btn btn-secondary" v-on:click="getMenuFiltrado()"> Buscar </button>
                        <button type="button" class="btn btn-secondary" v-on:click="getMenu()"> Traer todo </button>
                    </td>
                </tr>
                <tr v-for="item in menu">
                    <td>
                        <input type="text" class="form-control" placeholder="Ingrese el nombre" v-model="item.nombre" />
                    </td>
                    <td>
                        <input type="text" class="form-control" placeholder="Ingrese una descripción" v-model="item.descripcion" />
                    </td>
                    <td>
                        <select class="form-control" v-model="item.categoria">
                            <option value=""> Ingrese la categoría </option>
                            <option v-for="c in categorias" v-bind:value="c[0]"> {{ c[0] }} </option>
                        </select>
                    </td>
                    <td>
                        <button type="button" class="btn btn-primary"> Modificar </button>
                        <button type="button" class="btn btn-danger"> Eliminar </button>
                    </td>
                </tr>
            </tbody>
        </table>
        
    </div>
    
    <script>
        
        new Vue({
            el: '#app',            
            data: function(){
                return {
                    categorias: [],
                    item_menu_temp: {
                        nombre: '',
                        descripcion: '',
                        categoria: ''
                    },
                    menu: [],
                    texto_busqueda: ''
                }
            },
            created: function(){
                this.getCategorias();
            },
            methods: {
                getCategorias: function(){
                    let that = this;
                    google.script.run.withSuccessHandler(function(categorias){
                        that.categorias = categorias;
                    }).getCategorias();
                },
                addItemMenu: function(){
                    let that = this;
                    google.script.run.withSuccessHandler(function(){
                        alert("El registro se ha guardado en el menú.");
                        that.item_menu_temp.nombre = "";
                        that.item_menu_temp.descripcion = "";
                        that.item_menu_temp.categoria = "";
                    }).addItemMenu(that.item_menu_temp);
                },
                validarItemMenu: function(p_item){
                    return (
                        p_item.nombre.split(" ").join("") != "" &&
                        p_item.descripcion.split(" ").join("") != "" &&
                        p_item.categoria.split(" ").join("") != ""
                    );
                },
                getMenu: function(){
                    let that = this;
                    that.menu = [];
                    google.script.run.withSuccessHandler(function(menu){
                        that.menu = [];
                        for(let i = 0; i < menu.length; i++)
                        {
                            that.menu.push({
                                ID:  menu[i][0],
                                nombre: menu[i][1],
                                descripcion: menu[i][2],
                                categoria: menu[i][3]
                            });
                        }
                    }).getMenu();
                },
                getMenuFiltrado: function(){
                    let that = this;
                    that.menu = [];
                    google.script.run.withSuccessHandler(function(menu_filtrado){
                        that.menu = [];
                        if(menu_filtrado.length > 0){
                            for(let i = 0; i < menu_filtrado.length; i++)
                            {
                                that.menu.push({
                                    ID:  menu_filtrado[i][0],
                                    nombre: menu_filtrado[i][1],
                                    descripcion: menu_filtrado[i][2],
                                    categoria: menu_filtrado[i][3]
                                });
                            }
                         }else{
                             alert('La búsqueda no ha traído resultado');
                         }
                    }).getMenuFiltrado(that.texto_busqueda);
                }
            }
        });   
        
    </script>
    
    
  </body>
</html>

Por el momento los botones de Modificar y Eliminar no tendrán acción alguna, lo veremos en las próximas publicaciones.

Saludos!

Anterior: Google Apps Script, parte 5: Agregar filas en una hoja de cálculos

Siguiente: Google Apps Script, parte 7: Modificar filas de una hoja de cálculos