Download Click here to download full source code file
Google Sheets is an excellent tool for storing and managing data, and it can be particularly useful when dealing with data in CSV format. CSV (Comma Separated Values) files are a common format for data exchange and can be easily exported from many applications. However, once you have your data in a CSV file, it can be cumbersome to work with and analyze. In this blog, we will explore how to import CSV data into Google Sheets using Python.
Prerequisites:
we begin, you will need to have the following:
- A Google account
- A project on Google Cloud Console with the Google Sheets API enabled
- A service account with the necessary permissions to access the Google Sheets API
- The google-auth and google-api-python-client Python packages installed
User stages: These are specific to each user and can be used to load data from a local file system or from a cloud storage provider such as Amazon S3, Microsoft Azure, or Google Cloud Storage.
Import CSV Data into Google Sheets using Python:
Step 1: Set up a service account on the Google Cloud Console
To get started, you need to create a service account on the Google Cloud Console. This account will allow your Python script to access the Google Sheets API. Follow these steps to create a service account:
- Go to the Google Cloud Console (https://console.cloud.google.com/).
- Create a new project or select an existing one.
- Click on the navigation menu and select "IAM & Admin" > "Service accounts".
- Click on "Create Service Account".
- Enter a name for the service account and click on "Create".
- On the next screen, select the "Project" role and click on "Continue".
- On the "Create Key" screen, select "JSON" as the key type and click on "Create".
- Download the JSON file containing the service account credentials.
Step - 2 Install the required Python packages
You will need to install the google-auth and google-api-python-client Python packages to authenticate with the Google Sheets API and interact with Google Sheets.
Step 3: Authenticate with the Google Sheets API
To authenticate with the Google Sheets API, you need to provide your service account credentials. You can do this by creating a Credentials object from the credentials JSON file you downloaded in step 1. Here's some sample code that demonstrates how to authenticate with the Google Sheets API:
from __future__ import print_function
from googleapiclient.discovery import build
from google.oauth2 import service_account
import pandas as pd
import gspread
from gspread_dataframe import set_with_dataframe
# from oauth2client.service_account import ServiceAccountCredentials
import sys
import io
import requests
import pygsheets
import webbrowser
SCOPES = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]
credentials = service_account.Credentials.from_service_account_file('csvfilelinktogsheet.json', scopes=SCOPES)
spreadsheet_service = build('sheets', 'v4', credentials=credentials)
drive_service = build('drive', 'v3', credentials=credentials)
#you may use command to run multiple file using system arrguments
# url=sys.argv[1]
url="https://sample.com/file1.csv"
csvfilename = url.split("/")[-1]
def create(sheetId, csvfilename):
permission1 = {
'type': 'anyone',
'role': 'reader'
}
drive_service.permissions().create(fileId=sheetId, body=permission1).execute()
return sheetId
spreadsheet_details = {
'properties': {
'title': csvfilename
}
}
sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details,
fields='spreadsheetId').execute()
sheetId = sheet.get('spreadsheetId')
print('Spreadsheet ID: {0}'.format(sheetId))
create(sheetId, csvfilename)
s=requests.get(url).content
data_df=pd.read_csv(io.StringIO(s.decode('utf-8')))
# data_df
gc = gspread.service_account(filename='csvfilelinktogsheet.json')
print('Spreadsheet ID: {0}'.format(sheetId))
sh = gc.open_by_key(sheetId)
worksheet = sh.get_worksheet(0)
set_with_dataframe(worksheet, data_df)
webbrowser.open("https://docs.google.com/spreadsheets/d/"+sheetId+"/edit#gid=0")
# print("https://docs.google.com/spreadsheets/d/"+sheetId+"/edit#gid=0")
print(csvfilename)
Download Click here to download full source code file