Python + Google API to import csv to Google Sheets

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!

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

GitHubishaanx

Authentication

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

Create a free account on Google Cloud
Create a new Project
After your project is created go to Dashboard and search for Google Drive API and Google Sheets API. Enable both of them.
Go to IAM > Service Accounts > Create a service account. You can name this account anything
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
After the Service Account is created open it and look for 'Keys' section
Tap on Add Key then select Json as the key type. It will download a .json file which we will need for authentication
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