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.

Requirements

• Python 3.7+

• googleapliclient

• A google sheets ID

• A google service account

Preparation

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: andybravo@appname-202107.iam.gserviceaccount.com

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”

Cool!

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:

https://docs.google.com/spreadsheets/d/15s9cg-pnvZzyeV1khBZh4FwLQbovL7Q/ 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: myserviceaccount@appname-202107.iam.gserviceaccount.com

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 = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive',
         'https://www.googleapis.com/auth/drive.file']
SPREADSHEET_ID = '15s9cg-pnvZzyeV1khBZh4FwLQbovL7Q'
SERVICE_ACCOUNT_FILE = 'my_path/sheets-service-account.json.json'

def get_service():
   """
   Generates a service client for sending requests
   Returns:
       googleapiclientservice
   """
   credentials = ServiceAccountCredentials.from_json_keyfile_name(
       SERVICE_ACCOUNT_FILE,
       scopes=SCOPES)
   return discovery.build('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(
       spreadsheetId=SPREADSHEET_ID,
       body=request_body)
   try:
       response = request.execute()
       pprint(response)
   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
   Args:
       service: googleapiclient service
       tab: Tab name
       data: Data to write in format [[]]
       index: Position for start writing
   """
   resource = {
           "majorDimension": "ROWS",
           "values": data
   }
   try:
       request = service.spreadsheets().values()
                 .append(spreadsheetId=SPREADSHEET_ID,
                         range=f'{tab}!a{index}',
                         body=resource,
                         valueInputOption='RAW')
       request.execute()
   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
   Args:
       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()
   try:
       tab = "My tab"
       create_tab(service, tab)
       data = generate_random_data()
       prepare_data(service, data, tab, "My title")
   except HttpError as ex:
       print(ex)
       return
       
execute()

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

Conclusion

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.