Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

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

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.

about 4 years ago · Juan Pablo Isaza
2 answers
Answer question

0

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:

enter image description here

Example output:

enter image description here

about 4 years ago · Juan Pablo Isaza Report

0

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.

about 4 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!