Javascript - Create a dynamic dictionary in apps script google sheet

bry888
August 8, 2024
102 views
2 votes
3 Answers

I have a column with data in google sheets like this:

col_name
A
A
A
B
B
C

I want to store this data in a dictionary where column value is a key and number of occurrences is a value:

dict = {A:3, B:2, C:1}

I tried to iterate through a list of values:

col_values = mySheet.getRange(2, col, last_row-1, 1).getValues();

With a function:

function count_occurances_in_col(col_values){
  let occurances_dict = new Map();
  for(var i=0;i<=col_values.length;i++){
    if(occurances_dict.has(col_values[i][0])){
      let value = occurances_dict.get(col_values[i][0]);
      occurances_dict.set(col_values[i][0], value+1);
    } else {
      occurances_dict.set(col_values[i][0], 1);
    }
  }
  return occurances_dict;
};

But the function returns undefined instead of a dictionary (perhaps) because keys are set dynamically (if I understood correctly the forums – I don’t know JS well, only python).

Can I obtain such a dict with some function or any other way?

Notice that getValues() method returns col_values in a form:

[[A], [A], [A], [B], [B], [C]]

Answers

This should work

const col = 1;
const last_row = mySheet.getLastRow();
col_values = mySheet.getRange(2, col, last_row - 1, 1).getValues();

function count_occurances_in_col(col_values) {
    let occurances_dict = {};
    for (var i = 0; i <= col_values.length -1 ; i++) { 
        if (occurances_dict[col_values[i][0]] != undefined) {
            occurances_dict[col_values[i][0]] += 1;
        } else {
            occurances_dict[col_values[i][0]] = 1;
        }
    }
    return occurances_dict;
};

The simplest way to get a dictionary is to use a plain vanilla Object, like this:

function count_occurances_in_col(col_values) {
  const occurances_dict = {};
  col_values.forEach(v => occurances_dict[v] = 1 + (Number(occurances_dict[v]) || 0));
  return occurances_dict;
}

function test() {
  const ss = SpreadsheetApp.getActive();
  const col_values = ss.getRange('Sheet1!A2:A').getValues().flat().filter(String);
  const result = count_occurances_in_col(col_values);
  console.log(result);
  return result;
}

The test() function will log and return the result in the format { A: 3, B: 2, C: 1 }.

Here is an example using Set to get the unique values of col_values and then count the number of occurances.

Code.gs

function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Test");
    let col = 1;
    let last_row = sheet.getLastRow();
    let col_values = sheet.getRange(2,col,last_row-1,1).getValues().flat();
    let keys = [...new Set(col_values)];
    let dict = {};
    keys.forEach( key => dict[key] = 0 );
    col_values.forEach( key => dict[key[0]]++ );
    console.log(dict);    
  }
  catch(err) {
    console.log("Error in test: "+err)
  }
}

Execution log

7:02:47 AM  Notice  Execution started
7:02:48 AM  Info    { A: 3, B: 2, C: 1 }
7:02:48 AM  Notice  Execution completed

Please signup or login to give your own answer.

Click here to cancel reply.

Javascript – Create a dynamic dictionary in apps script google sheet

Answers