Query Google Spreadsheet columns by name in Google Query Language?

[GradualStudent]

Newbie question. I have a Google Spreadsheet (example below).

I'm able to execute queries like "SELECT B, COUNT(A) GROUP BY B" using
the column letter identifier.

I'd like to execute queries like "SELECT region, COUNT(visit_id) GROUP
BY region" using the column name more like a regular database.

Is that possible?



https://docs.google.com/spreadsheet/ccc?key=0AtEH2Kw9zfIodEQ2dFNFam9sdEFEZlVnNEV3ZEZMZEE&hl=en_US#gid=0)

[asgallant]

The documentation for the query language implies that you can do so with Google Spreadsheets: http://code.google.com/apis/chart/interactive/docs/querylanguage.html

I have no experience trying it, though, so I could be wrong.

[ChartMan]

The query language operates over column IDs. In google spreadsheets these are the column letters A,B,C,...

Sorry
ChartMan

> --
> You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/la6w7T96XWAJ.
> To post to this group, send email to google-visua...@googlegroups.com.
> To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.
>

[GradualStudent]

Thanks!

[MC Get Vizzy]

What do you mean "by name"?  Spreadsheet columns are A, B, C...

On Thu, Sep 29, 2011 at 7:09 AM, GradualStudent <shet...@gmail.com> wrote:

Thanks!

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.

[GradualStudent]

Great question, that sparked the clue to resolve it in my head...

In Google Spreadsheets, the columns indeed have headings A, B, C.
But the first row also appears to be reserved and function something
like a column name.

For instance in this spreadsheet, the first row looks more like a
column name than just a first row.

https://docs.google.com/spreadsheet/ccc?key=0AtEH2Kw9zfIodEQ2dFNFam9sdEFEZlVnNEV3ZEZMZEE&hl=en_US#gid=0


Here the query "SELECT B, COUNT(A) GROUP BY B" returns the values
'East', 'South', 'West' and 'Midwest.
Interestingly, it does not return the value in the first row, (i.e.
'Region'). Which is good.

I see now that the URL it generates and which I pass to Google Query
Language appends the argument "&headers=1"

When I uploaded the dataset, Google guessed (correctly) that the first
row is a header, and made that a setting.

So it's smart enough not to use the first row as a dataset. But it
doesn't treat it as a column name either. It just knows to ignore the
first row.

[Vp Singh]

I'm also facing same challenge, 

and specially if column name exceed in number say column BY is treated as Keyword hence google is unable to parse query which refers to column name BY. 

If you are able to find workaround or solution kindly let me know 

Regards

Vajinder

vps...@cssoftsolutions.com

Skype : vjsingh.cssoft

[Spindizzy Wizard]

I ran into this myself, but found this microscopic bit of text in the query language description:

 (Note that column IDs in spreadsheets are always letters; the column heading text shown in the published spreadsheet are labels, not IDs. You must use the ID, not the label, in your query string.)

So, while for OTHER sources of data, you might be able to use the first row as id's, you cannot do that with spreadsheets. 

See the text at the beginning of the first example for Setting the Query for the Data Source URL.

https://developers.google.com/chart/interactive/docs/querylanguage#Setting_the_Query_in_the_Data_Source_URL

[Dave Meindl]

With a little fancy footwork, you can, in fact, query columns by name.

https://docs.google.com/spreadsheets/d/1v0ud0ldnBcYLrGkN6I5Nx0Z7DIA_gwLmrCvy8eTg7_Y/edit?usp=sharing

The explanation and breakdown of how this works is here: http://mudd.com/blog/query-column-header-name-google-sheets/

[Marie Schleimer]

would it even be possible to match by name but via a cell that refers to the text or would that be pushing the fancy footwork?

Let's say for example the word "Year" was in a cell in the "match sheet" and we would want to refer to "year" header in the "data" sheet?

[Marie Schleimer]

Just found out it is possible yay! with TEXT("CellRef",0) check this out if anyone needs this too for some reason...

https://docs.google.com/spreadsheets/d/15nI0mqj2x0RDXEIY8bkYlPsXFZ96Ypz1fkBsBLj_FqI/edit?usp=sharing

[Dave Meindl]

Absolutely. If I understand your question correctly, you want to be able to put "Year" into a cell to tell the QUERY function to select the year. 

First, I'd probably set up a cell with data validation. The data validation would list the values in row 1 of the "data" sheet, i.e. "Make", "Model", and "Year". Then you could select the field you wanted, and avoid accidentally entering an invalid value.

Next, instead of "hard-coding" the word "Year" into my MATCH function, I'd reference the cell with the data validation. In other words, instead of =MATCH("Year",data!A1:C1,0) I'd do something like =MATCH(A1,data!A1:C1,0).

I've added an example here: https://docs.google.com/spreadsheets/d/1v0ud0ldnBcYLrGkN6I5Nx0Z7DIA_gwLmrCvy8eTg7_Y/edit#gid=1013051815

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/FMGmbzrhh_4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.

Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/3841a54f-0832-412a-8df4-f57d752ea4f5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Dave Meindl]

Great, Marie! Well done. The TEXT function might not be necessary. You may be able to simply reference the cell directly. MATCH(H1,data!A1:C1,0)

[Ian Viney]

If you name the range of your data table as MyData, and name the row containing the field names as MyHeaderRow then you can use this pattern (as illustrated in this example sheet):

=QUERY(MyData,"select " &

CHAR(64+MATCH("Field1",MyHeaderRow)) & "," &

CHAR(64+MATCH("Field2",MyHeaderRow)))

MyHeaderRow needs to stretch from column A to at least the rightmost column of MyData, even if MyData starts in a column to the right of column A. For example, if MyData is columns B through D, MyHeaderRow should be A1:D1 (or 1:1 would work fine too).

Remove the line breaks before you copy the code into a cell, otherwise Google Sheets gets confused.

Note that this only works for tables that don't go past column Z.

See here for how to name ranges if you're not familiar with them.