What is the article about?
In this article, I will briefly describe the process of connecting any Python script to the Google Sheets API. To programmatically be able to edit Google tables. Now that everything has been cleared up, you can move on to setting up Google Cloude and creating the necessary access keys.
Set up Google Cloud and create an access key
And from the very beginning I will send you to another site - https://console.cloud.google.com/. It is the place from where we will create a separate project and connect to it the corresponding API for Google Sheets. Of course, you will need to have a Google account to log in.
Next, you will need to create a "project". This is done by pressing Ctrl-O or as shown in the image below:
Please don't forget to go to the previously created project by clicking on its name. After that, you will need to connect the API we need from the library - Google Sheets.
Find the required API through the search.
By clicking on this API, you will need to activate it. Press the "Enable" button.
After clicking, you will be redirected to the API management page. We just have to create the corresponding access keys and download them.
To do this, click on the "+ Create Credentials" button and select Create "Service Account".
You will be redirected to a new page, you will need to give a name to this service account plus provide the appropriate permissions to edit tables.
It remains only to generate the keys and download them to the machine on which your Python script will be launched.
Now that we have the necessary key, we must provide editing access previously created by the service account. This is done in the table that will need to be edited. More on this further.
Delegate access to the table
Copy the address of the service account
And then create a table with which you want to work accordingly. After granting access to the service account that was created in the previous chapter.
The main thing is to make sure that you have given the correct permissions for the service account. Click Submit and now you are ready to use this API to work with tables via Python.
Connect Python script to Google Sheet API
And, as always, we install the necessary packages in the first place. To do this, you will need to create a virtual environment and activate it. Then install the following packages:
- google-api-python-client - to interact with Google Sheets API
- google-auth-httplib2 - to be able to authenticate to the Google ecosystem
- google-auth-oauthlib - to be able to authenticate to the Google ecosystem
- gspread - for easy interaction with tables using python code
Move the previously downloaded file to the project directory and rename it to a more meaningful and simpler name to make it easier to refer to it from code.
Import the necessary modules:
Finally, connect to the table:
Now I'll explain. The cred.json file is your file you downloaded earlier. Then comes Sheet_id, this is a unique ID for each table. It can be obtained by opening the required table. access to which we provided service account earlier. here:
That is, sheet_id everything between /d/ and the next / and yes, I love Star Wars :)
Then it remains only to access the table through the variable Sheet and edit it to your discretion and the required tasks. For example, you can insert the following data:
Since this article is not about editing the tables themselves, I will not parse all the functionality of Gspread, but I will direct it to existing documentation.
Conclusion
Google Sheets are used almost everywhere and almost everyone. The same story about Python. Therefore, it is not surprising that these two "pieces", rather different software, easily integrate together and perfectly complement each other. And it will not be difficult for you to use this in your next works in the future.
