Google-sheets – How to get most frequent terms from a set of lists

google sheetsgoogle-sheets-charts

I have a list on Google Spreadsheets. Each line is a mission, and one of the columns indicates who I've worked together on the mission (which is between 1 and 3 people). The format is "Aquaman, Batman, Cyclops", but I can change it if needed.

Is there a way to use the plot/chart tool to create a graph which shows the frequency of each collaborator (essentially a histogram, perhaps sorted by frequency)?

In case a graphical representation is not possible, a textual representation will do, e.g., a sorted list of all collaborators, with the number of collaborations next to each person's name.

I'll be very glad to hear any ideas!

Best Answer

The comma-separated representation is really inconvenient in a spreadsheet. So the first thing I'd do is to split it. Suppose column A has the data; put =split(A1,",") in cell B1 and copy down. This fills some of columns B,C,D with the names.

Then you probably want a single column (E) listing each name only once. This can be achieved with

=unique({filter(B:B, len(B:B)); filter(C:C, len(C:C)); filter(D:D, len(D:D))})

Then you need to count how many times each appeared. Like this:

=countif(B$1:D,E1)

copied down the F column.

Now you have a table with names and their frequencies. This can already be plotted. But if you want to sort by decreasing frequency, use

=sort(E:F, 2, False)

which sorts by the 2nd column of the range, descending.

Related Solutions

Google Sheets – Dropdown Lists Values from Spreadsheet Ranges

Your script should access the spreadsheet by its URL or Id, get the values from there, and include them in HTML. Here is an example:

function doGet() {
  var ss = SpreadsheetApp.openByUrl("url");
  var sheet = ss.getSheetByName("Sheet1");
  var values = sheet.getRange("C6:C11").getValues();
  var options = values.map(function(row) {
    return '<option value="' + row[0] + '">' + row[0] + '</option>';
  });
  var html = '<form onSubmit="handleSubmit(this)"> <select name="food"> Type of Cuisine' + options.join('') + '</select></form>';
  return HtmlService.createHtmlOutput(html);
}

How to Slow Down Frequent Execution of Import Functions in Google Sheets

The specific issue was the Recalculation setting being not only "on change" but also at time intervals.

If the issue persists, consider the nuclear option: designate some cell of the spreadsheet, say A1, for the status (update / freeze), and put the following on top of importJSON function:

function importJSON(...) {
  var currentStatus = SpreadsheetApp.getActiveSheet().getRange("A1").getValue();
  if (currentStatus != "Update") {
    return; 
  }
  // the rest of function

This will guarantee that the custom function does nothing unless the sheet is set in the "Update" mode. The cell used for this purpose can have data validation with dropdown, so it's easy to trigger it between two states.

But in general, a custom function like importJSON are supposed to be recalculated only when its parameter(s) change. If the content of the URL from which it imports the data changes, one may force a manual refresh by calling the function like =importJSON(url, 1) putting some extra parameter in. The custom function will ignore extra parameters, but the Sheets software does not know that.

Best Answer

Related Solutions

Google Sheets – Dropdown Lists Values from Spreadsheet Ranges

How to Slow Down Frequent Execution of Import Functions in Google Sheets

Related Topic