Cell values ​do not update

Question

I want to update every minute a cell with data from a HTML link. The problem is that the value does not update unless I manually empty the cell or make changes to the script. I tried to add NOW() to the script but the cells do not update. I do not know much about JavaScript. Could someone explain to me what and where in the script I should add or change something?

This is my script:

function DataFromDomoticz() {
  var now = new Date();
 SpreadsheetApp.getActive().getSheetByName('LUCHTVOCHTIGHEID_V2').getRange('A1').setValue(now);
 SpreadsheetApp.getActive().getSheetByName('LUCHTVOCHTIGHEID_V2').getRange('I2').setValue('=ImportJSONBasicAuth("http://*****:8080/json.htm?type=devices&rid=68";"**";"**";"/result")');     

Answer 1

• go to File
• select Spreadsheet settings...
• change recalculation like this:

• you can test it with this formula:

=IF(SECOND(NOW())={00;01;02;03;04;05;06;07;08;09;
                   10;11;12;13;14;15;16;17;18;19;
                   20;21;22;23;24;25;26;27;28;29;
                   30;31;32;33;34;35;36;37;38;39;
                   40;41;42;43;44;45;46;47;48;49;
                   50;51;52;53;54;55;56;57;58;59}, RAND(), RAND())

Answer 2

To add a formula to a spreadsheet, instead of setValue(formula) use setFormula(formula). For details on how to use this method please checkout https://developers.google.com/apps-script/reference/spreadsheet/range#setformulaformula.

Google Sheets isn't recalculating because the formula because it is exactly the same and due to Google Apps Script / Google Sheets optimization algorithms that is being ignored. To fix this your code should clear the formula, then use Spreadsheet.flush() to apply the change to the spreadsheet, then add the formula to the cell.

Remark:

In this case, the formula include a custom function, ImportJSONBasicAuth. There is a famous open source project that include a function of the same name. This famous project also include an advanced function that could be used to do the same that ImportJSONBasicAuth. This function could be used instead of the custom function to be called by a time-driven trigger.

Related

• How to make that a custom function update its result immediately
• How to update this formula so it automatically updates?