lundi 19 février 2018

Google Scripts seems to be Caching Cell Values - Any way to avoid?

Hello Stack Overlords!

I'm currently trying to put together a little coding project. Something simple, but I'm having a rather difficult time.

The objective, clear out three cells once the time of day, based on one of those cells, passes.

Essentially - G9 contains the the time I want the cells to be cleared. Once it has gone past the time of day specified in G9, I'd like cells D6, D9 and G9 cleared.

I've set up a way of doing this using an IF statement, both from the sheets side, and scripts side. On the sheet itself, I have a 1/0 toggle in cell K12. If the value in G9 is greater than the time now (stored in K11), it shouldn't be deleted, so is set as "1". As soon as the value in G9 is greater than the time now stored in K11, it set's the value to "0", meaning it should be deleted.

I've got Google Scripts to check the value of cell K12, and to delete my specified ranges if K12 = 0, as so:

function ClearCells() {

var s =    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AutoDeleteCellsOnTimeExpired');
var Check = s.getRange("K12").getDisplayValue();

if (Check == "0"){
  s.getRange('D6').clearContent();
  s.getRange('D9:G9').clearContent();}
}

I've then set a toggle on the Scripts function, to run the script once a minute. The issue is, the script will not clear the cells, even after the value in K12 has changed from "1" to "0". The execution transcript shows the code executes fine.

Here's the clinch... If the value is 0, to begin with (I set a value in G9 already later than now), the script runs fine automatically, as expected. After taking a look, it seems that Google Sheets assumes because there hasn't been a USER edit to the sheet (the spreadsheet settings are set to re-calculate NOW formulas once a minute), it thinks the value can't be different and doesn't bother to re-check the value in K12. If I edit another cell on the spreadsheet (even totally unrelated), Google Scripts then checks the cell value again as expected.

This indicates to me it's because it's caching the value of K12, not expecting it to change without user input (which is wrong because it updates itself based on an automatic =NOW update)?

If I'm wrong, please let me know! Otherwise... how can I stop Google Sheets assuming this? I've tried adding a flush() command, but that did nothing.

Link to the spreadsheet can be found here (this is a disposable version, only showing the need-to-see info - edit and tweak away): https://docs.google.com/spreadsheets/d/1AGOLuvGCNWJk1Ft9T2ifjcf9CzCXNHysLiSvsUwxZW4/edit?usp=sharing

Many thanks for your time!

Aucun commentaire:

Enregistrer un commentaire