IssueTracker

Can you provide the below requested information to better understand the issue:

Can you please test with latest library and update result here.
https://developer.android.com/topic/libraries/support-library/revisions.html

If possible please provide  a complete sample application.

Expected output
What do you expect to occur?

Current output
What do you see instead?

This seems to be intended according to https://issuetracker.google.com/74278849, the suggestion is to remove the extra logic to deliverResult() in onStartLoading().

@gg..@google.com
Sample project is attached.

Steps to test:
1. Import, build and install apk
2. Notice "Number of 'onLoadFinished' calls: 1"
3. Click "Click me!" button to start Second Activity
4. Notice "Number of 'onLoadFinished' calls: 2"

EXPECTED: At stap 4 "Number of 'onLoadFinished' calls" should be 1.
ACTUAL: It's 2.

@ha...@gmail.com
That's not the solution because developers are not in control of all Loaders. For example CursorLoader from support libaray should be then fixed by google, not by developers.
And this is behavior change which is not documented, so suggestion on issuetracker is not good enough.

We have shared this with our engineering team and will update this issue with more information as it becomes available.

Do you have any news ?

@6 I think they changed the way it works, without much information about the change.
Try this solution I've written about (read the last part of the text too, as it talks about the changes) :
https://stackoverflow.com/a/22675607/878126

@7 Thank you for your information. I still get the error when orientation change

@8 I can't confirm. It works fine without any error .
Attached sample project, that "loads" content, and after 5 seconds returns a result, that will be returned on the current Activity.

OK I'll check it again. Thx a lot!

@9 Hello man, when orientation change  the "onLoaderFinished" method, called again. Do you know how to stop calling this method?

@11 It is called only once after onCreate is called, just as intended.
The reason is that after you load something, you wish to use it. After orientation change, all is destroyed, but the result is still available, so you can use it.

If you don't want to use the result, either ignore it, or check whether the loader (if existing) has a result or not.
But this loses the whole point of using a loader. By using a loader, you are supposed to use its result, even after an orientation change.

I've been waiting for this feature for a while now, very glad it's been worked on.
I tried to reproduce the external_table_definition feature in Comment12, but couldn't get it to work.
Details here:
https://stackoverflow.com/questions/50627488/bigquery-google-drive-query-multiple-sheets-with-googlesheetsoptions-range

Hi Albert,
Thanks for sharing this. Using your sheets, I could not repro the issue you are seeing using the same external table def json contents as yours. Following is the output from my runs:

bq query --external_table_definition=mySheetTable::~/sheet_api_test/tabledef_sheet1.json 'SELECT * FROM mySheetTable'
+------+------+
| col1 | col2 |
+------+------+
| col1 | col2 |
| foo  | bar  |
| abc  | def  |
+------+------+

bq query --external_table_definition=mySheetTable::~/sheet_api_test/tabledef_sheet2.json 'SELECT * FROM mySheetTable'
+------+------+
| col1 | col2 |
+------+------+
| col3 | col4 |
| foo  | foo  |
| bar  | bar  |
+------+------+

works now, thanks!

In trying to automate the process of creation of the table with 'bq mk' and the creation of the json with the range parameter, I encountered problems again. See below for details:

https://stackoverflow.com/questions/50643699/bigquery-error-while-reading-data-error-message-row-1-has-only-1-columns-whil

Hi Albert, I've replied to your question on stackoverflow.

Cool, but could you also build in support for the BigQuery GUI?
We are trying to automate importing multiple tabs on a sheet to different tables by using scheduled query. But this does not work yet.

+1

+1

Yeah I met the same problem too.

+1 please

+1 please

+1 for GUI support

+1 for supporting this in the web UI, please!

+1 please

+1 please

Hint for importrange users in this case:
When creating a new sheet, and importing the content of the specific worksheet via the "importrange" function, it throws an error when querying that table in Bigquery. The solution is to skip the first row when setting up the import/transfer of the sheets document in Bigquery. Otherwise, the formular itself in the first cell cannot be read by bigquery, hence the error.

+1

+1

+1

I'm trying to specify a range on a temporary table from python using:

external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")
external_config.source_uris = [...]
external_config.options.range = "MyTab!A1:Z"
external_config.schema = schema
..etc...

However, it seems to ignore options.range. My guess is that if the CLI supports it, the API should too. What I'm I doing wrong?

I managed to make it work by a hack that forces the range option in:

external_config.options._properties["range"] = "MyTab!A1:Z"

Is there a cleaner way of doing this?

+1

Hi, yes, the client library support is not there yet.
We are rolling out the support in the new BQ web UI for you to specify a range parameter as well. Please stay tuned.

+1

+1

+1

+1
The ability to query from multiple tabs in 1 google sheet document would be awesome.   I prefer not to create 7 google sheet documents and then query them.

Useful in applying UNIONS or other SQL technique with other big query tables.


Thanks

+1

+1

The solution per #12 worked when I first tried it months ago.

I now receive the error `BigQuery error in query operation: Invalid source URI: /google_sheets_tabledef.json` using the same configurations. I tested the Google Sheets url in the format: "https://drive.google.com/open?id=XXXXXXXXXX" and "https://docs.google.com/spreadsheets/d/XXXXXXXXXX", both returned the same error.

Were there updates that deprecated this feature?

Hi Kevin, this shouldn't happen, both these url forms should work for Google Sheets. If you can share more details (like job id, or external table definition you used, we can look into it.

Hi Li,

Attached the external table definition here.

This is the command I used:
bq query --destination_table gsheets.subway --external_table_definition="mySheetTable::~/Documents/subway.json" 'SELECT * FROM mySheetTable'

I have tried using different paths and different flags but get the same result every time.

Thank you for looking into this.

Looks like you need to use a full path to your json file, try replace the '~' in "~/Documents/subway.json" to the actual path.

Thank you, that worked great.

While this is able to create a permanent, static table, how can the command be adjusted to create a table linked to the Google Sheet? Not sure why I can't reproduce the same results as I have before...

Tried bq mk as well just for the heck of it. But as expected, not supported.

You can use bq mk ---external_table_definition=<your_json_file> <your_data_set>.<your_table_name>

This table will be a so called external table, backed up by your Google Sheets as the source.

Announcement on this feature update:
1) The support for specifying sheet range is now available in the BigQuery new Web UI as well:
 -- When creating an external table with Google Sheet as the file format, you can specify it in the text box "Sheet range (Optional)".
 -- When you click such a table under your dataset, in the Details view, you will see a "Sheet range" property.

2) Schema auto-detection is also supported even when you specify a sheet range (i.e., the specific comment about auto-detection in comment#12  is no longer true).

This is pretty cool. Thanks to everyone who worked on it!

On Thu, Mar 14, 2019 at 2:43 AM <buganizer-system@google.com> wrote:

Works perfectly! This is awesome, great work! Love the new UI also

+1

+1

+1

+1

+1

+1

+1

How can we load multiple worksheets (that have similar schema) in the same file at once?

I have a google sheet that has 5 years of data, where each worksheet represents one month.

Right now, I have to create 60 tables and then UNION them in BigQuery?

This ticket resolves the issue of allowing users to select the sheet of our choice. But can we combine data from multiple sheets to create an external table?
What I mean is to select data from sheets 3 to 5 of a google worksheet to create a BQ table on top of it.

same issue

+1