3
  • Doc1: I have individual sheets setup for each Game Week which list the football matches being played on that particular week.

  • Doc2: I have an individual sheet setup for each team.

I'm looking to pull the scores and team names from a certain row in Doc1 to Doc2 based on the team in Doc2.

I've worked out how to do this manually but with 20 teams and 38 Game Weeks that is a lot of copying, pasting and changing 30,000+ cells to alter the column reference in.

The "Manual" code I've got working is:

=importrange("https://docs.google.com/spreadsheets/d/xxxxxx/edit#gid=xxxxxx","Game Week 1!A1")

I'd like to be able to set the team name in Doc2 and automatically fill in the Game Week rows based on than name.

After some time researching I've come up with this:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#gid=xxxxxxxxxx","Game Week 16!A1:A19"),B1,"Select * Row1,Row5")

But this seems to only want to return numbers and not text as it gives me the error message:

...Error
Function QUERY parameter 3 expects number values. But 'Select * Row1,Row5' is a text and cannot be coerced to a number.

Any help would be great, as I am at a bit of a dead end.

Below are screenshots of the "Game Week 1" sheet set up and the individual "team" sheet.

enter image description here

enter image description here

Update 1 on the Code:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#gid=xxxxxxxxxx","Game Week 20!A:E"),"Select Col1, Col2, Col3, Col4, Col5 Where Col3, Col5 matches "B1&"")

This seems like a little step in the right direction but is throwing up an error:

"Error Formula parse error."

Update 2:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#gid=xxxxxxxxxx","Game Week 1!A:E"),"Select Col1, Col2, Col3, Col4, Col5 Where Col3, Col5 matches '"&B1&"'")

The latest tweaks seem to have had some forward progress but I am now receiving this error message:

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " <ID> "Col3 "" at line 1, column 43. Was expecting one of: "(" ... "(" ...

Tinkering with a few bits, it seems to be after the 'where' part. As soon as it reads the Col3, Col5 part it seems to mess up.

Update 3:

Realized a small mistake on my part, I was searching Col3, Col5 instead of Col1, Col5. This still didn't work but then I removed the Col5 so just looking at Col1 and this has returned the correct value.

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dMo6F1CTxAaEx1bS2NEu4kIu5hJTCBbCt13AV2E_f2w/edit#gid=1390527531","Game Week 1!A:E"),"Select Col1,Col2,Col3,Col4,Col5 Where Col1 Matches '"&B1&"'")

Not sure why I can't check two columns as I've seen other peoples work performing this, may look at changing the whole code to an IF statement(?).

Update 4:

CRACKED IT!

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#gid=xxxxxxxxxx","Game Week 1!A:E"),"Select Col1,Col2,Col3,Col4,Col5 Where Col5 Matches '"&B1&"' or Col1 Matches '"&B1&"'")

Thanks to Rubén for his valuable input.

4
  • I'm sorry, there was a typo on the formula on my comment, by the other hand, why there is a ** in B1? What it means / what do you expect as result?
    – Rubén
    Sep 10, 2019 at 15:27
  • B1 will be one of the team names from game week 1, I've used ** as a place holder for now.
    – Necrovana
    Sep 10, 2019 at 15:54
  • It's better to use an actual team name otherwise a simple version of the formula will throw errors and to have a formula that will handle this errors will be a bit harder to understand. Anyway, I edited my answer.
    – Rubén
    Sep 10, 2019 at 15:56
  • I use the actual team names in the individual sheets I've created. adding the additional bits you suggest has seemingly moved this on but I now get another error, which I've added to my main question.
    – Necrovana
    Sep 11, 2019 at 10:56

1 Answer 1

1

Assuming that B1 has a team name:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#gid=xxxxxxxxxx","Game Week 20!A:E"),"Select Col1, Col2, Col3, Col4, Col5 Where Col3, Col5 matches '"&B1&"'")

Please note the use of ' to enclose the team name and & to concatenate it to the SQL statement


As the error message says, the syntax of your second formula is wrong.

The second argument, in this case B1, seems to be out of place.
The third argument of the QUERY should be a number.

Maybe you misplaced the arguments, but it's impossible to say as the value of B1 was not included in the question.

On the other hand "Select * Row1,Row5" isn't a valid Google Query Language statement. Please read QUERY function.

From the above link, here are a couple of samples:

  • QUERY(A2:E6,"select avg(A) pivot B")

  • QUERY(A2:E6,F2,FALSE)

Related

4
  • Thanks for getting replying so quickly. Coding is just a little side hobby I've started recently so I'm still learning. I'll come back once I have something a bit better.
    – Necrovana
    Sep 10, 2019 at 14:28
  • @Necrovana I added a link to my answer. If you found this answer helpful consider to accept it (click the tick mark below the triangles and answer score.
    – Rubén
    Sep 10, 2019 at 14:32
  • I feel like this, although still doesn't work, is closer to what i'm looking for... =QUERY(IMPORTRANGE("docs.google.com/spreadsheets/d/xxxxxxxxx_f2w/… Week 1!A1:E19"),"Select Col1,Col5 matches "B1"")
    – Necrovana
    Sep 10, 2019 at 14:57
  • @Necrovana you forgot to use & operator . If B1 is a number =QUERY(IMPORTRANGE("docs.google.com/spreadsheets/d/xxxxxxxxx_f2w/… Week 1!A1:E19"),"Select Col1,Col5 matches "&B1) if B1 is a text =QUERY(IMPORTRANGE("docs.google.com/spreadsheets/d/xxxxxxxxx_f2w/… Week 1!A1:E19"),"Select Col1,Col5 matches '"B1&"'") . If you need further help, edit your question to add your progress.
    – Rubén
    Sep 10, 2019 at 15:03

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.