title: Subtract date from today's date in google spreadsheet
body: <p>I currently have a Google spreadsheet representing a TO-DO list with multiple columns.
  2 of the columns are as follow:</p>
  
  <ul>
  <li>BY WHEN, which is a date with a format "dd MMMM yyyy" (e,g: 20 August 2014)</li>
  <li>DAYS TO ACT which is a number. This currently has the following formula e.g: (=D2 - today()) This give me the number of days left to act on something</li>
  </ul>
  
  <p>So we end up with something like this</p>
  
  <pre><code>        BY WHEN             DAYS TO ACT
      20 August 2014          0
      20 August 2014          0
      20 August 2014          0
      21 August 2014          1
      21 August 2014          1
      21 August 2014          1
      21 August 2014          1
      22 August 2014          2
      26 August 2014          6
      26 August 2014          6
      26 August 2014          6
      29 August 2014          9
      11 September 2014       22
      18 September 2014       29
      18 September 2014       29
  </code></pre>
  
  <p>I have an apps script attached to this spreadsheet which colorizes the rows based on the number of days left. The script can be found at the bottom of this message. Everything in the script works fine and has done so for many months.</p>
  
  <p>The problem I have is that when I leave the spreadsheet open overnight, and then make a new entry in the morning, the DAYS TO ACT value does not change for old entries as the formula is in the sheet and not in code.</p>
  
  <p>Is there a way to either tell the sheet to recalculate itself or failing this, is there a way to programmatically update the DAYS TO ACT values?
  If so, how?</p>
  
  <pre><code>        //Sets the row color depending on the value in the "DAYS TO ACT" column.
      function setRowColors() {
        SpreadsheetApp.flush();
        var sheet = SpreadsheetApp.getActiveSheet();
        var range = SpreadsheetApp.getActiveSheet().getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());
        range.setFontWeight("bold");
        range.setVerticalAlignment("top");
        var whenColumnOffset = getColumnOffset("BY WHEN");
        var actColumnOffset = getColumnOffset("DAYS TO ACT");
        var noteColumnOffset = getColumnOffset("NOTES");
        var priorityColumnOffset = getColumnOffset("PRIORITY");
  
        range.sort([{column: 5, ascending: true}, {column: 6, ascending: true}]);
  
        for (var i = range.getRow(); i &lt; range.getLastRow(); i++) {
          rowRange = range.offset(i - 2, 0, 1);
  
          days_to_act = rowRange.offset(0, actColumnOffset).getValue();
          by_when = rowRange.offset(0, whenColumnOffset).getValue();
          notes = rowRange.offset(0, noteColumnOffset).getValue();
  
          if (notes == "DONE") {
            rowRange.setBackground("#0000FF");
            rowRange.setFontColor("#FFFFFF");
            rowRange.offset(0, actColumnOffset, 1, 1).setValue(9999);
            rowRange.offset(0, actColumnOffset, 1, 1).setFontColor("#0000FF");
            range.sort([{column: 5, ascending: true}, {column: 6, ascending: true}]);
            continue;
          }
  
          if (notes == "IN PROGRESS") {
            rowRange.setBackground("#000000");
            rowRange.setFontColor("#FFFFFF");
            rowRange.offset(0, actColumnOffset, 1, 1).setValue(9998);
            rowRange.offset(0, actColumnOffset, 1, 1).setFontColor("#000000");
            range.sort([{column: 5, ascending: true}, {column: 6, ascending: true}]);
            continue;
          }
  
          if (days_to_act &gt; 14) {
            rowRange.setBackground("#00FF00");    
          } else if (days_to_act &lt;= 14 &amp;&amp; days_to_act &gt; 7) {
            rowRange.setBackground("#FFD700"); 
          } else if (days_to_act &lt;= 7 &amp;&amp; days_to_act &gt; 3) {
            rowRange.setBackground("#FF8C00");
          } else if (days_to_act &lt;= 3 &amp;&amp; days_to_act &gt; 1) {
            rowRange.setBackground("#FF4500");
          } else if (days_to_act &lt;= 1 &amp;&amp; days_to_act &gt;= -1 &amp;&amp; by_when != "") {
            rowRange.setBackground("#FF0000");
          } else {
            rowRange.setBackground("#808080");
          }
        }
        range.sort([{column: 5, ascending: true}, {column: 6, ascending: true}]);
      }
  
      //Returns the offset value of a specific column
      //(eg, if the 7th column is labeled "Status", this function returns 6)
      function getColumnOffset(colName) {
        lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
        var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);
  
        for (var i = 0; i &lt; range.getLastColumn(); i++) {
          if (range.offset(0, i, 1, 1).getValue() == colName) {
            return i;
          } 
        }
      }
  
      function onOpen() {
        setRowColors();
      }
      function onEdit() {
        setRowColors();
      }
  </code></pre>
  
  <p>I am not looking at the sheet automatically updating itself when the date changes, only when a new entry is made or failing this by calling a new function from a menu item I would create.</p>
  
  <p>Regards
  Crouzilles</p>