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

En la publicación anterior habíamos visto como listar las filas de una hoja de cálculos, en esta ocasión vamos a ver cómo modificar esos registros.

Método setValue()

Para cambiar el valor de una celda debemos hacer una combinación de los métodos getRange() y setValue(). Al primero debemos pasarle la fila y la columna, y al segundo el nuevo valor de la celda.

Por ejemplo en la siguiente imagen:

Supongamos que queremos modificar el nombre del primer ítem, la descripción nueva debería ser «Agua mineral con gas «.

En ese caso el proceso sería el siguiente:

let ws = SS.getSheetByName("Menu");
ws.getRange(2, 3).setValue("Agua mineral con gas");

Como vemos en el código el primer parámetro que le pasamos al método getRange() es la fila, es decir la 2, es cierto que es el primer registro, pero la primer fila está ocupada con las cabeceras. El segundo parámetro es el 3, que son las columnas, en este caso la de descripción.

Y por último encadenamos el método setValue(), y le pasamos como parámetro el nuevo valor de la celda.

El problema que se nos presenta ahora es que podemos ver las coordenadas de la celda, contando filas y columnas, sin embargo ¿Cómo debería hacer nuestro sistema para averiguar cuáles son esas coordenadas y así cambiar los nuevos valores ingresados por el usuario?

Bueno, en primer lugar tenemos una columna donde guardamos el ID del ítem, por tanto deberíamos buscar ese valor dentro del array y al encontrarlo podemos saber cuál es la fila que hay que cambiar.

Método map()

Una forma bastante amigable de hacerlo es mediante el método map(). Este método nos permite crear un array a partir de una columna.

Por ejemplo, recuperamos el array que contiene los datos del menú:

Recordemos lo que hacía esta función:

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

La misma nos devolvía un array de arrays con la información del menú.

Lo que nos interesa ahora es hacer una búsqueda por ID, para así calcular la posición de la fila a modificar. Podemos usar el método map() de la siguiente forma:

let menu = getMenu();
  
let ides = menu.map(function(e){
  return e[0];
});
  
Logger.log(ides);

Vemos cómo a este método le pasamos una función donde tenemos que devolver el número de columna. Como es la primera la que nos interesa, ponemos 0 (cero).

El resultado será un array con todos los ID:

[
	1601229551655-76, 
	1601229551655-80, 
	1601160613399-45, 
	1601229451944-36, 
	1601229485750-56, 
	1601160634457-32, 
	1601229404976-83
]

Método indexOf() de un Array

En la publicación pasada habíamos visto un método de un String llamado indexOf(). Los array también tienen un método con el mismo nombre al cual le pasamos un valor, y de encontrarlo dentro de la colección nos devolverá la posición del mismo.

Si por ejemplo probamos con el valor: ‘1601229551655-76’, este método nos devolverá 0 (cero), porque es la primer posición.

let menu = getMenu();
  
let ides = menu.map(function(e){
  return e[0];
});
  
Logger.log(ides.indexOf('1601229551655-76'));

Si en cambio le pasamos: ‘1601229404976-83’ Nos va a devolver: 6 (seis), que es la posición que ocupa.

Cuando el valor no se encuentra dentro del array nos va a devolver -1 (menos uno)

Sabiendo esto, podemos crear una función a la cual le pasamos como parámetro el id del registro que queremos modificar para que nos devuelva el número de fila:

function getFilaPorId(p_id){
    
  let menu = getMenu();
  
  let ides = menu.map(function(e){
    return e[0];
  });
    
  let fila = ides.indexOf(p_id);
  
  if(fila > -1){
    fila += 2;
  }
  
  return fila;  
  
}

Una aclaración que debo hacer, si es que no pudiste verlo, es la siguiente línea:

if(fila > -1){
  fila += 2;
}

En realidad la función indexOf() nos devuelve la posición de un array encontrado. Por ejemplo el ID: «1601229551655-76» como es la primer posición es 0 (cero).

Sin embargo en la hoja de cálculos la fila es la número 2 (la primer fila está ocupada por las cabeceras):

Por eso debemos sumarle 2, porque nos devuelva el número de fila correcto.

Es decir, el primer registro comienza desde 0 (cero) al ser un array, pero en la hoja de cálculos los registros comienzan en la fila 2, así que sumamos:

0 (índice del array) + 2 = 2 (celda que debemos modificar)

Si en cambio el registro a modificar fuese el último, en el array es el número 6 (seis), pero en la hoja de cálculos es el 8 (ocho) El mismo proceso sumando de a dos:

6 (índice del array) + 2 = 8 (celda que debemos modificar)

Bueno, finalmente vamos a crear la función dentro de GAS que nos permita modificar la fila:

function updateItemMenu(p_item){
  
  let ws = SS.getSheetByName("Menu");
  
  let fila = getFilaPorId(p_item.ID);
  
  if(fila > -1){
    ws.getRange(fila, 2).setValue(p_item.nombre);
    ws.getRange(fila, 3).setValue(p_item.descripcion);
    ws.getRange(fila, 4).setValue(p_item.categoria);
    return true;
  }else{
    return false;
  }
  
}

Como vemos en el código, le pasamos como parámetro el ítem a modificar, que desde el frontend será un JSON. Luego modificamos las filas 2, 3 y 4; que son las de nombre, descripción y categoría respectivamente.

Vamos al frontend y agregamos el siguiente método:

updateItemMenu: function(p_item){
                    google.script.run.withSuccessHandler(function(){
                        alert("El registro se ha guardado en el menú.");
                    }).updateItemMenu(p_item);
}

Y por último modificamos el ítem en el HTML:

<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" v-on:click="updateItemMenu(item)" v-bind:disabled="!validarItemMenu(item)"> Modificar </button>
                        <button type="button" class="btn btn-danger"> Eliminar </button>
                    </td>
</tr>

Vemos que al botón de modificar agregamos el método:

<button type="button" class="btn btn-primary" v-on:click="updateItemMenu(item)"> Modificar </button>

El código finalmente será el siguiente:

Código GAS:

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 updateItemMenu(p_item){
  
  let ws = SS.getSheetByName("Menu");
  
  let fila = getFilaPorId(p_item.ID);
  
  if(fila > -1){
    ws.getRange(fila, 2).setValue(p_item.nombre);
    ws.getRange(fila, 3).setValue(p_item.descripcion);
    ws.getRange(fila, 4).setValue(p_item.categoria);
    return true;
  }else{
    return false;
  }
  
}
    
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;
}

function getFilaPorId(p_id){
    
  let menu = getMenu();
  
  let ides = menu.map(function(e){
    return e[0];
  });
    
  let fila = ides.indexOf(p_id);
  
  if(fila > -1){
    fila += 2;
  }
  
  return fila;  
  
}

Código 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" v-on:click="updateItemMenu(item)" v-bind:disabled="!validarItemMenu(item)"> 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);
                },
                updateItemMenu: function(p_item){
                    google.script.run.withSuccessHandler(function(){
                        alert("El registro se ha guardado en el menú.");
                    }).updateItemMenu(p_item);
                }
            }
        });   
        
    </script>
    
    
  </body>
</html>

En la siguiente publicación vamos a ver cómo es eliminar, para así finalizar el ABM completo.

Saludos!

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

Siguiente: Google Apps Script, parte 8: Eliminar filas en una hoja de cálculos