When you need to access Google Sheets data from Python script you will need to prove that you have access to the resource. There are several options to authenticate to Google API, one of them is Service Account. We will show you how to get JSON file with credentials to access Google Sheets. Show StepsCreating JSON file with credentials to access Google Sheets API is fast and easy. It can be summarized with the following steps.
The schema showing the concept of how does it work. Create a new projectPlease open your Google Cloud console in your web browser. You can open it by navigating the https://console.cloud.google.com/. In this example, we will create a new project. It will store all details about our connection to Google Sheets API. In my opinion, it is good to have a separate project for accessing a Sheets, because in the case of emergency it is easy to revoke the access. If it is your first project then you will see the view similar like in the image below: If you already have some projects in your console, please click on project in the top menu bar, and you will have
dialog window with The form for project creation will be displayed. Just set the name and click After Project creation, you
should see view like in the image below. Please click Enable access to APIs in projectIn the next step, we will need to enable access to APIs for our project. The project will need to have access to:
Why do we need to give additional access to Google Drive API? Itโs because Google Sheets are stored in Google Drive. Changes made on Sheets are also changes on files in the Google Drive. Letโs search for When you found it, just click After API is enabled, you will see the below view. Please click on We need to search one more API. Please enter Like before, please enable it with Create Service Account credentialsAfter enabling both (Drive and Sheets) APIs you will have the view like in the image below. You can click
there The other option for credentials creation is to go to the project dashboard and click You will have a menu displayed with several options. Please select Service Account option will create a new account that we will use only to access Google Sheets API. Google Cloud will create a new email associated with Service Account. We will share selected Spreadsheets with that email. Such solution is good and safe in my opinion. We have an option to set a granular access to the resources that we want to share with our script. In the case of stolen credentials, we simply remove the credentials or even a whole project. Please fill the Service account name and click In the next step, set access to the project. In this example, I set the role to Create JSON file with credentialsAfter creating the Service Account you will see a view with your credentials. Please click on your Service Account. You will see its
details. Please select In the You will see a dialog where you can select the key format. Please select JSON (should be the default) and click Congratulations! The JSON file is ready to use in your Python scripts. Letโs test it. Authenticate in PythonI will use Python code that is generated with MLJAR Studio notebook:
This is how it looks in the MLJAR Studio: You should see the message that no spreadsheets are available. You will see also an email that is associated with Service account. You need to share Spreadsheets with that email. The email can be also checked in You share the Spreadsheet with Service Account email in the same way as you will share with a friend: Remove the projectIf you would like to remove the project, please click three dots near your
profile picture in the top right corner and select There, please click Thatโall, project is scheduled for removal. SummaryIn this article you have created a JSON file with credentials to authenticate Python code to access Google Sheets data. We use Service Account credentials. To have access to the selected Spreadsheet, it needs to be shared with email associated with Service Account. It is much safer apporach than sharing all your Spreadsheets at once. We are looking for feedback or comments from you. You can reach us by email to Ola. We are working on MLJAR Studio desktop application. It is a no-code framework for creating Python scripts. The application is still under the development. If you would like to be informed about the release please fill the form. ๐ Join our newsletter ๐Subscribe to our newsletter to receive product updates Share your Python Notebooks with othersHow do I share a Google spreadsheet with a service account?Share Google Sheet with Service Account ๐
Share the Google Sheet with the service account email (above) as an editor: Click that Share button in the top-right of your Sheet. (Name your Sheet if you haven't done so). The service account email should autocomplete for a valid service account.
Can Google Sheets connect to API?The Google Sheets API lets you read, write, and format Google Sheets data with your preferred programming language, including Java, JavaScript, and Python.
Is Googlesheet API free?All use of the Google Sheets API is available at no additional cost. Exceeding the quota request limits doesn't incur extra charges and your account is not billed.
How do I access Google services account drive?Procedure. Go to Google API Console.. Open the Service accounts page. If prompted, select your project.. Click CREATE SERVICE ACCOUNT.. In the Create service account window, type a name for the service account, select Furnish a new private key and then the key type JSON.. Click Create.. |