Retrieve latest row of data based on a condition

Question

I am using my google scripts to retrieve the most recent row of data from my google sheet based on a condition. My code bellow is able to retrieve data however it selects the oldest data and not the newest.

function retreiveData(number){
  var url = "";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Raw Data");
  var data = ws.getRange(1,1, ws.getLastRow(), ws.getLastColumn()).getDisplayValues();
  var dataValues = [];
  var find = 204; //Value I am trying to find
  var filterData = data.filter(
    function(r){
      if(r[3] == find){ //the condition is in the 4th column (column D)
        var i = 4;//begins in the 5th column (column E) 
        while(i < 55){//My spreadsheet has 55 columns
          Logger.log(r[i]);
          dataValues.push(r[i]);
          i++;
        }
      }
    }
  )
  
  var k = 0;
  while(k < 51){
    Logger.log(k +  " " + dataValues[k]);
    k++;
  }

  return dataValues;
}

Currently returning the top row, it is not returning the newer row that I have highlighted.

Answer 1

Getting the last selected row

function retreiveData1(number=17) {
  var url = "url";
  var ss = SpreadsheetApp.openByUrl(url);
  var sh = ss.getSheetByName("Raw Data");
  var dsh = ss.getSheetByName('Sheet2')
  let dvs = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getDisplayValues().filter(r => r[3] == number);
  dvs = [dvs[dvs.length - 1]];
  dsh.clear();
  dsh.getRange(1,1,dvs.length,dvs[0].length).setValues(dvs);
}

Test Data:

24	21	9	24	16	7	12	13
12	19	9	19	20	7	11	18
19	18	6	1	19	16	1	16
2	0	4	19	8	12	8	20
19	19	8	24	8	0	1	18
22	6	9	2	17	18	5	20
22	13	7	1	9	15	24	14
20	7	8	21	11	2	10	22
4	11	12	21	13	6	9	22
12	19	23	6	8	9	5	12
3	18	11	17	7	12	3	22
19	19	11	3	13	15	4	12
23	1	10	16	20	11	5	20
17	20	14	13	4	13	15	1
8	4	22	8	13	19	24	3
4	19	24	13	11	9	19	9
3	3	14	7	1	6	24	16
22	0	21	7	16	16	7	16
2	20	16	17	10	7	4	5
23	18	17	6	17	24	13	11
16	3	2	5	22	4	20	6
14	8	8	0	15	22	12	20
4	6	3	5	20	12	11	11
17	13	8	15	9	17	15	21
0	15	19	3	4	14	16	20

Results:

A	B	C	D	E	F	G	H
2	20	16	17	10	7	4	5

This provides a simple User Interface for entering data:

function retreiveData3() {
  const url = "";
  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1iUA-rv6h7y0Q562XVGWZFwzTNB-LNuHZbkS3pTmA99M/edit#gid=1280165640");
  const sh = ss.getSheetByName("Raw Data");
  const dsh = ss.getSheetByName('Sheet2');
  const r = SpreadsheetApp.getUi().prompt('Enter number to find', 'Search Dialg', SpreadsheetApp.getUi().ButtonSet.OK_CANCEL)
  if (r.getSelectedButton() == SpreadsheetApp.getUi().Button.OK) {
    let number = parseInt(r.getResponseText());
    let dvs = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getDisplayValues().filter(r => r[3] == number);
    dvs = [dvs[dvs.length - 1]];
    dsh.clear();
    if (dvs && dvs.length > 0) {
      dsh.getRange(1, 1, dvs.length, dvs[0].length).setValues(dvs);
      ss.toast(`${dvs.length}`,'Matches')
    } else {
      ss.toast('None','Matches');
    }

  } else {
    ss.toast('Process Cancelled');
  }
}

A	B	C	D	E	F	G	H
2	20	16	17	10	7	4	5

This displays the data in a dialog:

function retreiveData2() {
  const url = "";
  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1iUA-rv6h7y0Q562XVGWZFwzTNB-LNuHZbkS3pTmA99M/edit#gid=1280165640");
  const sh = ss.getSheetByName("Raw Data");
  const r = SpreadsheetApp.getUi().prompt('Enter number to find', 'Search Dialg', SpreadsheetApp.getUi().ButtonSet.OK_CANCEL)
  if (r.getSelectedButton() == SpreadsheetApp.getUi().Button.OK) {
    let number = parseInt(r.getResponseText());
    let dvs = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getDisplayValues().filter(r => r[3] == number);
    dvs = [dvs[dvs.length - 1]];
    if (dvs && dvs.length > 0) {
      let h = "ABCDEFGH".split("").map(e => `<th>${e}</th>`).join('');
      let html = `<style> td,th{border:1px solid black}</style><table>${h}`;
      dvs.forEach((r, i) => {
        html += '<tr>';
        r.forEach((c, j) => {
          html += `<td>${c}</td>`;
        });
        html += '</tr>';
      });
      html += '</table>';
      Logger.log(html);
      SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), "Display Dialog");
      ss.toast(`${dvs.length}`, 'Matches')
    } else {
      ss.toast('None', 'Matches');
    }

  } else {
    ss.toast('Process Cancelled');
  }
}

Data:

The only issue with my answer is that I can't tell from you question how to determine which row is the oldest. Rows don't have age unless you have some other definition to offer then I can't provide that functionality.

It seems that you now want the last one nearest the bottom of the sheet.

Finding the Last Row

function retreiveData() {
  const url = "";
  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1iUA-rv6h7y0Q562XVGWZFwzTNB-LNuHZbkS3pTmA99M/edit#gid=1280165640");
  const sh = ss.getSheetByName("Raw Data");
  const r = SpreadsheetApp.getUi().prompt('Enter number to find', 'Search Dialg', SpreadsheetApp.getUi().ButtonSet.OK_CANCEL)
  if (r.getSelectedButton() == SpreadsheetApp.getUi().Button.OK) {
    let number = parseInt(r.getResponseText());
    let dvs = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getDisplayValues().filter(r => r[3] == number);
    dvs = [dvs[dvs.length - 1]];//this selects the last row in an array of spreadsheet data and places it into an array so that it's two dimensional again.
    if (dvs && dvs.length > 0) {
      let h = "ABCDEFGH".split("").map(e => `<th>${e}</th>`).join('');
      let html=`<style> td,th{border:1px solid black}</style><table>${h}`;
      dvs.forEach((r,i) => {
          html += '<tr>';
        r.forEach((c,j) => {
          html+= `<td>${c}</td>`;
        });
        html += '</tr>';
      });
      html+= '</table>';
      Logger.log(html);
      SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html),"Display Dialog");
      ss.toast(`${dvs.length}`,'Matches')
    } else {
      ss.toast('None','Matches');
    }

  } else {
    ss.toast('Process Cancelled');
  }
}

Data: