Google Sheets Scripts - How to get a cell value with corresponding category

Question

In Google Apps Script, I'm trying to get a value of a cell that has a particular category.

My data:

Category	Value
Work Expenses	£15.00
Work Expenses	£15.00
Pets	£25.99
Food & Drink	£38.50
Work Expenses	£11.48

So in my script I need a way to select the corresponding values of my "work expenses" category.

Answer 1

Solution:

You can directly use filter to select only the Work Expenses category and then use map to get the values of the second column.

Example script:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Sheet1") // name of the sheet
  const data = sheet.getRange('A2:B'+sheet.getLastRow()).getValues(); // select A2:B
  const fdata = data.filter(r=>r[0]=="Work Expenses").map(r=>r[1]);
  console.log(fdata);
}

Example input:

Example output:

Answer 2

If you need a script it could be something like this:

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange('A2:B');
  var cells = get_cells_by_category(range, 'Work Expenses');
  console.log(cells);
}

function get_cells_by_category(range, category) {
  var data = range.getValues();
  var cells = [];
  for (let row of data) {
    if (row[0] == category) cells.push(row[1]);
  }
  return cells;
}

But if it's a big project and you need to get values many times it makes sense to make an object from the data. The categories could be keys of the object and arrays of the cells could be values of the object:

var obj = {
  'Work Expenses': [ '£15.00', '£15.00', '£11.48' ],
  'Pets': [ '£25.99' ], 
  'Food & Drink': [ '£38.50' ] 
}

console.log(obj['Work Expenses']);
console.log(obj['Pets']);

This way you can get the values much faster. No need to loop every time through all the data.