How to load & save data to Google Sheets in Python using Google Colab

How to load Google Sheets in Python / Google Colab and save data into Google Sheet spreadsheets using Python in Colab environment.

Providing a downloadable template solution and step by step tutorial for beginners regardless of previous knowledge on programming. In this tutorial and corresponding video, I provide a step-by-step tutorial and a ready-to-execute & download code script template to load or open and save your input and output csv data files from and into google sheets spreadsheets.

Code and data files templates are available to download in last part of this article.

• View a similar tutorial on reading and saving data from Google Drive: https://winswithdata.com/?p=1

Initial settings

Step1: initial settings and library for google sheet connection and authentication

Step 2: after running the code in first block, you are prompted a new opened window to login and connect your google account where your GSheet exists.

Step 3: Read the existing spreadsheet name in your google sheet account and the tab worksheet name of that spreadsheet you are focusing on

Let’s look at the name of sheet and worksheet for my example for better clarification

This is a sample data on online traffic volume of a website via different mediums (columns) in different dates.

Step 4: read the rows and data frame based on rows of the selected worksheet of your data

Reading Data is done!

Saving data into google sheets:

Now, we make a simple change to data and then save the new data in a new tab of same spreadsheet in your google sheet account.

• side note comment: The spreadsheet is open in your code, so no need to open it again

New sheet is called simple ‘NewSheet’ here.

Voilà, now let’s see the new data as we saved it!

Download related files:

• code script to execute in your google colab account: https://colab.research.google.com/drive/1GABY_57EYzOXSgWZ47hY1tUNEQmtzQg9?usp=sharing
• data sample file: https://docs.google.com/spreadsheets/d/1TxWBsmSaQomlhEly3FrfXL_MicTeieeCuywdI0pzpwI/edit?usp=sharing