I have made a simple script using Python which imports a defined .csv file to a Google Sheet. A sample use case would be as follows -
- Lets say a daily cron runs which extracts employee attendance from database and exports it in .csv file
- A user will have to manually download this .csv and import it in Sheets, apply headers, set font size etc (DAILY)
This process can easily be automated and requires zero human interaction once the initial setup is done.
See the script in action here!
Here is a sample script that I made. This script create a new worksheet in a pre-defined spreadsheet where the name of the worksheet is current world date. It can be modified to whatever your use case requires.
You can always fetch the latest code from my GitHub repository -
This section will help you to get the credentials.json file needed for authentication.
- Create a free account on Google Cloud
- Create a new Project
- After your project is created go to Dashboard and search for Google Drive API and Google Sheets API. Enable both of them.
- Go to IAM > Service Accounts > Create a service account. You can name this account anything
- Here for test purposes we will give Editor access to our service account. You can play around and figure out which one suits for your application best
- After the Service Account is created open it and look for 'Keys' section
- Tap on Add Key then select Json as the key type. It will download a .json file which we will need for authentication
- The final step would be to give edit access to our Google Sheet by sharing the sheet with the service account email address. This email address can be found under Service Account details