0

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) {
        tempArray.push(a[i]);
    }
}

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] = [];
    tempArray2[i].push(tempArray[i][IdIndex]);
    tempArray2[i].push(tempArray[i][NameIndex]);
    tempArray2[i].push(tempArray[i][StatusIndex]);
    }
}

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. – Vidar S. Ramdal Sep 11 '18 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 '18 at 23:26
  • Have you considered to ask this question on Code Review? – Rubén Sep 12 '18 at 1:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.