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

My data:

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

CodePudding user response：

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.

CodePudding user response：

Solution:

You can directly use

Example output: