Google Sheets – Daily Average Calculations

You can use the WEEKDAY functionality to determine the day of the week, and then compute the Average of all days that are not Sunday (1) or Saturday (7).

WEEKDAY(number, type)

Returns the day of the week for the given number (date value). The day is returned as an integer based on the type. Type determines the type of calculation: type = 1 (default), the weekdays are counted starting from Sunday (Monday = 2), type = 2, the weekdays are counted starting from Monday (Monday = 1), type = 3, the weekdays are counted starting from Monday (Monday = 0).

from: https://support.google.com/docs/bin/static.py?hl=en&topic=25273&page=table.cs&tab=1240285

The you would need a collection of SumIF statements for each week day 2-6, and a count of them to determine an average.

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.