Data Studio
1.8K members online now
1.8K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Connecting Google Sheets to BigQuery

Follower ✭ ☆ ☆
# 1
Follower ✭ ☆ ☆

I want to connect a google sheet to bigquery so I can create a custom table for DataStudio. The documentation isn't so good on the process to connect a G Sheet to BigQuery. Has anyone sucessfully done this? If so, any links or notes would be greatly appreciated.

Re: Connecting Google Sheets to BigQuery

Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

Capture.PNGYou can. You just need to create new table, select Location is googledrive and file format is google sheet, then paste the google sheet link. After that u need to create python script to select all the data from that table. The 2 function is like this( run_load , writeToTargetTable). 

 

def run_load():
#jobIds = []
for detail in details:
stable = 'SOURCETABLE'
tdataset = 'DATASET'
ttable = 'TARGETTABLE'
query="SELECT * FROM [PROJECTNAMESmiley Very HappyATASET.{A}]".format(A=stable)
writeToTargetTable(stable,query,tdataset,ttable)

 

def writeToTargetTable(JobName,Script,TargetDataSet,TargetTable):
#query = executeScriptsFromFile(Script)
query = Script
data = {"configuration":
{
"query": {
"query": query,
"destinationTable": {
"projectId": "PROJECTID",
"datasetId": TargetDataSet,
"tableId": TargetTable
},
"allowLargeResults": 'true',
"createDisposition": "CREATE_IF_NEEDED",
"writeDisposition": "WRITE_TRUNCATE",
}
}
}

.

.

.

.

After done, put in the shell script to call that python and you can register it in cronjob to run it everyday.

Connecting Google Sheets to BigQuery

Follower ✭ ☆ ☆
# 3
Follower ✭ ☆ ☆

Wow, thank you very much. Sorry but I have one more question: where do I create this script at? In BQ or in the G Sheet? Thanks! 

Re: Connecting Google Sheets to BigQuery

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

The script you need to create in BQ VM instances, connect using SSH. or if you have putty you can used it.

1. Go to https://console.cloud.google.com

2. Compute Engine Dashboard

3. Select and open your instance by clicking the SSH

4. From here, create shell script (.sh) to call the python script (.py) . Then register shell script in crontab. 

 

Example to call python script (Must put in .sh file):

 

_logGSTT="$_ANYPATH/GS_to_TABLE_$_now.log"
python ANYPATH/GS_to_TABLE.py > $_logGSTT 2>&1 &

 

 

 

GS_to_TABLE.py must contain this 2 function:

 

def run_load():
#jobIds = []
for detail in details:
stable = 'SOURCETABLE'
tdataset = 'DATASET'
ttable = 'TARGETTABLE'
query="SELECT * FROM [PROJECTNAME].DATASET.{A}]".format(A=stable)
writeToTargetTable(stable,query,tdataset,ttable)

 

def writeToTargetTable(JobName,Script,TargetDataSet,TargetTable):
#query = executeScriptsFromFile(Script)
query = Script
data = {"configuration":
{
"query": {
"query": query,
"destinationTable": {
"projectId": "PROJECTID",
"datasetId": TargetDataSet,
"tableId": TargetTable
},
"allowLargeResults": 'true',
"createDisposition": "CREATE_IF_NEEDED",
"writeDisposition": "WRITE_TRUNCATE",
}
}
}

.

.

.

.

 

You can install anaconda2 and click anaconda navigator, then click jupyter notebook,

 Untitled.png

You can start develop and test python script there. To transfer the python script into your instance, you can used filezilla or gcloud.

 

 

 

 

Connecting Google Sheets to BigQuery

Follower ✭ ☆ ☆
# 5
Follower ✭ ☆ ☆

ah. man -- THANK YOU. but this is OVER MY HEAD. I'm sure someone else will find this detailed information very helpful. Thank you very much for taking the time. 

Connecting Google Sheets to BigQuery

Follower ✭ ☆ ☆
# 6
Follower ✭ ☆ ☆

If you want to help me do this, I will provide a "dotation" to via Paypal. No worries, if not. Thanks again!