Updating/refreshing google sheets cells every minute automatically

Question

I'm trying to write a test script to make Google sheets update its cells automatically every minute. It's important I get this test script to work, so I can use it to do other things.

I have a google apps script function as follows:

 function custom_function(id){
  return new Date().toLocaleTimeString();;
}

Basically this correctly returns the current time, and the id parameter isn't used. If I use this function in a cell, i.e. =custom_function(B4) then it correctly computes the current time and displays it in the cell. So far so good.

But the cell doesn't update by itself. I'm trying to make it so this cell's value, i.e. the current time, is automatically updated every minute without any interaction by the person viewing the spreadsheet.

I've tried installing a trigger that calls the following function every minute:

function private_function(){
        SpreadsheetApp.flush();
}

My trigger does call this function every minute, but the flush does not seem to do anything. I was under the impression that flush would cause all the cell entries to be recomputed, thereby updating the values of every cell by calling the custom function again in every cell. It doesn't do what I want. Is there a simple way to do what I'm trying to do without bringing in dummy time-based parameters into all my functions?

Umair Mohammad Umair Mohammad · Accepted Answer · 2019-05-29T11:43:50

Try something like this

function updateStampInSheet(e) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    var stamp = new Date();
    sheet.getRange(1,1).setValue(stamp);
}

You'll need to configure time-driven trigger to execute this every minute

Updating/refreshing google sheets cells every minute automatically

1 Answers