Query function to get last row in Google Sheets

Question

I have a sheet where data is updated daily.

however, the last integer often ends in between row 131 to 133.

how do I run a query function to display the last integer in row 1 column C?

I cannot do a simple =A131 on C1 because it can be on A133 the next day.

images: https://i.stack.imgur.com/eJAAF.jpg

Your question is very confusing. What do you mean by the end of integer on line 131 ~ 133? What is the exact purpose of your query? Row 1 column C seems pretty random. In my understanding, you want to write a query that find the last element in each column, but I cannot make sure. You would want to make your question clear to get an answer. — Jin, Aug 18 '19 at 01:12
"you want to write a query that find the last element in each column" yes that's correct. — sheetsquestions, Aug 18 '19 at 01:58
Ah, it seems I misunderstood the display part. Yeah it would be simple. I will post an answer. — Jin, Aug 18 '19 at 02:53

score 3 · Answer 1 · answered Aug 18 '19 at 08:28

3

paste in C1 cell:

=QUERY(A1:A, "offset "&COUNTA(A1:A)-1, 0)

answered Aug 18 '19 at 08:28

player0

124,011
12
67
124

I interpreted the OPs data differently. As I understand it, Column A is populated (from another source?) with integers in the first 130-odd rows and, thereafter, the cells contain text and a variety of other stuff, possibly including dates but NOT including integers. The OP's goal (as I understand it) is to identify the last row that contains an integer. In any event, the OP's "demo" data includes text and integers which are not addressed by your answer. – Tedinoz Aug 24 '19 at 02:48
1

FWIW, it was only today that I realised that I had previously misunderstood the issue, and edited my own answer accordingly. – Tedinoz Aug 24 '19 at 04:01

Tedinoz · Answer 2 · 2019-08-24T02:29:35.003

This is a simplified version of my original code.

The value of the last integer and the row number are output for user information.

The code copes with dates, text, pseudo-value text (37*2), and decimals.

function so5754079102() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheetname = "Sheet1";
  var sheet = ss.getSheetByName(sheetname);
  var Avals = ss.getRange("A1:A").getValues();
  var Alast = Avals.filter(String).length;
  Logger.log("Last row = "+Alast);
  var data = sheet.getRange(1,1,Alast,1).getValues();
  for (var i = Alast-1;i>=0;i--){
    Logger.log("i = "+i+", data 1 ="+data[i][0]+", and parse data 1 = "+parseInt(data[i][0], 10));
    if (data[i][0] === parseInt(data[i][0], 10)){
      break; 
    }
  }
  sheet.getRange("D1").setValue(+(i+1));
  sheet.getRange("D2").setValue(data[i][0]);
}

Screenshot

Query function to get last row in Google Sheets

2 Answers2