Function Recalculates Every Time I Open The Sheet, I Only Want It To Recalculate On Edit

function timestamp() { return new Date() } The function recalculates every-time i open the sheet. i only want it to recalculate on edit. I have tried to mess with 'onEdit' but i c

Solution 1:

It looks (based on your comments on the question), like you've created a custom formula and put it in a cell. If that's the case (meaning that you actually have =timestamp() in a cell), then it will always recalculate both on open and on edit.

That's just how formulas (both the standard ones and custom ones) work. You'll notice this when you have start to have too many formulas in a spreadsheet and it starts to slow down, or even not load correctly.

If you want something that only updates on edit, you'll have to write a function that does exactly that, then set a trigger that calls it on edit.

For example, this function will put the current date in cell A1 of the first sheet of the current spreadsheet:

function setTimestamp()
{
    var thisSS = SpreadsheetApp.getActiveSpreadsheet();
    var firstSheet = thisSS.getSheets()[0];
    var firstCell = firstSheet.getRange(1, 1);

    firstCell.setValue(new Date());
}

This kind of function will only run when you call it, so you can set the trigger to whatever you want. Obviously, you'll have to figure out how to select the cell where you want the date to appear.