Google-sheets – Google sheets ifs then else problem

Custom functions are re-run when their parameters change. For example, if you have =myfcn("sheetname", 1, 1, 1, 1) and change some of parameters here, the function will run again. But if you go and change the content of cell sheetname!A1, the function will not re-run, since there is no indication in the spreadsheet that its output depends on sheetname!A1.

Alternative

To get all formulas from a sheet into another sheet as text, use a script function like this:

function copyFormulas() {
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() == 'Watched Sheet') {   
    var data = sheet.getDataRange();
    var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Formulas');
    target.getRange(1, 1, data.getHeight(), data.getWidth()).setValues(data.getFormulas());
  }   
}

The function takes formulas from 'Watched Sheet' and puts them into 'Formulas' sheet as text. This script can run periodically, or on every edit, or on every change (including adding rows, etc). See triggers.

Use lookup instead of if:

=lookup(B7,
       {"#000000","#00ff00","#ff0000","#ff9900"},
       {"Not applicable", "Read","Unread","In Progress"}
  ) 

Note the second parameter must be a sorted list.