Google sheets QUERY function for joined cells

Question

for example, I have the next table. And I want to query all values which have the first cell "Computer". I have tried QUERY formula "QUERY(A1,B3;"select B where A = 'Computer'")" but it returns only the first B value - Keyboard.

Is it possible to return all values? Thanks.

Hi @RuslanLeshchenko! I could program an Apps Script code that will work in your scenario. Would that be a valid solution for you? — Jacques-Guzel Heron
Thanks, @Jacques-GuzelHeron, I want to query all cells (Keyboard, Display, Mouse) which have Computer cell at the left. — Ruslan Leshchenko

Jacques-Guzel Heron Jacques-Guzel Heron · Accepted Answer · 2020-05-27T09:33:19

I understand that you want to read a table composed of different pieces like the one that you posted, and if a match is found on the left column the script will need to return the contents of the right column. If my assumption is incorrect, please forgive me. An example initial table can look like this one:

In the example we will return the rows that contain PRAESENT in the first column. In that case, you can use the following Apps Script:

function so61913445() {
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var matchData = [];
  var indexColumn = 0; // Column A
  var targetIndex = "PRAESENT";

  for (var r = 0; r < data.length; r++) {
    for (var c = 0; c < data[0].length; c++) {
      if (c == indexColumn && data[r][c] == targetIndex) {
        matchData.push(sheet.getRange(r + 1, c + 2, 3, 1).getValues());
      }
    }
  }

  for (var r = 0; r < matchData.length; r++) {
    for (var c = 0; c < matchData[0].length; c++) {
      sheet.appendRow(matchData[r][c]);
    }
  }
}

In the code, the first step is to declare some variables to read the sheet (using the methods SpreadsheetApp.getActive() and Spreadsheet.getActiveSheet()), the data (with Sheet.getRange() and Range.getValues() methods) and some settings like the target word to match. After that, the code iterates over the data and, if the target word is found, the code will add the contents of the right column into the final array. Finally, the code will repeat the iteration to write the data just under the table using Sheet.appendRow(). The final result will look like this:

Please, ask me any question if you still need some help.

Google sheets QUERY function for joined cells

1 Answers