copy data matches from one sheet to another within a specific row range Google apps script

Question

This is what I want to do:

I have a source sheet called "BD Visitas". This sheet contains some data, which will later be the data that I want to copy to another sheet.

I have a target sheet called "Ficha mascota". This sheet contains a range of rows where I want to paste the data from the source sheet "BD Visitas".

The issue is the following:

On the destination sheet, in cell "D18" the name of the pet is written.

Take the value of this cell, perform a search in column B of the source sheet "BD Visitas" and look for the name matches.

After finding the matching rows, get the last 15 matches. That is, from the last row to the first row (from bottom to top).

When you get the last 15 matching rows, then copy the columns C, G, H, I (source sheet) and paste the values ​​in the range of rows 39 to 53, in the columns C, D, G, M of the destination sheet "Ficha mascota".

However, it must be copied in reverse order, that is, the last match of the source sheet must be copied to row 39 of the destination sheet. The penultimate match of the source sheet must be copied to row 40 of the target sheet. And so on. Therefore, in row 53 of the target sheet the first match within the range of 15 matches will be pasted.

The work done that I have is the following:

function get15lastRowsBDvisitasToFichaMascota() {

  const libro = SpreadsheetApp.getActiveSpreadsheet();
  const hojaOrigen = libro.getSheetByName('BD visitas');
  const hojaDestino = libro.getSheetByName('Ficha mascota');

  // Rango destino

  var rowsDestino = hojaDestino.getRange("C39:U53");
  var rowsDestinoR = rowsDestino.getNumRows();
  var rowsDestinoUF = rowsDestino.getLastRow();

  // Coincidencia nombre mascota

  var nombreVisor = hojaDestino.getRange('D18').getValue();
  var tablaDeBusquedaNombre = hojaOrigen.getRange('B7:J').getValues();
  var listaDeBusquedaNombre = tablaDeBusquedaNombre.map(function(row){return row[0]});
  var busquedaNombre = listaDeBusquedaNombre.indexOf(nombreVisor);

  var coincidenciaNombre = tablaDeBusquedaNombre[busquedaNombre][0];
  var fechaVisita = tablaDeBusquedaNombre[busquedaNombre][1];
  var motivoVisita = tablaDeBusquedaNombre[busquedaNombre][5];
  var sintomasVisita = tablaDeBusquedaNombre[busquedaNombre][6];
  var observacionesVisita = tablaDeBusquedaNombre[busquedaNombre][7];

  // Rangos copia y pega

  var obj = [
    { src: fechaVisita, dst: "C" + rowsDestinoUF }, // Fecha
    { src: motivoVisita, dst: "D" + rowsDestinoUF }, // Motivo
    { src: sintomasVisita, dst: "G" + rowsDestinoUF }, // Sintomas
    { src: observacionesVisita, dst: "M" + rowsDestinoUF }, // Observaciones
  ];
  Logger.log(obj);

  obj.forEach(({src, dst}) => hojaOrigen.getRange(src).copyTo(hojaDestino.getRange(dst), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false));  

}

But I am stuck. I do not know how to call to paste the fixed ones of the source sheet within the range of rows of the destination sheet.

I also don't know how to get the rows to be pasted on the destination sheet in reverse order.

I am new to coding with google apps script and javascript. I have learned a lot in a few days and I have been able to do many things, however, my capacity is still limited and I miss a lot.

Any help is well appreciated. Thannk you very much. Sorry for my poor English.

Answer 1

It seems you're trying to search for the pet name using indexOf(), but that would just bring the first match and, from what I understand, you want to get up to 15 matches.

One way of doing that would be:

1. Iterate over the rows of the source sheet and check if the name in column B matches the one specified by cell D18 in the destination sheet. Since you want to get them in reverse order, you could start from the last row and go up until you get to the desired number (15) or until you check all values.
2. When there is a match, you can get the values from the columns you want and store them in an array.
3. Next, you write the values to the destination spreadsheet. Since your destination columns are not all consecutive and supposing you don't want to delete any existing data in the middle of the range, using setValues() probably wouldn't help in this case. One alternative would be to write the values one by one using setValue(), but that isn't very efficient. Perhaps a better way would be to write the values column by column, as the rows are consecutive.

The above can be implemented like this:

function get15lastRowsBDvisitasToFichaMascota() {

  const libro = SpreadsheetApp.getActiveSpreadsheet();
  const hojaOrigen = libro.getSheetByName('BD visitas');
  const hojaDestino = libro.getSheetByName('Ficha mascota');

  // Rango destino

  var rowsDestino = hojaDestino.getRange("C39:U53");
  var rowsDestinoR = rowsDestino.getNumRows();
  var rowsDestinoUF = rowsDestino.getLastRow();

  // Coincidencia nombre mascota

  var nombreVisor = hojaDestino.getRange('D18').getValue();
  var tablaDeBusquedaNombre = hojaOrigen.getRange('B7:J' + hojaOrigen.getLastRow()).getValues();

  var maxMascotas = 15;
  var dadosMascotas = [];
  for (var i = tablaDeBusquedaNombre.length - 1; dadosMascotas.length <= maxMascotas && i >= 0; i--) {
    if (tablaDeBusquedaNombre[i][0] === nombreVisor) {
      var fechaVisita = tablaDeBusquedaNombre[i][1];
      var motivoVisita = tablaDeBusquedaNombre[i][5];
      var sintomasVisita = tablaDeBusquedaNombre[i][6];
      var observacionesVisita = tablaDeBusquedaNombre[i][7];
      dadosMascotas.push([fechaVisita, motivoVisita, sintomasVisita, observacionesVisita]);
    }
  }

  if (dadosMascotas.length === 0) {
    return;
  }

  var hojaDestinoCols = ['C', 'D', 'G', 'M'];
  var primeraRowDestino = rowsDestino.getRow()
  for (var i = 0; i < hojaDestinoCols.length; i++) {
    // Get the values per column
    var colValues = dadosMascotas.map(function (row) { return [row[i]] });

    // Write the column values starting from the first row of the destination range
    hojaDestino.getRange(
      hojaDestinoCols[i] + primeraRowDestino +
      ':' +
      hojaDestinoCols[i] + (primeraRowDestino + dadosMascotas.length - 1))
      .setValues(colValues);
  }
}