Google-sheets – How to query for a string on a shared Googlesheet from the Googlesheet

google sheetsgoogle-sheets-query

A business partner has shared their Google sheet with me and I have view only access. I would like to query a field on column C and look for a String value (which is a ticket number) from my Google sheet.

What I ultimately want to do is find the row with the ticket number (column 'c') and display the status of the ticket on my Google sheet (col 'o' as my business partner updates the shared Google sheet.

I've been looking on the Internet and have tried different queries but I can't find anything that is used to reference a shared Google sheet.

Would appreciate anyone who can point me in the right direction.

Best Answer

UNTESTED

Please try:

=QUERY(ImportRange("*key*","*Sheet1*!A:O"),"select Col3 where Col15 =*8*",1)

You would need to insert the appropriate Sheets ID (key), Sheet name (replace Sheet1 if necessary) and provide the value to be used for searching (here 8). Do not include any asterisks.

Related Solutions

Google-sheets – How to consolidate information from different sources in Google Sheets

By using array formulas with embedded arrays you could append the three ranges in one. I.E.

=ArrayFormula(Sheet1!A1:D4;{Sheet2!A1:C3,Shee2!D1:D3*-1};Sheet3!A1:D4})

Then you could add the balance column next to the resulting range.

Google-sheets – Using Query and Importrange to pull Google Form data from one worksheet to another

The syntax of your first formula is wrong.

Form Responses!COL1:A is not a valid reference. Hard to know what you are trying to do here. NOTES:
1. Range references could have the form A1:B1, A1:B, A:B or 1:1 where A and B could be any column name while the first is the same column or it be always at the left of the second, also 1 could be any row number while the first is the same or it's on top the second.
2. Using COL1:A as reference is converted to A1:COL where COL is the 26th column of the sheet.
'column(12)' is not the proper way to reference a column in the select argument of QUERY. Use Col12 instead without apostrophes/single quote marks.

Best Answer

Related Solutions

Google-sheets – How to consolidate information from different sources in Google Sheets

Google-sheets – Using Query and Importrange to pull Google Form data from one worksheet to another

Related Topic