• Andy Brave

How to write to google sheets with Python

In this tutorial, we will learn how to write to a Google Sheet through the official Google API. We are going to cover the main problems and how to solve them. If you are new to consuming and understanding APIs, this tutorial will help you.


  • Python 3.7+

  • googleapliclient

  • A google sheets ID

  • A google service account



The first thing for this project is creating the Google Service account for this project.

  1. Go to your Google Cloud Console

  2. Select your project

  3. Go to API & Credentials and create a new one

  4. Make the role owner for the service account (Member name = service account ID = service account email ex:

  5. Copy the email address of your service account = service account ID

  6. Add the role of owner

  7. Create a new key for the service account (We are going to download it in a JSON format). I’m going to call it “sheets-service-account.json”


Now we need to enable the API for Drive.

Go to APIs and services and enable the Google Drive API.

Now comes an essential step. I had stuck with this issue for two days until I found the reason. Here you have the trick.

The first thing we are going to do is the document creation for writing to them. Although you can do it through the API, we are going to work with an existing one for this tutorial.

  1. Once you have created the document, take the spreadsheets ID from the URL: this will be the identifier.

  1. Go to SHARE on the top right of your screen

  2. Go to advanced settings and share it with an email address of your service account ex:

Here comes the easiest part, the code.

import googleapiclient
from googleapiclient import discovery
from googleapiclient.errors import HttpError
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint
import numpy as np
import pandas as pd

SCOPES = ['', '',
SPREADSHEET_ID = '15s9cg-pnvZzyeV1khBZh4FwLQbovL7Q'
SERVICE_ACCOUNT_FILE = 'my_path/sheets-service-account.json.json'

def get_service():
   Generates a service client for sending requests
   credentials = ServiceAccountCredentials.from_json_keyfile_name(
   return'sheets', 'v4', credentials=credentials)
def create_tab(service, name_tab: str):
   Generates a new tab for writing to them. If tab exists raises an Exception
   request_body = {
       'requests': [{
           'addSheet': {
               'properties': {
                   'title': f'{name_tab}',
                   'tabColor': {
                       'red': 0.88,
                       'green': 0.99,
                       'blue': 0.50
   request = service.spreadsheets().batchUpdate(
       response = request.execute()
   except HttpError as err:
       raise err
def write_to_google_drive_sheet(service, tab: str, data: list, index: int):
   Writes data to a specified tab
       service: googleapiclient service
       tab: Tab name
       data: Data to write in format [[]]
       index: Position for start writing
   resource = {
           "majorDimension": "ROWS",
           "values": data
       request = service.spreadsheets().values()
   except HttpError as err:
       raise err
def generate_random_data():
   Utility for generate random data
   rng = np.random.default_rng()
   df = pd.DataFrame(rng.integers(0, 100, size=(50, 8)), columns=list('ABCDEFGH'))
   return df
def prepare_data(service:googleapiclient, df: pd.DataFrame, tab: str, title: str) -> int:
   Encapsulates the call to google API
       service: googleapiclient service
       data: Data to write
       index: Position for start writing
       tab: Tab
       title: Title for the data inside the sheet
   data = np.append([df.columns.to_list()], df.fillna(0).to_numpy(), axis=0)
   write_to_google_drive_sheet(service, tab, [[title]], 1)
   write_to_google_drive_sheet(service, tab, data.tolist(), 2)
def execute():
   service = get_service()
       tab = "My tab"
       create_tab(service, tab)
       data = generate_random_data()
       prepare_data(service, data, tab, "My title")
   except HttpError as ex:

And that’s all!! Here you can check the results:


The hardest thing was to realize how to let permissions to my google sheets document and connect them through the authentication client. But with this code, I’m pretty sure you won’t face those problems.

13 views0 comments

Recent Posts

See All