I have a Google Sheet that is being used to track applicant interview data. I am trying to find the Round Average Score for each candidate based on their Interview Round and Round score. I figured out how to gather this data with a query function but for this use case in particular it has to be done in a script.

Here is an example of the sheet

Any help would be greatly appreciated.

CodePudding user response：

If you need to get the data as permanent static values that will not change later even if the source data gets modified, you can still use a query() formula to get the results, and then use a short script to replace the formula and its results with static values. To try it out, Insert > Sheet and use this:

=query(sumAve!A1:E, "select A, B, avg(D) where D is not null group by A, B", 1)

/**
* Replaces formulas with values in the active sheet.
*/
function replaceFormulasWithValuesInActiveSheet() {
  const wholeSheet = SpreadsheetApp.getActiveSheet().getDataRange();
  wholeSheet.setValues(wholeSheet.getValues());
}

CodePudding user response：

Average of Average Scores

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  let co = { pA: [] }
  vs.forEach((r, i) => {
    let p = `${r[0]}/${r[2]}`;
    if (!co.hasOwnProperty(p)) {
      co[p] = { cnt: 1, sum: r[4], idx: i }
      co.pA.push(p);
    } else {
      co[p].cnt  = 1;
      co[p].sum  = r[4];
    }
  });
  let vo = vs.map((r, i) => {
    let p = `${r[0]}/${r[2]}`;
    if (i == co[p].idx) {
      return [co[p].sum / co[p].cnt];
    } else {
      return [''];
    }
  })
  sh.getRange(2, 6, vo.length, vo[0].length).setValues(vo);
}

Ouput: