Python + Google API to import csv to Google Sheets

Coding May 19, 2021

I have made a simple script using Python which imports a defined .csv file to a Google Sheet. A sample use case would be as follows -

  • Lets say a daily cron runs which extracts employee attendance from database and exports it in .csv file
  • A user will have to manually download this .csv and import it in Sheets, apply headers, set font size etc (DAILY)

This process can easily be automated and requires zero human interaction once the initial setup is done.

See the script in action here!

See it In Action!

Here is a sample script that I made. This script create a new worksheet in a pre-defined spreadsheet where the name of the worksheet is current world date. It can be modified to whatever your use case requires.

# Libraries
import gspread
from datetime import date
from oauth2client.service_account import ServiceAccountCredentials

# Authorization
scope = [
    "https://spreadsheets.google.com/feeds",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive.file",
    "https://www.googleapis.com/auth/drive",
]
credentials = ServiceAccountCredentials.from_json_keyfile_name("credentials.json", scope)
client = gspread.authorize(credentials)

# Variables definition
today = date.today()
curr_date = today.strftime("%m/%d/%y")
spreadsheet = client.open("MySpreadsheet")
worksheet = spreadsheet.add_worksheet(title=curr_date, rows="1000", cols="5")
curr_sheet = curr_date + '!' + 'A1'

# Import CSV Function
def ImportCsv(csvFile, sheet, cell):
    """
    csvFile - path to csv file to import
    sheet - a gspread.Spreadsheet object
    cell - string giving starting cell, optionally including sheet/tab name
      ex: 'A1', 'Sheet2!A1', etc.
    """
    if "!" in cell:
        (tabName, cell) = cell.split("!")
        wks = sheet.worksheet(tabName)
    else:
        wks = sheet.sheet1
    (firstRow, firstColumn) = gspread.utils.a1_to_rowcol(cell)

    with open(csvFile, "r") as f:
        csvContents = f.read()
    body = {
        "requests": [
            {
                "pasteData": {
                    "coordinate": {
                        "sheetId": wks.id,
                        "rowIndex": firstRow - 1,
                        "columnIndex": firstColumn - 1,
                    },
                    "data": csvContents,
                    "type": "PASTE_NORMAL",
                    "delimiter": ",",
                }
            }
        ]
    }
    return sheet.batch_update(body)


ImportCsv("data.csv", spreadsheet, curr_sheet)
ImportCsv.py

You can always fetch the latest code from my GitHub repository -

ishaanx/csv-to-gsheets
Import csv files to google sheets using Sheets API - ishaanx/csv-to-gsheets

Authentication

This section will help you to get the credentials.json file needed for authentication.

  1. Create a free account on Google Cloud
  2. Create a new Project
  3. After your project is created go to Dashboard and search for Google Drive API and Google Sheets API. Enable both of them.
  4. Go to IAM > Service Accounts > Create a service account. You can name this account anything
  5. Here for test purposes we will give Editor access to our service account. You can play around and figure out which one suits for your application best
  6. After the Service Account is created open it and look for 'Keys' section
  7. Tap on Add Key then select Json as the key type. It will download a .json file which we will need for authentication
  8. The final step would be to give edit access to our Google Sheet by sharing the sheet with the service account email address. This email address can be found under Service Account details

Tags

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.