I've got multiple sheets with anywhere from 500 - 10,000 rows each that I need to find specific values in a given column. Ex. Sheet A, B, C have information about a bunch of products. I search for Product Id in A, B, C (with 3 individual for loops) and output the results into a new sheet.

To search through each sheet I use .getValues() for a finite range and use a for loop:

for (var i=0;i<a.length,i++) {
    if (a[i][indexWithId] == ProductId) {

and then once I have my tempArray I insert it into the new sheet with .setValues().

My question is: Is there a way to just pull out all of a certain index without going through the results and rebuilding a 2nd temp array? Eg. of these 30 columns, I only need 3. I'd like to display information about each product, but in sections, not in a single row. The ideal output would look like:

> ---Status section from sheet A---
> Id    |    name    |    status
> 001      productX       active
> ---Category section from sheet A---
> Id    |    name    |    product category
> 001      productX            sales

The only way I can think to accomplish this is to brute force or hard code something like this for every section I want to create:

var tempArray2 = [];
for (var i=0;i<tempArray.length,i++) {
    tempArray2[i] = [];

If my answer is a loop, that's fine. But if there's a function that spits out all values of a given set of indexes without a loop, that'd be cool too.

  • 1
    Have you considered doing it with a regular formula (not a script)? Something like ={'Sheet A'!A:A, 'Sheet A'!D:D} gives you the A and D columns of Sheet A. Sep 11, 2018 at 9:05
  • I was actually using an =arrayformula with multiple query but the overhead and recalculation was crazy. I then was using .setFormula and doing a query for each parameter was also taking too long. In the end, a loop has been quickest, if not the prettiest.
    – Grant
    Sep 11, 2018 at 23:26
  • Have you considered to ask this question on Code Review? Sep 12, 2018 at 1:59


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.