Exit script if Google Sheet not opened or not modified today (within 24 hours)

Question

I have a script with a time based trigger that makes a backup copy of a spreadsheet every evening.

However, the sheet isn't modified on weekends or some weekdays, at all.

I want to exit the DUPLICATESHEET script if the document (google sheet) wasn't opened or modified that day.

I'd prefer to do it WITHOUT having to write "last modified" in a cell somewhere.

I can get it to work by writing a last modified script to a cell, and then checking that cell with the script.

However, I'm hoping there is a way to just use the script to check the Google Sheet metadata to see the LastModified or LastOpened date. If it wasn't opened within last 24 hours, then exit the script.

Sort of a code newbie

var FILE_ID = 'FILE_ID';
var LastUpdatedPerDrive = DriveApp.getFileById(FILE_ID).getLastUpdated();

  //if currentDateTime - lastUpdatedDateTime  is more than 1 day (more than 24 hours since last update) then exit the script.
  if (new Date() - LastUpdatedPerDrive > 1 * 24 * 60 * 60 * 1000) {
  return;

//EXIT THE FUNCTION and do not proceed with below
}

Not really sure how to do this. https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet Doesn't seem to have a LastModified

Maybe I have to use the Drive app, like this How to get Google docs last edit date

Have you tried the code included on the question? By the other hand, what is the question? — Rubén
Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. — Tanaike
Thank you for your response. I'm glad your issue was resolved. — Tanaike

Tanaike Tanaike · Accepted Answer · 2019-07-31T05:30:49

You want to when the difference between the current time and the last modified time or the last opened time is smaller than 24 h, you don't want to run the script.

If my understanding is correct, the last modified time can be retrieved by getLastUpdated(). But the last opened time cannot be retrieved by it. So how about this workaround?

Workaround:

As a workaround, I would like to propose to use using OnOpen and OnEdit event trigger and Properties service. The flow of this workaround is as follows.

When the Spreadsheet is opened, the time is saved to the Properties service by OnOpen event trigger.
When the cells of Spreadsheet are modified, the time is saved to the Properties service by OnEdit event trigger.
- If you need OnChange event trigger, you can also implement it.
When the script for processing backup is run by the time-driven trigger, the script is run by comparing the saved time and the current time.

Sample script:

Please copy and paste the following script.

function saveTime() {
  PropertiesService.getScriptProperties().setProperty("time", new Date().getTime());
}

function onOpen() {
  saveTime();
}

function onEdit() {
  saveTime();
}

function process() {
  var t = PropertiesService.getScriptProperties().getProperty("time");
  if (new Date().getTime() - Number(t) < 1 * 24 * 60 * 60 * 1000) {
    return;
  }
}

When the Spreadsheet is opened, onOpen() is run by the simple trigger of OnOpen event.
When the cells of Spreadsheet are edited, onEdit() is run by the simple trigger of OnEdit event.
process() supposes a script with a time based trigger that makes a backup copy of a spreadsheet every evening.
- When process() is run, the current time and saved time are compared. In above script, when new Date().getTime() - Number(t) is less than 1 * 24 * 60 * 60 * 1000, the script is exited.

Note:

I think that Exit script if Google Sheet not opened or not modified today (within 24 hours) might be the opposite to if currentDateTime - lastUpdatedDateTime is more than 1 day (more than 24 hours since last update) then exit the script.. About this, I might misunderstand. If my understanding is wrong, please tell me.
If you want to use just if (new Date() - LastUpdatedPerDrive > 1 * 24 * 60 * 60 * 1000) return, you can modify to as follows.
```
if (new Date().getTime() - LastUpdatedPerDrive.getTime() > 1 * 24 * 60 * 60 * 1000) return
```

References:

If I misunderstood your question and this was not the direction you want, I apologize.