CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
ebyrnes24
ebyrnes24

Reputation: 43

get last updated time for cell in another sheet

This is likely a simple problem, but I am new to using google apps scripts. I am trying to write a function that tells me the last time a specific cell within a different sheet than the active one was updated.

The codes I have tried are:

function lastEdit(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet1');

  var lastUpdated = s.LastUpdated();
   s.getRange('A2').return(lastUpdated);
}

and

function lastEdit(input) {
 r = Date(input)
 return(r)
}

The first one kicks up an error that .LastUpdated() is not a function and the second only gives me the current Date/Time. Thanks for any help.

Upvotes: 0

Views: 3918

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

There is no Apps Script method for spreadsheets called LastUpdated()

As a workaround, you can

  • implement an onEdit trigger
  • Save on each edit of the specified cell the modification date in scriptProperties
  • Retrieving the date of the last update by querying for the value saved in the script properties

Sample:

const ss = SpreadsheetApp.getActiveSpreadsheet()
const s = ss.getSheetByName('Sheet1')
const updatedCellNotation = "A1"
const props = PropertiesService.getScriptProperties()

function lastEdit(){
  var lastUpdated = props.getProperty("lastDate")
   s.getRange('A2').setValue(JSON.parse(lastUpdated))
}

function onEdit(e){
var editedCell = e.range.getA1Notation()
 if(editedCell == updatedCellNotation && e.range.getSheet().getName() == s.getName()){
 props.setProperty("lastDate", JSON.stringify(new Date()))
 }
}
  • Note that for writing values into a cell you need to use the setValue() method - return is not a valid method for it
  • Please refer to the reference for other valid Apps Script methods.

Upvotes: 1

Related Questions