In this article, you will learn how to export Excel data to Google Sheets programmatically in Python.

Export Excel Files to Google Sheets in Python

Excel files are widely used to store the data and perform various types of operations on it, such as generating charts, applying formulas. On the other hand, Google Sheets is a popular online application for creating and manipulating spreadsheets. Google Sheets also provides real-time sharing of spreadsheets with multiple people. In certain cases, you may need to export Excel XLS or XLSX files to Google Sheets programmatically. To achieve that, this article provides a complete guide on how to set up a Google project and export data from Excel files to Google Sheets in Python.

Prerequisites - Export Excel Data to Google Sheets in Python

Google Cloud Project

To communicate with Google Sheets, we will have to create a project on Google Cloud and enable Google Sheets API. Also, we need to create credentials that are used to authorize the actions we are going to perform with our code. You can read the guidelines on how to create a Google Cloud project and enable Google Sheets API.

After creating the Google Cloud project and enabling the Google Sheets API, we can proceed to install the following APIs in our Python application.

Python Libraries to Export Excel Files to Google Sheets

To export data from Excel XLS/XLSX files to Google Sheets, we will need the following APIs.

Export Data from Excel to Google Sheets in Python

The following is the step-by-step guide on how to read data from an Excel XLSX file and write it to Google Sheets in a Python application.

1. Create a new Python application.

2. Install Aspose.Cells and Google client libraries in the project.

pip install aspose.cells
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

3. Place the JSON file (we have downloaded after creating credentials in Google Cloud) into the project’s directory.

4. Write a method named create_spreadsheet that creates a new spreadsheet on Google Sheets, sets the name of the default sheet, and returns the ID of the spreadsheet.

def create_spreadsheet(_service, _title, _sheetName):
# Spreadsheet details
spreadsheetBody = {
'properties': {
'title': "{0}".format(_title)
},
'sheets': {
'properties': {
'title' : "{0}".format(_sheetName)
}
}
}
# Create spreadsheet
spreadsheet = _service.spreadsheets().create(body=spreadsheetBody,
fields='spreadsheetId').execute()
print('Spreadsheet ID: {0}'.format(spreadsheet.get('spreadsheetId')))
print('Spreadsheet URL: "https://docs.google.com/spreadsheets/d/{0}'.format(spreadsheet.get('spreadsheetId')))
# Open in web browser
webbrowser.open_new_tab("https://docs.google.com/spreadsheets/d/{0}".format(spreadsheet.get('spreadsheetId')))
return spreadsheet.get('spreadsheetId')
view raw create_spreadsheet.py hosted with ❤ by GitHub

5. Write another method named add_sheet to add a new sheet in the Google spreadsheet.

def add_sheet(_service, _spreadsheetID, _sheetName):
data = {'requests': [
{
'addSheet':{
'properties':{'title': '{0}'.format(_sheetName)}
}
}
]}
# Execute request
res = _service.spreadsheets().batchUpdate(spreadsheetId=_spreadsheetID, body=data).execute()
view raw add_sheet.py hosted with ❤ by GitHub

6. Now, initialize the Google Sheets service using the credentials (JSON file) and define the scopes of the application. The scopes parameter is used to specify the access permissions to Google Sheets and their properties.

# If modifying these scopes, delete the file token.json.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
creds = None
# The file token.json stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.
if os.path.exists('token.json'):
creds = Credentials.from_authorized_user_file('token.json', SCOPES)
# If there are no (valid) credentials available, let the user log in.
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(
'credentials1.json', SCOPES)
creds = flow.run_local_server(port=0)
# Save the credentials for the next run
with open('token.json', 'w') as token:
token.write(creds.to_json())
service = build('sheets', 'v4', credentials=creds)
view raw connect_to_google.py hosted with ❤ by GitHub

7. Then, load the Excel XLS or XLSX file using Aspose.Cells and get the name of the first worksheet in the workbook.

# Load Excel workbook
wb = Workbook(fileName)
# Get worksheets collection
collection = wb.getWorksheets()
collectionCount = collection.getCount()
# Get workbook and first sheet's name
spreadsheetName = wb.getFileName()
firstSheetName = collection.get(0).getName()
view raw load_excel_file.py hosted with ❤ by GitHub

8. Call the create_spreadsheet method to create a new spreadsheet on Google Sheets.

# Create spreadsheet on Google Sheets
spreadsheetID = create_spreadsheet(service, spreadsheetName, firstSheetName)
view raw create_new_spreadsheet.py hosted with ❤ by GitHub

9. Loop through the worksheets in the Excel file. In each iteration, read data from the worksheet and add it to an array.

# Loop through all the worksheets
for worksheetIndex in range(collectionCount):
# Get worksheet using its index
worksheet = collection.get(worksheetIndex)
# Set worksheet range
if(worksheetIndex==0):
sheetRange= "{0}!A:Y".format(firstSheetName)
else:
add_sheet(service, spreadsheetID, worksheet.getName())
sheetRange= "{0}!A:Y".format(worksheet.getName())
# Get number of rows and columns
rows = worksheet.getCells().getMaxDataRow()
cols = worksheet.getCells().getMaxDataColumn()
# List to store worksheet's data
worksheetDatalist = []
# Loop through rows
for i in range(rows):
# List to store each row in worksheet
rowDataList = []
# Loop through each column in selected row
for j in range(cols):
cellValue = worksheet.getCells().get(i, j).getValue()
if( cellValue is not None):
rowDataList.append(str(cellValue))
else:
rowDataList.append("")
# Add to worksheet data
worksheetDatalist.append(rowDataList)
view raw read_excel_file.py hosted with ❤ by GitHub

10. For each worksheet in the Excel file, create a request to write data to the Google Sheets.

# Set values
body = {
'values': worksheetDatalist
}
# Execute request
result = service.spreadsheets().values().update(
spreadsheetId=spreadsheetID, range=sheetRange,
valueInputOption='USER_ENTERED', body=body).execute()
# Print number of updated cells
print('{0} cells updated.'.format(result.get('updatedCells')))
view raw write_to_google_sheet.py hosted with ❤ by GitHub

The following is the complete function to export data from an Excel file to a spreadsheet in Google Sheets.

def export_to_google(fileName):
# If modifying these scopes, delete the file token.json.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
creds = None
# The file token.json stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.
if os.path.exists('token.json'):
creds = Credentials.from_authorized_user_file('token.json', SCOPES)
# If there are no (valid) credentials available, let the user log in.
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(
'credentials1.json', SCOPES)
creds = flow.run_local_server(port=0)
# Save the credentials for the next run
with open('token.json', 'w') as token:
token.write(creds.to_json())
try:
service = build('sheets', 'v4', credentials=creds)
# Load Excel workbook
wb = Workbook(fileName)
# Get worksheets collection
collection = wb.getWorksheets()
collectionCount = collection.getCount()
# Get workbook and first sheet's name
spreadsheetName = wb.getFileName()
firstSheetName = collection.get(0).getName()
# Create spreadsheet on Google Sheets
spreadsheetID = create_spreadsheet(service, spreadsheetName, firstSheetName)
# To set worksheet range
sheetRange = None
# Loop through all the worksheets
for worksheetIndex in range(collectionCount):
# Get worksheet using its index
worksheet = collection.get(worksheetIndex)
# Set worksheet range
if(worksheetIndex==0):
sheetRange= "{0}!A:Y".format(firstSheetName)
else:
add_sheet(service, spreadsheetID, worksheet.getName())
sheetRange= "{0}!A:Y".format(worksheet.getName())
# Get number of rows and columns
rows = worksheet.getCells().getMaxDataRow()
cols = worksheet.getCells().getMaxDataColumn()
# List to store worksheet's data
worksheetDatalist = []
# Loop through rows
for i in range(rows):
# List to store each row in worksheet
rowDataList = []
# Loop through each column in selected row
for j in range(cols):
cellValue = worksheet.getCells().get(i, j).getValue()
if( cellValue is not None):
rowDataList.append(str(cellValue))
else:
rowDataList.append("")
# Add to worksheet data
worksheetDatalist.append(rowDataList)
# Set values
body = {
'values': worksheetDatalist
}
# Execute request
result = service.spreadsheets().values().update(
spreadsheetId=spreadsheetID, range=sheetRange,
valueInputOption='USER_ENTERED', body=body).execute()
# Print number of updated cells
print('{0} cells updated.'.format(result.get('updatedCells')))
except HttpError as err:
print(err)
print("Workbook has been exported to Google Sheets.")
view raw export_data_from_excel_to_google.py hosted with ❤ by GitHub

Complete Source Code

The following is the complete source code to export an Excel XLSX file to Google Sheets in Python.

from __future__ import print_function
import jpype
import webbrowser
import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
import asposecells
jpype.startJVM()
from asposecells.api import Workbook, License
def export_to_google(fileName):
# If modifying these scopes, delete the file token.json.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
creds = None
# The file token.json stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.
if os.path.exists('token.json'):
creds = Credentials.from_authorized_user_file('token.json', SCOPES)
# If there are no (valid) credentials available, let the user log in.
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(
'credentials1.json', SCOPES)
creds = flow.run_local_server(port=0)
# Save the credentials for the next run
with open('token.json', 'w') as token:
token.write(creds.to_json())
try:
service = build('sheets', 'v4', credentials=creds)
# Load Excel workbook
wb = Workbook(fileName)
# Get worksheets collection
collection = wb.getWorksheets()
collectionCount = collection.getCount()
# Get workbook and first sheet's name
spreadsheetName = wb.getFileName()
firstSheetName = collection.get(0).getName()
# Create spreadsheet on Google Sheets
spreadsheetID = create_spreadsheet(service, spreadsheetName, firstSheetName)
# To set worksheet range
sheetRange = None
# Loop through all the worksheets
for worksheetIndex in range(collectionCount):
# Get worksheet using its index
worksheet = collection.get(worksheetIndex)
# Set worksheet range
if(worksheetIndex==0):
sheetRange= "{0}!A:Y".format(firstSheetName)
else:
add_sheet(service, spreadsheetID, worksheet.getName())
sheetRange= "{0}!A:Y".format(worksheet.getName())
# Get number of rows and columns
rows = worksheet.getCells().getMaxDataRow()
cols = worksheet.getCells().getMaxDataColumn()
# List to store worksheet's data
worksheetDatalist = []
# Loop through rows
for i in range(rows):
# List to store each row in worksheet
rowDataList = []
# Loop through each column in selected row
for j in range(cols):
cellValue = worksheet.getCells().get(i, j).getValue()
if( cellValue is not None):
rowDataList.append(str(cellValue))
else:
rowDataList.append("")
# Add to worksheet data
worksheetDatalist.append(rowDataList)
# Set values
body = {
'values': worksheetDatalist
}
# Execute request
result = service.spreadsheets().values().update(
spreadsheetId=spreadsheetID, range=sheetRange,
valueInputOption='USER_ENTERED', body=body).execute()
# Print number of updated cells
print('{0} cells updated.'.format(result.get('updatedCells')))
except HttpError as err:
print(err)
print("Workbook has been exported to Google Sheets.")
def create_spreadsheet(_service, _title, _sheetName):
# Spreadsheet details
spreadsheetBody = {
'properties': {
'title': "{0}".format(_title)
},
'sheets': {
'properties': {
'title' : "{0}".format(_sheetName)
}
}
}
# Create spreadsheet
spreadsheet = _service.spreadsheets().create(body=spreadsheetBody,
fields='spreadsheetId').execute()
# Open in web browser
webbrowser.open_new_tab("https://docs.google.com/spreadsheets/d/{0}".format(spreadsheet.get('spreadsheetId')))
return spreadsheet.get('spreadsheetId')
def add_sheet(_service, _spreadsheetID, _sheetName):
data = {'requests': [
{
'addSheet':{
'properties':{'title': '{0}'.format(_sheetName)}
}
}
]}
# Execute request
res = _service.spreadsheets().batchUpdate(spreadsheetId=_spreadsheetID, body=data).execute()
# Create a Aspose.Cells icense object
license = License()
# Set the license of Aspose.Cells to avoid the evaluation limitations
license.setLicense("D:\\Licenses\\Conholdate.Total.Product.Family.lic")
export_to_google("Book1.xlsx")
view raw export_excel_to_google_sheets.py hosted with ❤ by GitHub

Get a Free Aspose.Cells License

You can get a free temporary license and use Aspose.Cells for Python without evaluation limitations.

Conclusion

In this article, you have learned how to export Excel data to Google Sheets in Python. We have covered how to create a project on Google Cloud, enable Google Sheets API, read Excel files, and export data from Excel files to Google Sheets. To explore more about Aspose.Cells for Python, you can visit the documentation. Also, you can ask your questions via our forum.

See Also