How do I make Google Sheets refresh every 60 secon

问题:

I have an ImportJSON script in my Google Sheets retrieved from here. Now I have code:

=ImportJSON("http://date.jsontest.com/","/time", "")

which simply retrieves the time right now. My issue is that it does not refresh automatically.

How do I make it refresh every 60 seconds?

回答1:

I use a refresh script with the google apps script.

First you need to generate a random number to trick it into thinking its a new link.

  var min = Math.ceil(0);
  var max = Math.floor(99);
  var randomNum = Math.floor(Math.random() * (max - min + 1)) + min

After that you need to write to the cell you want to have the time

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("Sheet1");

sh.getRange("A1").setFormula('=ImportJSON("http://date.jsontest.com/","/time", "","' + randomNum +'")';

Then you can set up the script to run whenever you want.