Search code examples
pythongoogle-sheetsgoogle-sheets-api

CSV to Google Sheets python

I have a CSV that I want to put into a google sheet into sheet3 of many. I was hoping someone can help me complete this code. I am using Google API. So far I have gotten the csv to upload to the google drive. Now I would like to change the code to update a specific google sheet in sheet3 instead of creating a new sheet. Bellow you will find the code that I am using to create a new sheet with the CSV data.

# Import Csv to Google Drive
import os
import glob

from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
gauth = GoogleAuth()           
drive = GoogleDrive(gauth)  

 
# line used to change the directory
os.chdir(r'DIRECTORY OF CSV')

list_of_files = glob.glob('DIRECTORY OF CSV\*') # * means all if need specific format then *.csv
latest_file = max(list_of_files, key=os.path.getctime)
print(latest_file)

upload_file_list = [latest_file]
for upload_file in upload_file_list:
    gfile = drive.CreateFile({'parents': [{'id': 'THE GOOGLE ID'}]})
    # Read file and set it as the content of this instance.
    gfile.SetContentFile(upload_file)
    gfile.Upload() # Upload the file.
Solution

  • I believe your goal is as follows.

    • You want to put CSV data to the specific sheet of a Google Spreadsheet.
    • You want to achieve this using python.
    • You have already been able to get and put values to the Spreadsheet using Sheets API.

    In this case, how about the following sample script?

    Sample script 1:

    When googleapis for python is used, how about the following sample script?

    service = build("sheets", "v4", credentials=creds)  # Please use your script for authorization.
spreadsheet_id = "###"  # Please put your Spreadsheet ID.
sheet_name = "Sheet3"  # Please put the sheet ID of the sheet you want to use.
csv_file = "###"  # Please put the file path of the CSV file you want to use.

f = open(csv_file, "r")
values = [r for r in csv.reader(f)]
request = service.spreadsheets().values().update(spreadsheetId=spreadsheet_id, range=sheet_name, valueInputOption="USER_ENTERED", body={"values": values}).execute()

    Sample script 2:

    When gspread for python is used, how about the following sample script?

    import gspread
import csv

client = gspread.oauth(###) # Please use your script for authorization.
spreadsheet_id = "###"  # Please put your Spreadsheet ID.
sheet_name = "Sheet3"  # Please put the sheet ID of the sheet you want to use.
csv_file = "###"  # Please put the file path of the CSV file you want to use.

spreadsheet = client.open_by_key(spreadsheet_id)
worksheet = spreadsheet.worksheet(sheet_name)
f = open(csv_file, "r")
values = [r for r in csv.reader(f)]
worksheet.update(values)

    Note:

    • About both sample scripts, the CSV data is retrieved from a CSV file on your local PC, and the CSV data is converted to a 2-dimensional array and put the array to "Sheet3" of Google Spreadsheet using Sheets API. In this sample script, Drive API is not used.

    Reference: