Google Sheets "QUERY" and Calc equivalent

users
#1

Is there an equivalent Calc function for the Google Sheets function "QUERY"? When I downloaded my spreadsheet from Google Sheets as a Calc file, the "QUERY" function was not translated into an equivalent Calc function.

My original Google Sheets function is:"=QUERY('NBBP Address Book'!B2:B52)"

Thanks for any help or suggestion.

Marc

#2

Hi Marc,

Is there an equivalent Calc function for the Google Sheets function
"QUERY"? When I downloaded my spreadsheet from Google Sheets as a Calc
file, the "QUERY" function was not translated into an equivalent Calc
function.

My original Google Sheets function is:"=QUERY('NBBP Address Book'!B2:B52)"

I suppose it might help if we actually knew what this Google specific
function did / is supposed to do ?

Alex

#3

Ah, yes

It references a list of names in another sheet called "NBBP Address Book" and shows the list in a column on the first sheet A1.

So for example, in "NBBP Address Book" sheet

Name1
Name2
Name3
Name4

Will then appear in sheet A1 as the same:

Name1
Name2
Name3
Name4

#4

Marc Paré wrote:

Is there an equivalent Calc function for the Google Sheets function
"QUERY"? When I downloaded my spreadsheet from Google Sheets as a Calc
file, the "QUERY" function was not translated into an equivalent Calc
function.

What does that function to do? From a quick search:
   https://www.google.co.uk/search?q=google+sheets+query+function
it appears to provide a means of querying data in a range of cells using "Google Visualization API Query Language", which seems to be a Google-specific SQL-like query language:
   https://support.google.com/docs/answer/3093343?hl=en

I'm not aware of anything like that in Calc, but maybe others here know better. I suspect you're out of luck though, given that it seems to use a Google-specific query language.

My original Google Sheets function is:"=QUERY('NBBP Address Book'!B2:B52)"

In any case, from the above references it appears there should be a second parameter containing the query string. Even if an equivalent is available, any conversion is bound to be unreliable at best if the syntax is incorrect (even if it does seem to work in the original format). May be worth trying to correct the formula, and see if a conversion is possible then, though I suspect you'll still be out of luck.

You're best bet is probably to try to find another way to achieve the required result, without using Google-specific function.

#5

I should also add that the Google "=QUERY" then lists the names as above, but, the formula only shows on the first name (Name1) and the other names in the list are displayed as names. This allows a user to copy/paste the other names without having any formula appear when clicking in the field.

I use the list as a reference list to help users copy/paste names into other fields on the A1 sheet. It avoids users from spelling the names with errors.

Marc

#6

Is there an equivalent Calc function for the Google Sheets function "QUERY"?

Probably not - though you may want to look at Calc's "database functions". (That's the database functions in Calc, not LibreOffice's database functionality, Base.) See the help text for functions such as DGET().

When I downloaded my spreadsheet from Google Sheets as a Calc file, the "QUERY" function was not translated into an equivalent Calc function. My original Google Sheets function is:"=QUERY('NBBP Address Book'!B2:B52)"

It references a list of names in another sheet called "NBBP Address Book" and shows the list in a column on the first sheet ...

You probably don't need QUERY to do that. Just construct the formula
='NBBP Address Book'.B2:B52
but instead of clicking the green tick mark or pressing Enter, press Ctrl+Shift+Enter. The formula will be surrounded by braces (but note that you cannot simply type these yourself) and will become an array formula with similar results.

I trust this helps.

Brian Barker

#7

https://support.google.com/docs/answer/3093343?hl=en

#8

Thanks for the link Luuk.

I wonder if it would be in the interest of LibreOffice to code the same function into Calc? It would assure an amount of compatibility for people wanting to use Google Sheets on Google Drive and then LibreOffice Calc on their desktops.

At least our devs could take a look at the code and presumably the Google code would be open source.

Unless we already had a compatible function that Google could use when exporting to LibreOffice Calc.

Marc

#9

Hi Marc,

Is there an equivalent Calc function for the Google Sheets function
"QUERY"? When I downloaded my spreadsheet from Google Sheets as a Calc
file, the "QUERY" function was not translated into an equivalent Calc
function.

My original Google Sheets function is:"=QUERY('NBBP Address
Book'!B2:B52)"

I suppose it might help if we actually knew what this Google specific
function did / is supposed to do ?

https://support.google.com/docs/answer/3093343?hl=en

Thanks for the link Luuk.

I wonder if it would be in the interest of LibreOffice to code the same
function into Calc? It would assure an amount of compatibility for
people wanting to use Google Sheets on Google Drive and then LibreOffice
Calc on their desktops.

There's not just QUERY, but also some other functions (top right of the page....)

Some are not very useful (i think) like i.e. SPARKLINE

Some are complex like i.e. QUERY

Some are tied to Google like i.e. GOOGLEFINANCE

At least our devs could take a look at the code and presumably the
Google code would be open source.

I would not 'GO' for a complete coverage, and make sure that only useful functions have the chance of being implemented.

The decision of what is useful is up to someone else :wink:

I consider a 'stable' LibreOffice far more important than half-implemented-mostly-working-sometimes-not-but-we-should-have-that-functions

Unless we already had a compatible function that Google could use when
exporting to LibreOffice Calc.

of course...

#10

Base can do this.