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:

1. Go to the Google Cloud Console (https://console.cloud.google.com/).
2. Create a new project or select an existing one.
3. Click on the navigation menu and select "IAM & Admin" > "Service accounts".
4. Click on "Create Service Account".
5. Enter a name for the service account and click on "Create".
6. On the next screen, select the "Project" role and click on "Continue".
7. On the "Create Key" screen, select "JSON" as the key type and click on "Create".
8. 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