refresh cell instead of pressing ctrl-Shift-E manually

Question

I have a few queries on the sheet where I extract info to a database.

The problem I have is that the code implemented was fine till some sheets won't display the query showing the: "warning: one or more of these results' entries may not be displayed. Select ctrl+Shift+E to show them.

I tried clearing the values and pasting them back to no avail.

what else can I do?

Answer 1

IMHO I don't think that there is a canonical answer to this question - there has always been a loud clamour from users around certain topics which merely dissipate into an eerie silence of the Google docs engineering team (this issue seems to be shrouded in the same eternal mystery as IMPORTRANGE frequently failing or SPLIT not creating a perfect matrix).

I have been bitten by this myself several times so have some experience. I have used a heath-robinson workaround, which has worked for me. But YMMV.

The OP's question discusses the situation where there is a formula which was working well, but after some time of operation, a Ctrl+Shift+E situation has started to occur.

(There are other situations where Ctrl+Shift+E might occur due to results of one formula attempting to overwrite onto cells written by another formula. In this scenario, sometimes adding EXPAND at the beginning of the desired formula does the trick. Also using Filter functions, rather than 'IF' comparisons, does mop-up previous =CONTINUE(1,2,3) cells ... but perhaps neither of these are likely to be of help to the OPs question).

In my experience the spreadsheet might develop such a 'confused' behaviour over time, so requiring Ctrl+Shift+E, meaning a period of time where there are several uses-of-the-spreadsheet resulting in changes to the data. I might further speculate that there is more likelihood of this confused behaviour when the referred-to data is more dynamic e.g. rows added, rows removed OR that there are other formulas which also calculate from the same data set (let alone that these two formulas relate to each other).

I think that the idiom 'confused-behaviour' is appropriate since the actual engineering of the spreadsheet is an open box only to google engineers; we cannot rationalise its workings. So it seems mysterious and magical how, in the OP's case and my experience, the spreadsheet was working well, but then altered behaviour to require Ctrl+Shift+E without there being any change in the formulas, only in the sheets' accumulated data changes through usage.

I am giving an example of my workaround using an example case of mine. Note that in my case the data and also the problematic formulas are in one single sheet which I am calling the production sheet

The workaround involves using a script to duplicate a 'template-sheet', in the same spreadsheet as the production sheet. The template-sheet is structurally identical to the production sheet with the same column headings, but with only a few lines of sample data. It has the same formulas as the production sheet, which refer to the sample data in the self-same template sheet (not to the production sheet). Importantly, the template sheet is not showing 'confused-behaviour' - no Ctrl+Shift+E is required in the formula cells should there be any changes to the template's sample data. It also contains the formating of the production sheet.

So, when the script runs, it creates a duplicate of the template-sheet; it subsequently copies over the production sheet's data to this template duplicate and reapplies the formats. This duplicate becomes the new production sheet. The old production sheet is hidden (in my case, but could be deleted).

I have this system operational on a Tasks List spreadsheet being used by 12 people daily, where Tasks come in by google form throughout the day (and are themselves copied over to the production sheet by another script, not shown as not relevant to this OP). Once a task is completed, the task's row is removed from the production sheet. So the data grows and shrinks constantly throughout the day. The duplicate sheet is created each night, by timed trigger upon the insertSheet() function. Here is the script I use:

function insertSheet(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet =ss.getSheets()[0]; //first sheet is the production sheet
  var sheetRange = sheet.getRange('A3:P');
  var sheetValues= sheetRange.getValues();
  var d = Utilities.formatDate(new Date(), Session.getTimeZone(), 'ddMMMyyyy-hh:mm:ss');
  var ex = sheet.setName('CCEs' + d);
  var templateSheet = ss.getSheetByName('templateSheet');
  var s2 = ss.insertSheet(0,{template: templateSheet});
  var height = sheetRange.getHeight();
  s2.insertRowsAfter(2, height)
  s2.setName('CCEs');
  templateSheet.hideSheet();
  ex.hideSheet();
  s2.getRange('A3:P' + (sheetValues.length+2)).setValues(sheetValues);
  format(); //
  templateSheet.hideSheet();
  ex.hideSheet();
}

function format(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formatRange = ss.getSheetByName('templateSheet').getRange('B2:N2').copyTo(ss.getSheetByName('CCEs').getRange(2,2, ss.getSheetByName('CCEs').getMaxRows() -1,14), {formatOnly:true});
}

Prior to deploying this strategy, the spreadsheet would experience 'confused-behaviour' after a couple of days or so.

The sheet contained three formulas which would become confused. They are not important to the OP, but I duplicate here just to give the reader a feeling for the success of this strategy:

=arrayformula(IFERROR(FILTER(if(row(O:O) =1,"Auto Time Stamp ",iferror(1/0)) &O:O&if(row(O:O) =1,"copy",),len(A:A)),"Error"))

=Arrayformula(iferror(if(filter(L:L, len(A:A)) - filter(A:A, len(A:A))>0, if( int(filter(L:L, len(A:A)) - filter(A:A, len(A:A))) = 0 , text( text(filter(L:L, len(A:A)), "HH:mm") -text(filter(A:A, len(A:A)), "HH:mm") , "H:mm") , int(filter(L:L, len(A:A)) - filter(A:A, len(A:A))) & "Day(s), " & text( text(filter(L:L, len(A:A)), "HH:mm") -text(filter(A:A, len(A:A)), "HH:mm") , "H:mm") ) ,iferror(1/0)),"Time Taken"))

=arrayformula( IFERROR(if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Normal")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A1 )),"OVERDUE Normal", if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Urgent")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A2 )),"OVERDUE Urgent", if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Very Urgent")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A3 )),"OVERDUE V. Urgent", IFERROR(1/0)))),countif(if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Normal")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A1 )),"OVERDUE Normal", if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Urgent")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A2 )),"OVERDUE Urgent", if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Very Urgent")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A3 )),"OVERDUE V. Urgent", iferror(1/0)))),"OVERDUE *")& " OVERDUE"))

After several days, we delete all the accumulated old production sheets.