0
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 can never get it to work. I have also tried the "current project triggers" but that doesn't seem to fix the problem. I apologize that for the simplicity of the question, I simply can't find or figure out the answer. Google sheets script https://docs.google.com/spreadsheets/d/1-Ix9LUmMYNqWnz0mB2PAg9ocJ-TwszqHRbqxAchcxhc/edit#gid=0

Beukenator
  • 11
  • 3
  • This is a custom function written in Google Apps script. This function is used by entering =timestamp() in a spreadsheet cell, right ? In order to change this to an onEdit you will have to code in what sheet you want to wach for edits, what range, where the date should be written, etc... – JPV Sep 26 '15 at 18:58
  • using it as a function works great right now. i just wish it would only update when something was changed. i can use onEdit but i'm using the time stamp in multiple columns and the length of the script for each range would get very long – Beukenator Sep 26 '15 at 19:10
  • What currently causes the `timestamp()` function to run? An `onOpen()` function? Why is it being triggered when the spreadsheet is opened? – Alan Wells Sep 26 '15 at 20:46
  • a custom function like that will never work becsuse it will be evsluated only once and result will be cached by google. its explained in the docs. custom functions must only calculate based on passed parameters. see here and other similar s.o. questions: http://stackoverflow.com/a/17347290/2213940 – Zig Mandel Sep 27 '15 at 15:02
  • instead make a menu that does it to the selected cell – Zig Mandel Sep 27 '15 at 15:05
  • @Zig Mandel how would i make a menu? i'm not familiar with that – Beukenator Oct 03 '15 at 13:27
  • @SandyGood i posted an example in the question of how it functions. there is a built in trigger for when the sheet is opened and i don't know how to disable it. – Beukenator Oct 03 '15 at 13:27

1 Answers1

0

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.

Finn Smith
  • 772
  • 2
  • 7
  • 21
  • @ZigMandel, I'm not sure I understand. I created this spreadsheet (https://docs.google.com/spreadsheets/d/1u2x4mMlGxhubGG58V0zsToXDDiWHCZadWaCYWLMulqA/edit?usp=sharing), and it seems to be working. Can you take a look? – Finn Smith Sep 27 '15 at 22:51
  • @villager, this could get very messy for the situation i'm in. I will share an example link of the problem. – Beukenator Sep 28 '15 at 13:36