CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
JohnA
JohnA

Reputation: 1107

Replacing the Month Number with the Month Name from a Query()

I am grouping data by month in a Google Sheets QUERY using the MONTH function. However, it returns the month number from 0-11 and I prefer to see the month names.

Here is the QUERY

=QUERY(DataAllMonths!A1:B, "SELECT MONTH(A), COUNT(A) WHERE LOWER(B) LIKE '%no empathy%' GROUP BY MONTH(A) LABEL MONTH(A) 'Month', COUNT(A) 'Total'", True)

This returns something like the following:

enter image description here

I ended up writing a customer function to take the results from the QUERY and substitute the month names for the numbers

=returnMonthName(QUERY(DataAllMonths!A1:B, "SELECT MONTH(A), COUNT(A) WHERE LOWER(B) LIKE '%no empathy%' GROUP BY MONTH(A) LABEL MONTH(A) '', COUNT(A) ''", True), 1)

And this shows the following result

enter image description here

Here is the script

function returnMonthName(data, col) {
  // var data = [[1, 100], [2, 200], [3, 300]];
  // var col = 1;
  var months = [ "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" ];
  var retArray = [["Month","Total"]];
  var total = 0;
  // var retArray = [];
  var n = 0;
  var dataLen = data.length;
  for (n = 0; n < dataLen; n++) {
    retArray.push([months[data[n][0]], data[n][1]]);
    total+= data[n][1];
  }
  retArray.push(["Total", total]);

  return retArray;
}

I believe there is a way to do this without depending on a custom function, perhaps using an array formula. Does anybody have a solution.

Here is a link to a sample sheet.

Upvotes: 0

Views: 1115

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15328

By formula only, Try

=QUERY({arrayformula(eomonth(DataAllMonths!A1:A,0)),DataAllMonths!B1:B}, "SELECT (Col1), COUNT(Col1) WHERE LOWER(Col2) LIKE '%no empathy%' GROUP BY (Col1) LABEL (Col1) 'Month', COUNT(Col1) 'Total'", True)

enter image description here

and change format of column to MMM

enter image description here

reference

EOMONTH

Upvotes: 1

Cooper
Cooper

Reputation: 64130

Try this:

function lfuncko(m) {
  return [...Array.from(Array(12).keys(), x => Utilities.formatDate(new Date(2022, x, 15), Session.getScriptTimeZone(), "MMM"))][m]
}

Upvotes: 0

Related Questions