Creating a script that runs every second

Question

I am trying to create a script that edits a particular cell on a particular sheet every second with a random text like, "SetTime".

This Particular Cell is: X2

This Particular Sheet is: "System_Info"

You may ask why I need this, essentially, I have a cell that displays a time using the =NOW formula. When a spreadsheet is edited, it will refresh the =NOW formula.

So, I need a script that loops every second and runs a function that edits that cell.

I've used this:

setInterval(function(){ SpreadsheetApp.getSheet("System_Info").getRange('X2').setValue('SetTime'); }, 1000);

However, set interval is not defined.

Thanks for any help,

Shaun.

Apps script runs on the server side, so that kind of operations won't work. For this you could use "Triggers". Triggers will execute depending on the actions or in your case on the specified time. I'm not sure if you can create triggers to execute every second though. check the documentation: https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers — Gerardo, Nov 17 '15 at 21:07
So you couldn't just make it edit a cell every second. Just make it loop backround. — Shaun Cockram, Nov 18 '15 at 07:58
You probably could do this if you use a start and stop button. and have it run an infinite loop with a Utilities.sleep(2000) type of function inside it. You would have to play with the stop button to see how you could get it to stop. — Bjorn Behrendt, Nov 18 '15 at 12:38

score 5 · Accepted Answer · edited Feb 06 '19 at 07:28

5

you are mixing server with client code. even if you use time driven apps script triggers its not possible because they run at most once a minute, and changes through api do not cause a refresh.

Alternative: go to spreadsheet menu,file,properties. Select the option to update calculated functions every minute. No script needed.

edited Feb 06 '19 at 07:28

thiagobraga

1,519
2
15
29

answered Nov 18 '15 at 08:11

Zig Mandel

19,571
5
26
36

Well that's a good solution! Never really looked at the spreadsheet settings. – Shaun Cockram Nov 18 '15 at 10:56
how about updating the label just like a count down... is it possible? how...? – gumuruh Mar 15 '17 at 12:55
define label first – Zig Mandel Mar 15 '17 at 15:27

score 3 · Answer 2 · answered Nov 18 '15 at 19:21

3

Here is a function that will update the time in a cell every second for 15 seconds. It should be at least a starting point for you.

function updateCell() {
  for (i=0; i<15; i++){
    Utilities.sleep(1000);
    var date = new Date();
    SpreadsheetApp.getActiveSheet().getRange("A1").setValue(date);
    SpreadsheetApp.flush();
  } 
}

answered Nov 18 '15 at 19:21

Bjorn Behrendt

1,204
17
35

There is a time limit for the scripts. – johanvs Sep 09 '17 at 10:07
@johanvs I realized it works LOL just make a main function (default myFunction), and place / call this function from within the main function. While its true that each execution of a function can happen only once a minute etc. but within that main execution I guess its possible to make loops liek this LOL I'm telling you it works I just tested it.. I'm gonna see if the same works with intervals, then it could theoreitcallty check a server and make changes based on that LOL – B''H Bi'ezras -- Boruch Hashem Jan 16 '20 at 23:34

Creating a script that runs every second

2 Answers2