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.
={'Sheet A'!A:A, 'Sheet A'!D:D}
gives you theA
andD
columns of Sheet A.=arrayformula
with multiplequery
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.