CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
William.D
William.D

Reputation: 125

How to disable custom function re-run after opening a sheet?

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?

Upvotes: 1

Views: 854

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

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

Upvotes: 2

Related Questions