Is there a way to average only some rows of data in a sheet

Question

I have several sheets that import various scores based on file reviews for different areas. I want to calculate an office average for those offices who have had more than one review in each period, but there's no way to tell ahead of time which offices are going to have more than one, so in each list there could be

• office 1 score
• office 2 score
• office 2 score
• office 3 score Etc. Is there a way to automate this, eg find duplicates and average, or do I have to look through after the imports and do it by hand? Cheers :) Meg

Answer 1

You can use the query function in Sheets.

Put this in cell E1:

=query(A:C,"select B,avg(C) where B is not null group by B label avg(C) 'Office average' ",1)

Answer 2

function getDataSubset() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ssId = ss.getId();
  const sheet = ss.getSheetByName('contact')
  const sheetName = sheet.getName()
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();
  
//  let theQuery = "SELECT * WHERE job ='a job'"   // works
  let theQuery = "SELECT A, B WHERE E ='a job' AND G > 30"   //works
  
// with header row - result is an array of objects if header row is specified
//  const a1Range = sheet.getDataRange().getA1Notation();
//  let result = Utils.gvizQuery(
//     ssId       // YOUR_SPREADSHEET_ID
//    ,theQuery
//    ,sheetName  // can be a number (the sheetId), or the name of the sheet; if not needed, but headers are, pass in undefined
//    ,a1Range    // specify range, ex: `A2:O`
//    ,1          // HEADER_ROW_INDEX_IF_NEEDED> - always a number
//  );
  
// no header row  -  result is an array of arrays
  const a1Range = sheet.getRange(2,1,lastRow,lastCol).getA1Notation();
  let result = Utils.gvizQuery(
     ssId       // YOUR_SPREADSHEET_ID
    ,theQuery
    ,sheetName  // can be a number (the sheetId), or the name of the sheet; if not needed, but headers are, pass in undefined
    ,a1Range    // specify range, ex: `A2:O`
                // HEADER_ROW_INDEX_IF_NEEDED> - always a number
  );
  console.log( JSON.stringify(result) );
}


/**
 * https://stackoverflow.com/questions/51327982/how-to-use-google-sheets-query-or-google-visualization-api-from-apps-script/51328419#51328419
 */
(function(context) {

    const Utils = (context.Utils || (context.Utils = {}));


    /**
     * Queries a spreadsheet using Google Visualization API's Datasoure Url.
     *
     * @param        {String} ssId    Spreadsheet ID.
     * @param        {String} query   Query string.
     * @param {String|Number} sheetId Sheet Id (gid if number, name if string). [OPTIONAL]
     * @param        {String} range   Range                                     [OPTIONAL]
     * @param        {Number} headers Header rows.                              [OPTIONAL]
     */
    Utils.gvizQuery = function(ssId, query, sheetId, range, headers) {
        var response = JSON.parse( UrlFetchApp
                .fetch(
                    Utilities.formatString(
                        "https://docs.google.com/spreadsheets/d/%s/gviz/tq?tq=%s%s%s%s",
                        ssId,
                        encodeURIComponent(query),
                        (typeof sheetId === "number") ? "&gid=" + sheetId :
                        (typeof sheetId === "string") ? "&sheet=" + sheetId :
                        "",
                        (typeof range === "string") ? "&range=" + range :
                        "",
                        "&headers=" + ((typeof headers === "number" && headers > 0) ? headers : "0")
                    ), 
                    {
                        "headers":{
                            "Authorization":"Bearer " + ScriptApp.getOAuthToken()
                        }
                    }
                )
                .getContentText()
                .replace("/*O_o*/\n", "") // remove JSONP wrapper
                .replace(/(google\.visualization\.Query\.setResponse\()|(\);)/gm, "") // remove JSONP wrapper
            ),
            table = response.table,
            rows;

        if (typeof headers === "number") {

            rows = table.rows.map(function(row) {
                return table.cols.reduce(
                    function(acc, col, colIndex) {
                        acc[col.label] = row.c[colIndex] && row.c[colIndex].v;
                        return acc;
                    }, 
                    {}
                );
            });

        } else {

            rows = table.rows.map(function(row) {
                return row.c.reduce(
                    function(acc, col) {
                        acc.push(col && col.v);
                        return acc;
                    },
                    []
                );
            });
        }

        return rows;

    };

    Object.freeze(Utils);

})(this);