<jupyter_start><jupyter_text>Training: GDrive x BigQuery x GSheets[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://drive.google.com/open?id=19XZJFzkBj9QuLAfyRMbXj1-v7d9mAtYq)This notebook provides guidance on how you can connect your [Google Colab](https://colab.research.google.com/notebooks/welcome.ipynb) instance to the following:- Google Drive (GDrive) - BigQuery (BQ)- Google Sheets (GSheets)The interaction of these three is useful if you want to extract data from BigQuery and put it into a Google Sheet. *** ContentsThe outline of this notebook is:1. Mount GDrive1. Connect to BigQuery    + Import data from there1. Prepare your data1. Open and save into GSheets *** Set-upWe need to install and load the necessary packages and authenticate ourselves to use things like GDrive, BQ and GSheets.<jupyter_code># load packages
from google.colab import auth
from google.colab import drive
from oauth2client.client import GoogleCredentials

import pandas as pd
import re

import gspread
import gspread_dataframe as gd<jupyter_output><empty_output><jupyter_text>*** GDriveWe need to mount our GDrive to this session so we can access it. This location will be where we store the prepared data for other to use, a shared drive.<jupyter_code># mount GDrive
drive.mount('/content/drive')
# show files in current directory
!ls
# navigate to folder of interest
%cd "drive/My Drive/Projects/training_googlecolaboratory"<jupyter_output>/content/drive/My Drive/Projects/training_googlecolaboratory<jupyter_text>*** BigQueryWe connect to BigQuery to import data into our Google Colab session.> *Note: Colab cannot take too much data so do not import too much!*<jupyter_code># pretty table display
%load_ext google.colab.data_table
# authenticate to access Google Cloud SDK to use BQ 
auth.authenticate_user()
# save output in a variable, `data_hn_stories`
%%bigquery --project training-265812 data_hn_stories

SELECT 
  `id`
  ,`by` AS username_submitter
  ,`score`
  ,`time_ts`
  ,`title`
  ,`url`
  ,`text`
  ,`deleted`
  ,`dead`
  ,`descendants`
  ,`author` AS username_author
FROM `bigquery-public-data.hacker_news.stories` 
WHERE title IS NOT NULL
  AND title NOT IN  ('', 'Placeholder')
  AND text IS NOT NULL
  AND text NOT IN ('')
# view data
data_hn_stories.info()
data_hn_stories<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 218621 entries, 0 to 218620
Data columns (total 11 columns):
 #   Column              Non-Null Count   Dtype              
---  ------              --------------   -----              
 0   id                  218621 non-null  int64              
 1   username_submitter  218621 non-null  object             
 2   score               218621 non-null  int64              
 3   time_ts             218621 non-null  datetime64[ns, UTC]
 4   title               218621 non-null  object             
 5   url                 217250 non-null  object             
 6   text                218621 non-null  object             
 7   deleted             0 non-null       object             
 8   dead                95795 non-null   object             
 9   descendants         210069 non-null  float64            
 10  username_author     218621 non-null  object             
dtypes: datetime64[ns, UTC](1), float64(1), int64(2), object(7)
memory usage: 18.3+[...]<jupyter_text>*** GSheetWe open an existing spreadsheet and copy our dataframe over into it.<jupyter_code># authorise GSheet
gc = gspread.authorize(GoogleCredentials.get_application_default())
# open first worksheet from existing GSheet
sh = gc.open_by_key('<KEY>')
ws = sh.get_worksheet(0)

# reduce dataframe to manageable number of rows for GSheet
data_hn_stories_short = data_hn_stories.head(2500)

# add dataframe into GSheet
gd.set_with_dataframe(worksheet = ws, dataframe = data_hn_stories_short, row = 1, col = 1, include_column_header = True, resize = True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08464667005592272, 'fraction_numerical': 0.03355363497712252, 'mean_word_length': 3.146469968387777, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 18, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '355549', 'license': 'Unlicense', 'n_tokens_mistral': 1289, 'n_tokens_neox': 1192, 'n_words': 396, 'path': 'guide_gdrivebqgsheets.ipynb', 'repo_name': 'avisionh/Training-Collaboratory', 'size': '3973'}