In this article, you will learn how to export Excel data to Google Sheets programmatically 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.
- Aspose.Cells for Python - To read the data from Excel files.
- Google Client Libraries - To create and update spreadsheets on Google Sheets.
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.
5. Write another method named add_sheet to add a new sheet in the Google spreadsheet.
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.
7. Then, load the Excel XLS or XLSX file using Aspose.Cells and get the name of the first worksheet in the workbook.
8. Call the create_spreadsheet method to create a new spreadsheet on Google Sheets.
9. Loop through the worksheets in the Excel file. In each iteration, read data from the worksheet and add it to an array.
10. For each worksheet in the Excel file, create a request to write data to the Google Sheets.
The following is the complete function to export data from an Excel file to a spreadsheet in Google Sheets.
Complete Source Code
The following is the complete source code to export an Excel XLSX file to Google Sheets in Python.
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.