Google-sheets – Importing a row from one Google Sheet to another

google sheetsgoogle-sheets-queryimportrange

  • 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.

Best Answer

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

Related Topic