google-apps-script - 根据条件检索最新的数据行

Question

我正在使用我的谷歌脚本根据条件从我的谷歌表中检索最新的数据行。下面我的代码能够检索数据，但是它选择最旧的数据而不是最新的数据。

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;
}

当前返回第一行，它没有返回我突出显示的较新行。

标签： google-apps-scriptgoogle-sheets

Answer 1

获取最后选择的行

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);
}

测试数据：

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

结果：

一个	乙	C	D	乙	F	G	H
2	20	16	17	10	7	4	5

这为输入数据提供了一个简单的用户界面：

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');
  }
}

一个	乙	C	D	乙	F	G	H
2	20	16	17	10	7	4	5

这将在对话框中显示数据：

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');
  }
}

数据：

我的回答的唯一问题是，我无法告诉您如何确定哪一行是最旧的。行没有年龄，除非您提供其他定义，否则我无法提供该功能。

看来您现在想要最接近工作表底部的最后一个。

找到最后一行

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');
  }
}

数据：