Google Sheets is Google's version of Microsoft Excel. The fundamental difference is that Google Sheets is only accessed in your browser. Excel has both a desktop version, and a Browser based version.
Google Sheets is used by 160-180 million users compared to Excel which has 1.100-1500 million users. For once, Google is the little brother regarding spreadsheets. So why do people use Sheets instead of big daddy Excel?
The perks of Google Sheets
Gspread is a Python API for Google Sheets.
Google already has an API for Google sheets, so why use Gspread? Python users who need to connect to Google Sheets should use Gspread because they crafted it explicitly for that purpose. Gspread is based on the Google Sheets API and gives a much easier toolset for quickly interacting with Google Sheets.
There are two ways that you can authenticate to Google Sheets.You can setup a Project and give gspread access to your entire account with OAuth Client ID, or you can set up a Bot using a Service Account.
Creating a Service account means exactly that, it is a separate account and you will have to share all of your spreadsheets that you want to edit to this account.
We will walk through the setup of both methods in this article.
Go to Google Cloud Console and create a new project
Give the Project a name, for example Gspread-OAuth Client ID and optionally add it to an Organization, but this is not needed.
Go to APIs and services in your newly created projects dashboard.
Click on the + button "ENABLE APIS AND SERVICES"
Search for "Google Drive API" and enable it, and after that search for and enable "Google Sheets API"
If you'd rather setup a Service account, go to Service Account.
Navigate to the APIs and services menu on the left and click on "OAuth consent screen". Create an External user type.
Name the app, for example Gspread-OAuth Client ID and enter your Gmail address in the "User support email" field and in the "Developer contact information field". Click on "Save and continue" and do the same on the Scopes tab. Add your Gmail email again to the Test user field and then save and continue. Review your information in the summary and then click on "Back to dashboard".
Navigate to “APIs & Services > Credentials” and then click on "Create credentials" and choose "OAuth client ID". Select "Desktop app" as the Application type and name it, for "example Desktop client 1". Create the credentials.
Download the JSON file and rename it to "credentials.json". Create a folder named gspread in your %APPDATA% and move the file to the folder. %APPDATA% is a variable used in Windows that goes to your accounts appdata folder. For example, on my computer it is C:\Users\Conny\AppData\Roaming.
If you are using other operating system than Windows, put it in the folder ~/.config/ the full path would then be ~/.config/gspread/credentials.json.
All done! You can now go the Using gspread section if you do not need to create a Service Account.
A service account is a separate bot account that you have to share sheets to in order for the bot to get access to your sheets that you need to edit.
Navigate to “APIs & Services > Credentials” and click on “Create credentials > Service account”.
Fill in the details of the service account and click on "Done"
Go back to your newly created Service Account and click on the "Keys" tab and click on "Add key" and "Create new key".
Create a key of type JSON. It will download the key to your computer.
Rename the downloaded key to "service_account.json". Create a folder named gspread in your %APPDATA% and move the file to the folder. %APPDATA% is a variable used in Windows that goes to your accounts appdata folder. For example, on my computer it is C:\Users\Conny\AppData\Roaming.
If you are using other operating system than Windows, put it in the folder ~/.config/ the full path would then be ~/.config/gspread/service_account.json.
Create a new Google Sheet and give it the name "Gspread Service Account test". Enter "Hello from cell A1" in Cell A1. Share it with the Service Accounts email. You can find the email from the downloaded key file under "client_email". We will open this spreadsheet with the bot later.
All set! Let's move on to trying out gspread.
Open up the Command Prompt or your terminal and write pip install gspread
Be extremely cautious not to disclose the authorized credentials and credentials file to anyone. If anyone gets access to these files, they can access and edit (including deleting!) your content on Google Drive.
The first time that you run the script, a browser window will open and prompt you to sign in with your google account. The config directory is where gspread stores authorized credentials, along with credentials.json. You only need to authorize the browser once, and it will remember your credentials.
First we import gspread, and then we authenticate with the oauth() method. We then open the spreadsheet and print the contents of cell A1 on the first sheet.
Creating a spreadsheet is like opening a spreadsheet. We just need to change the open()
to create()
instead.
Instead of using oauth()
we now use service_account()
for authenticating. Remember the spreadsheet we created earlier? When we run this code, we get the output [['Hello from cell A1']]
.
When we create a spreadsheet with the service account, we need to share the spreadsheet to our account. Remember that the Service Account is a separate account. Just as you would share the spreadsheet with another person, you have to do this as well with gspread using the share()
method.
After sharing to your email, you will get a notice.
To specify an alternate location for the credentials file, change the path to credentials.json and authorized_user.json in the oauth()
method.
Sources: https://docs.gspread.org/en/latest/oauth2.html
Code is available on my GitHub