Scheduled query fails because data table is external (Google Sheets)

[Jannik_Adler]

I am trying to schedule my query using data from Google Sheets that updates daily.
However, when setting up the scheduled query I receive an error: 
Invalid value: XXXXX_raw_order_yesterday is not allowed for this operation because it currently has type EXTERNAL.; JobID: dogwood-XXXXXX

I have carefully followed the steps from De'Lon Dixon as displayed in his video:
https://www.youtube.com/watch?v=gJR9obSJo4k&t=316s

Any idea what I am missing here? Should be straight forward.

Cheers, 

[kvandres]

Good day @Jannik_Adler,

Welcome to Google Cloud Community!

This error is due to the table that you are trying to insert data into is an external table. External table has limitations, one of them is you cannot modify them using any kind of of methods including DML, they are read-only for Big Query, you can verify it by adding this query:

SELECT *
FROM <insert your dataset here>.INFORMATION_SCHEMA.TABLES
where table_type='EXTERNAL';

Every table listed here are external.

If you want to do it using the console.

1. From the left side hierarchy menu in BigQuery, click the table where you are trying to insert that data
2. Click the details, see under the name if it indicates External. 

You need to change the file directly, to modify the information on this or when you are trying to create a schedule query, specify a new table.

If you want to know more about the limitations of external tables, you can check the link below: https://cloud.google.com/bigquery/docs/external-tables#limitations

Additionally, there are two things that you need to make sure of before creating a scheduled query.
1. You need to ensure that you have enabled the Bigquery API and BigQuery Data Transfer Service.
2. You need to make sure that the user has the required permissions to schedule a query. You can check the required permissions using this documentation: https://cloud.google.com/bigquery/docs/scheduling-queries#required_permissions

To learn more about the pre-requisites, please check the link below: https://cloud.google.com/bigquery/docs/scheduling-queries#before_you_begin

Hope this will help!