Hi and thanks in advance!
I have a google sheets workbook with 56 worksheets being fed data directly from google forms. within the workbook is another sheet which is my master sheet which should consolidate data from all 56 workbook. I have tried the query function but the problem is that there was too much data being consolidated that we now have no identifier from which worksheet the records came from.
This is how the query is written:
=QUERY({'3637'!A2:N;'3636'!A2:N;'3635'!A2:N;'3634'!A2:N;'3633'!A2:N;'3632'!A2:N;'3631'!A2:N;'3537'!A2:N;'3536'!A2:N;'3535'!A2:N;'3534'!A2:N;'3533'!A2:N;'3532'!A2:N;'3531'!A2:N;'3441'!A2:N;'3440'!A2:N;'3439'!A2:N;'3438'!A2:N;'3437'!A2:N;'3436'!A2:N;'3435'!A2:N;'3434'!A2:N;'3433'!A2:N;'3432'!A2:N;'3431'!A2:N;'3341'!A2:N; '3340'!A2:N;'3339'!A2:N;'3338'!A2:N;'3337'!A2:N;'3336'!A2:N;'3335'!A2:N; '3334'!A2:N; '3333'!A2:N; '3332'!A2:N; '3331'!A2:N; '3241'!A2:N; '3240'!A2:N; '3239'!A2:N; '3238'!A2:N; '3237'!A2:N; '3236'!A2:N; '3235'!A2:N; '3234'!A2:N; '3233'!A2:N; '3232'!A2:N; '3231'!A2:N; '3139'!A2:N; '3138'!A2:N; '3137'!A2:N; '3136'!A2:N; '3135'!A2:N; '3134'!A2:N; '3133'!A2:N; '3132'!A2:N; '3131'!A2:N}, "select * where Col1 is not null",0)
Ideally, we'd like to be able to determine which worksheet these records being combined came from. Is there a way to do this like hard coding worksheet names per array declared on the code?
try:
=ARRAYFORMULA(QUERY({
{'3637'!A2:N, 3637&ZZZ2:ZZZ};
{'3636'!A2:N, 3636&ZZZ2:ZZZ};
{'3635'!A2:N, 3635&ZZZ2:ZZZ};
{'3634'!A2:N, 3634&ZZZ2:ZZZ};
{'3633'!A2:N, 3633&ZZZ2:ZZZ};
{'3632'!A2:N, 3632&ZZZ2:ZZZ};
{'3631'!A2:N, 3631&ZZZ2:ZZZ};
{'3537'!A2:N, 3537&ZZZ2:ZZZ};
{'3536'!A2:N, 3536&ZZZ2:ZZZ};
{'3535'!A2:N, 3535&ZZZ2:ZZZ};
{'3534'!A2:N, 3534&ZZZ2:ZZZ};
{'3533'!A2:N, 3533&ZZZ2:ZZZ};
{'3532'!A2:N, 3532&ZZZ2:ZZZ};
{'3531'!A2:N, 3531&ZZZ2:ZZZ};
{'3441'!A2:N, 3441&ZZZ2:ZZZ};
{'3440'!A2:N, 3440&ZZZ2:ZZZ};
{'3439'!A2:N, 3439&ZZZ2:ZZZ};
{'3438'!A2:N, 3438&ZZZ2:ZZZ};
{'3437'!A2:N, 3437&ZZZ2:ZZZ};
{'3436'!A2:N, 3436&ZZZ2:ZZZ};
{'3435'!A2:N, 3435&ZZZ2:ZZZ};
{'3434'!A2:N, 3434&ZZZ2:ZZZ};
{'3433'!A2:N, 3433&ZZZ2:ZZZ};
{'3432'!A2:N, 3432&ZZZ2:ZZZ};
{'3431'!A2:N, 3431&ZZZ2:ZZZ};
{'3341'!A2:N, 3341&ZZZ2:ZZZ};
{'3340'!A2:N, 3340&ZZZ2:ZZZ};
{'3339'!A2:N, 3339&ZZZ2:ZZZ};
{'3338'!A2:N, 3338&ZZZ2:ZZZ};
{'3337'!A2:N, 3337&ZZZ2:ZZZ};
{'3336'!A2:N, 3336&ZZZ2:ZZZ};
{'3335'!A2:N, 3335&ZZZ2:ZZZ};
{'3334'!A2:N, 3334&ZZZ2:ZZZ};
{'3333'!A2:N, 3333&ZZZ2:ZZZ};
{'3332'!A2:N, 3332&ZZZ2:ZZZ};
{'3331'!A2:N, 3331&ZZZ2:ZZZ};
{'3241'!A2:N, 3241&ZZZ2:ZZZ};
{'3240'!A2:N, 3240&ZZZ2:ZZZ};
{'3239'!A2:N, 3239&ZZZ2:ZZZ};
{'3238'!A2:N, 3238&ZZZ2:ZZZ};
{'3237'!A2:N, 3237&ZZZ2:ZZZ};
{'3236'!A2:N, 3236&ZZZ2:ZZZ};
{'3235'!A2:N, 3235&ZZZ2:ZZZ};
{'3234'!A2:N, 3234&ZZZ2:ZZZ};
{'3233'!A2:N, 3233&ZZZ2:ZZZ};
{'3232'!A2:N, 3232&ZZZ2:ZZZ};
{'3231'!A2:N, 3231&ZZZ2:ZZZ};
{'3139'!A2:N, 3139&ZZZ2:ZZZ};
{'3138'!A2:N, 3138&ZZZ2:ZZZ};
{'3137'!A2:N, 3137&ZZZ2:ZZZ};
{'3136'!A2:N, 3136&ZZZ2:ZZZ};
{'3135'!A2:N, 3135&ZZZ2:ZZZ};
{'3134'!A2:N, 3134&ZZZ2:ZZZ};
{'3133'!A2:N, 3133&ZZZ2:ZZZ};
{'3132'!A2:N, 3132&ZZZ2:ZZZ};
{'3131'!A2:N, 3131&ZZZ2:ZZZ}},
"where Col1 is not null", 0))