CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Victor Resnov
Victor Resnov

Reputation: 292

Dynamic QUERY Function

I have a Google Sheet connected to a Google Form. I am gathering information about several teams, and I want to display information about each specific team.

Rather than creating 27 separate sheets for each team, I thought about creating a dynamic sheet that would ask for the team number (each team has its own number, like 6544), then query that team. Here's how it works:

Team Number: | 6544 (this is cell B1)

Then, the query code looks like this:

=QUERY('Form Responses 1'!B2:G, "SELECT * WHERE C = " + B1 "")

I thought I could concatenate the value from B1 (the team number the user entered) into the string query, so it would look like this:

=QUERY('Form Responses 1'!B2:G, "SELECT * WHERE C = 6544")

However, this doesn't seem to be working. I get #ERROR! in the cell, then when I hover over it, it reads Formula parse error.

Any ideas what went wrong? Any help is greatly appreciated!

Upvotes: 0

Views: 62

Answers (1)

Victor Resnov
Victor Resnov

Reputation: 292

I figured out the problem. To concatenate the strings, I need to use the & operator, not addition like in JavaScript or Java.

So the script looks like this:

=QUERY('Form Responses 1'!B2:G, "SELECT * WHERE C = " & B1)

It works now!

Upvotes: 1

Related Questions