How to get the last-modified date of the current/active Spreadsheet?

[Faustino Rodriguez]

I need to get the last modified date of the current spreadsheet (from a time-based trigger call) using GAS code (Sheets add-on)

The add-on script related scopes are restricted to 

spreadsheets.currentonly
drive.file

1. I cannot find a SpreadsheetApp function that returns that last modified date

- I guess I can use some sort of checksum function to find of content has changed, but that is cumbersome and expensive

2. I can use a Drive function call like

Drive.Files.get("").modifiedDate

- to get that value, but 

- it would require the user to open the Google Picker, to choose the current Spreadsheet, to grant access

3. Is there any other/better way?

Thanks for your feedback

Fausto

[Martin Molloy]

Would this work for you

function LastUpdated() {
  var s = SpreadsheetApp.getActiveSpreadsheet()
  var id = s.getId()
  var last = DriveApp.getFileById(id).getLastUpdated()

}

it needs these scopes:

  "oauthScopes": [
  "https://www.googleapis.com/auth/spreadsheets.currentonly",
  "https://www.googleapis.com/auth/drive.readonly"
  ]

Martin Molloy

MTMOMK Limited

Add-ons: 

Form Maker; Create forms quickly and easily using a spreadsheet interface.

SheetsIE; Automate the movement of data between files of different types.

Accounts Manager; Manage User Accounts for your domain from a spreadsheet.

Classroom Manager; Mange Google Classroom classes and class lists for one teacher or the whole domain

Password Manager; Delegate management of user password resets to trusted users.

Drive Files Metadata; use a spreadsheet to view and mange metadata about your Google Drive files.

Sheets Pages Manager; Manage the pages in your Google Sheets files.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7b13bd13-5233-4210-a34a-22cab47f444e%40googlegroups.com.

[Faustino Rodriguez]

Thanks Martin

No, that won't for my case

I need to keep the drive scope restricted as 

drive.file

[Dimu Designs]

You'll have to forgo using the Drive Advanced Service, since it uses a more permissive scope.

In this case, you'll have to set your desired scopes manually via the manifest file (appscript.json) and call the Drive Rest API directly using UrlFetchApp. You'll also need to pass your Apps Script's project's OAuth token as an Authorization header in that call (via ScriptApp.getOAuthToken()). I'd recommend reading the documentation for Drive REST API to find the appropriate end-points you need to call and if the drive.file scope is sufficient to fetch the information you require. 

[Alan Wells]

That's a tricky situation.  As far as I'm concerned, Google should allow the "drive.file" scope to access the current document, regardless of whether it's picked or not, IF the user has approved the "currentOnly" scope.

But, that's not the case.

The only thing that I can think of, is to prompt the user to pick the current document when they first open it up, or set it up, or open a sidebar.  Something like that.  You'd need to explain to them, that it's got to be done.  I don't know if you can add a filter to the picker to show just the current doc to make it easier for the user.

I've also thought about having the code create the file that the user is using first, because files created by the code are available, but usually people create or open their file first.  They aren't going to want to use a new / different file.

I don't know if there is a good answer for this situation.

--

[Faustino Rodriguez]

Thanks everyone

I was trying to avoid the Google Picker, but it looks like it is the only option (so far) to get the last-modified date of current document, while keeping the drive.file scope

[Martin Molloy]

It might not be ideal but what about adding a =now() formula to a cell on the spreadsheet?

You could put it on a hidden sheet

then this script would work (with the =now() formula in A1 of Sheet1

function LastUpdated() {
  var s = SpreadsheetApp.getActiveSpreadsheet()

  var lastUpdate = s.getSheetByName('Sheet1')
  .getRange('A1')
  .getValue()

}

Martin Molloy

MTMOMK Limited

Add-ons: 

Form Maker; Create forms quickly and easily using a spreadsheet interface.

SheetsIE; Automate the movement of data between files of different types.

Accounts Manager; Manage User Accounts for your domain from a spreadsheet.

Classroom Manager; Mange Google Classroom classes and class lists for one teacher or the whole domain

Password Manager; Delegate management of user password resets to trusted users.

Drive Files Metadata; use a spreadsheet to view and mange metadata about your Google Drive files.

Sheets Pages Manager; Manage the pages in your Google Sheets files.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/619ee263-74f1-46c1-be2f-b58a44560482%40googlegroups.com.