Google Script pulling cached data-CodePudding

I have a sheet that is pulling data from an API every minute. Each row contains information on an individual NFT that shows what has been done to the NFT for a given day. The data resets once per day at a different time for each row/NFT. The script I have archives each row just before it resets to log what happened with it during that 24-hour period.

The issue is, the script does not always pull the live time updates in my helper cells. I have been logging debugs for the last day and I can see that sometimes it pulls live data, sometimes it pulls the same data for up to 4 minutes. In other words, the countdown clock for a row may show 10 minutes on the first script run, then in the sheet it changes to 9 minutes, but the script will pull 10 minutes again. This may continue again at 8 and 7 minutes remaining, where the script still says 10 minutes. Finally, at 6 minutes, the script will get live data again and show 6 minutes. My script begins with a Spreadsheet flush as well.

I have only found this similar issue reported a year ago, but I did not see it resolved anywhere: Script pulling old data I agree with that poster's assumption, it seems Google Script is pulling from a cached version of the sheet. The randomness of this bug is causing major problems with my archive, and so I'm trying to figure out if it is possible to force a fresh read of the sheet, or else if there is known information about this delay so that I can account for this with proper error handling (for example, currently the helper cells continuously flag a row to be archived for 4 minutes, and remove the flag once it's been archived, but I'm not sure if that needs to be even longer)

For reference, this is the code that pulls cached data sometimes, in case there is something I can add to it to force a fresh read (perhaps adding and removing data from a cell just to make it access the sheet?):

function saveHabs() {
  var dateStamp = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var habSheet = ss.getSheetByName("Harvests");
  SpreadsheetApp.flush;
  var beVals = habSheet.getRange("be2:be").getDisplayValues();
  var habs = beVals.filter(String).length;
  var dataSheet = ss.getSheetByName("New Historical");
  var aVals = dataSheet.getRange("a:a").getDisplayValues();
  var firstRow = aVals.filter(String).length 1;
  Logger.log(habs)
  var archiveDetails;
  if (habs>0){
    archiveDetails = habSheet.getRange("be2:bi" (habs 1)).getDisplayValues();
    dataSheet.getRange(firstRow,1,habs,5).setValues(archiveDetails);
    dataSheet.getRange(firstRow,6,habs,1).setValue(dateStamp);
    dataSheet.getRange(firstRow,6,habs,1).setNumberFormat("@");    
  }
  Logger.log(archiveDetails);
  //debugging logs
  var totalCount = habSheet.getRange("BM:BM").getDisplayValues().filter(String).length;
  Logger.log(totalCount);
  var logFlags = habSheet.getRange("BM2:BP" totalCount).getDisplayValues();
  console.log(habs " habitats to archive, below are the flags and time remaining (ignore second column, that's only for unharvested habs)");
  console.log(logFlags);
}

CodePudding user response：

I'm not positive if this will fix the issue in all cases, but I came across this problem and solved it as you mentioned. I added a simple value (in my case dummyCell.setValue("Running")) to a blank cell on the source sheet. At the end of the script, the script deletes it. That has, so far, caused the script to always read the live sheet, nothing cached. This seems to only happen when the source tab is only read from, never written to, so it would appear forcing the write action updates the sheet to the live version. Hopefully this will solve this for you as well!