1

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")');
Improve this question
1
  • it is not updating after 30 minutes
    – Coen17st
    Jan 18, 2019 at 15:36

2 Answers 2

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

9

  • 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())

Improve this answer
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.