Excel to Google Sheets
Filename : C:\PythonPrograms\excel_to_google_sheets_with_python\demo_data_from_eshop_sales.xlsm
Python script
# copy_excel_to_googlesheets.py
import pandas as pd
from googleapiclient.discovery import build
from google.oauth2.service_account import Credentials
# ———————
# CONFIG
# ———————
excel_file = “demo_data_from_eshop_sales.xlsm”
sheet_name = “eshop_sales_data”
google_sheet_id = “GOOGLE SHEET ID”
credentials_file = “service_account.json”
# ———————
# LOAD EXCEL (reads only used rows)
# ———————
print(” Loading Excel file…”)
df = pd.read_excel(excel_file, sheet_name=sheet_name)
# Remove completely empty rows (just in case)
df.dropna(how=”all”, inplace=True)
# Replace NaN with empty strings (Google API requirement)
df = df.fillna(“”)
print(” Authenticating with Google…”)
# ———————
# AUTHENTICATE
# ———————
creds = Credentials.from_service_account_file(
credentials_file,
scopes=[“https://www.googleapis.com/auth/spreadsheets”]
)
service = build(“sheets”, “v4”, credentials=creds)
print(” Preparing data for upload…”)
# ———————
# PREPARE DATA FOR GOOGLE SHEETS
# ———————
values = [df.columns.tolist()] + df.astype(str).values.tolist()
print(” Clearing existing Google Sheet data…”)
# ———————
# CLEAR SHEET
# ———————
service.spreadsheets().values().clear(
spreadsheetId=google_sheet_id,
range=”A1:Z15000″
).execute()
# ———————
# UPLOAD
# ———————
service.spreadsheets().values().update(
spreadsheetId=google_sheet_id,
range=”A1″,
valueInputOption=”RAW”,
body={“values”: values}
).execute()
print(“⬆ Uploading data to Google Sheets…”)
print(“”)
print(“Upload successful — copied up to the last filled row.”)
print(“”)
input(“Press Enter to close…”)
VBA MACRO script to run python filename: copy_excel_to_googlesheets.py
Sub RunPythonScript()
Dim pythonExe As String
Dim scriptPath As String
Dim cmd As String
‘ Path to Python executable
pythonExe = “C:\Python310\python.exe”
‘ Path to your Python script
scriptPath = “C:\PythonPrograms\excel_to_google_sheets_with_python\copy_excel_to_googlesheets.py”
‘ Build command
cmd = “””” & pythonExe & “”” “”” & scriptPath & “”””
‘ Run command
Shell cmd, vbNormalFocus
End Sub
Views: 1
Comments are closed.