CODEWITHSUNDEEP
javascriptjqueryjsongoogle-sheets
Corentin Le Fur
Corentin Le Fur

Reputation: 338

Get last updated date of a Google Spreadsheets as JSON endpoint

I have a public Google Spreadsheets and I'd like to get the updated date of it with the JSON endpoint in Javascript.

Here is the JSON endpoint : https://spreadsheets.google.com/feeds/list/1hrae_CqzDZjXR8CUs8d7_sqpit5WUk7sPv94nyHY1A4/1/public/basic?alt=json

You may see a date in feed.updated.$t (currently it is 2020-03-17T13:59:25.968Z : 17th march at 13:59).

But when I want to get it with Javascript, I always get the current date and time, and I don't understand why.

const elDate = document.getElementById('date');
const elFormattedDate = document.getElementById('formatted-date');

$.getJSON('https://spreadsheets.google.com/feeds/list/1hrae_CqzDZjXR8CUs8d7_sqpit5WUk7sPv94nyHY1A4/1/public/basic?alt=json', function(data) {
    var date = data.feed.updated.$t;
    var formattedDate = new Date(date);
    elDate.innerHTML = date;
    elFormattedDate.innerHTML = formattedDate;
});
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<div id="banner-message">
  <ul>
    <li><b>Date</b> : <span id="date"></span></li>
    <li><b>Formatted date</b> : <span id="formatted-date"></span></li>
  </ul>
</div>

Upvotes: 0

Views: 475

Answers (3)

Corentin Le Fur
Corentin Le Fur

Reputation: 338

I answer my own question to make it clear.

Explanation

Thank's to @huytc's answer, it became clear that there's no differences between calling the JSON endpoint from a browser than from a Javascript script. The difference in the dates was simply due to browser cache.

So to be clear, this JSON endpoint will always get you the current date, and not the date in which the spreadsheet was last updated.

Workaround with an App Script

Despite the fact that @Iamblichus's workaround with the Google Drive API seems a lot cleaner, I find it a bit "overkill" just to get the last updated date, especially because it requires not only a API key but the all oAuth process.

As @huytc suggests, I used a trigger in an App Script to make it work. I created a new Sheet called "Informations", and used the following script :

function onEdit(e) {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = s.getSheetByName('Informations');
  var date = new Date();
  var range = sheet.getRange(2, 1);
  range.setValue(date);
}

This gist helped me a lot.

References :

Upvotes: 1

Iamblichus
Iamblichus

Reputation: 19309

As @huytc said, every time you make a request to this endpoint, the updated time gets updated, so you will always get the current date, not the date in which the spreadsheet was last updated.

Workaround: Using Drive API:

In order to get the date in which this spreadsheet was last updated, you can use the Revisions or the Changes collection from Drive API (see the difference between both here).

You can, for example, list all the Revisions associated with this file via Revisions.list. You just have to provide the id of the file in your API request. As a response, you will get something along the following lines:

{
  "revisions": [
    {
      "modifiedTime": datetime,
      // ... rest of revision 1 properties
    },
    {
      "modifiedTime": datetime,
      // ... rest of revision 2 properties
    },
    // ... rest of revisions
    {
      "modifiedTime": datetime,
      // ... rest of last revision properties
    }    
  ],
  // ... rest of properties
}

The modifiedTime of the last revision in the response is the date you're looking for.

Reference:

Upvotes: 2

huytc
huytc

Reputation: 1141

Every time you call the endpoint, the field feed.updated.$t gets updated (try refreshing a few times to see for yourself). That's why you're getting the current date. You did nothing wrong, it's just the way Google Sheets API works.

Upvotes: 3

Related Questions