CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Erik
Erik

Reputation: 14750

How can I retrieve rows by with the latest date by using Google SpreadSheet API?

I have a big Google SpreadSheet document that stores some data and I need to use Google SpreadSheet REST API to retrieve particular rows from it.

Let's say we have the following test data:

ID | Name | Date
-----------------
1  | Erik | 07.06.2017
2  | Anna | 07.06.2017
3  | Kirk | 07.06.2017
4  | Erik | 06.06.2017
5  | Anna | 06.06.2017
6  | Kirk | 06.06.2017
....

I need to retrieve rows with the latest date such as:

1  | Erik | 07.06.2017
2  | Anna | 07.06.2017
3  | Kirk | 07.06.2017

How can I achieve this ?

EDIT:

From the official doc I've found that I can retrieve a range by the following request:

GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!A1:D5

But this not suitable for me because I need filtering by max date :(

Upvotes: 1

Views: 542

Answers (1)

Kresimir Pendic
Kresimir Pendic

Reputation: 3614

you can use event object that tracks every change in sheet to track last modified and 'copy' it to some fixed possiion in sheet like I did in this short video..

my example sheet and data that is last modified

you have to paste this in your Tools -> Script Editor

function onEdit(e) {
  var s = SpreadsheetApp.getActiveSheet(); 
  var rng = e.range;
  var ix = rng.getRow();
  var X = s.getRange( ix, 1, 1, 3 );
  s.getRange( 3, 5, 1, 3 ).copyTo( s.getRange( 4, 5 ) ); // SECOND LAST TO BUTTOM
  s.getRange( 2, 5, 1, 3 ).copyTo( s.getRange( 3, 5 ) ); // SECOND LAST TO BUTTOM
  X.copyTo( s.getRange( 2, 5 ) ); // LAST ON TOP!  
}

.. and then you can pull your data via REST like:

GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!E2:G4

hope that helps :)

Upvotes: 2

Related Questions