Google-sheets – Update latest spend on Google Sheets based on Date dimension

Your draft is pretty good, but there is a design flaw: if you insert the values between B and C, then the column with formulas will become D. So, next time the script will try to get data, it will be looking at a wrong place.

Simply put, the source of the data you are recording (i.e., the column with formulas) should stay in the same place. You can put the historical data to the right of it. Like this:

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Approval Funnel");
  var source = sheet.getRange("C2:C11");
  var values = source.getValues();
  values = [[new Date()]].concat(values);    // prepending the date to values
  sheet.insertColumnAfter(3);                // inserting AFTER column C
  SpreadsheetApp.flush();         
  sheet.getRange("D1:D11").setValues(values);
}

I put SpreadsheetApp.flush(); to make sure that the previous changes (namely, inserting a column) is indeed made before the script puts the data in with setValues.

Also you had an error in values[0][0] = new Date(); -- this command would overwrite the 0th element of array (namely, the content of C2) with the date. You wanted to prepend the date, which is what I did by creating a new array with one element, [[new Date()]], and concatenating values to it.

The formula used to correctly display the data is as follows:

=IF(B3=ʺEODʺ,IF(AND(D3<ʺ12:30ʺ,E3>50,F3>50,G3>6,H3<ʺ12:30ʺ),ʺPʺ,ʺFʺ),IF(B3=ʺSOʺ,IF(AND(D3<ʺ12:30ʺ,E3>50,F3>50,G3>10,H3<ʺ10:30ʺ),ʺPʺ,ʺFʺ),IF(B3=ʺSBʺ,IF(AND(D3<ʺ13:00ʺ,E3>50,F3>50,G3>6,H3<ʺ12:00ʺ),ʺPʺ,ʺFʺ),IF(B3=ʺNDʺ,IF(AND(D3<ʺ12:30ʺ,E3>50,F3>50,G3>6,H3<ʺ12:30ʺ),ʺPʺ,ʺFʺ),IF(B3=ʺAWʺ,IF(AND(D3<ʺ12:00ʺ,E3>42,F3>50,G3>4,H3<ʺ12:00ʺ),ʺPʺ,ʺFʺ))))))

It should be noted that the formula was not typed by me personally, it was added to a copy of my sample spreadsheet. When attempting to copy and paste from the copy into my spreadsheet, there was an unknown error with the transfer and although the formula appeared to be written identically, it would not work.

I hand typed it character by character and it then worked correctly.
I don't have an explanation for this but should anyone run into a similar issue, there's the fix.