I have a custom function like this:

  var today = new Date();
  var date = (today.getMonth() 1) '-' today.getDate() '-' today.getFullYear();
  var time = today.getHours()   ":"   today.getMinutes()   ":"   today.getSeconds();
  var dateTime = date ' ' time;
  return dateTime;

And I call the function above in the cells in Google Sheet. But everytime I open the sheet, all the function re-run and get a new timestamp. Is there a way to prevent this?

CodePudding user response：

You can utilise the Sheet.getCurrentCell() method to check if the cell already has a value and return it back if it does:

function getTimestamp() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const cell = ss.getActiveSheet().getCurrentCell()
  
  if (cell.getDisplayValue() !== "" && cell.getDisplayValue() !== "Loading...") {
    return cell.getDisplayValue()
  }
  
  var today = new Date()
  var date = (today.getMonth()   1)   '-'   today.getDate()   '-'   today.getFullYear()
  var time = today.getHours()   ":"   today.getMinutes()   ":"   today.getSeconds()
  var dateTime = date   ' '   time
  return dateTime
}

As per the documentation on Custom Functions in Google Sheets:

Custom functions can call certain Apps Script services to perform more complex tasks.

and:

Spreadsheet | Read only (can use most get*() methods, but not set*()).

So the idea is thus:

• Get the active Spreadsheet
• Get the current cell
• Check to see if the current cell has a value
• If it does, return that value (ie do not recalculate) unless that value is 'Loading...' (the function takes a moment to calculate during which the cell reads 'Loading...')
• If it does not, calculate the timestamp and set it