Google sheets API<\/figcaption><\/figure>\n\n\n\n2. Enable Google Sheets API<\/strong><\/p>\n\n\n\n\nNow that you have a GCP project, you need to enable the specific API you want to use: Google Sheets API.<\/li>\n\n\n\n In the GCP Console navigation bar, go to the “APIs & Services” section.<\/li>\n\n\n\n Select “Library” from the submenu. This is where you’ll find all available Google Cloud APIs.<\/li>\n\n\n\n Use the search bar to find “Google Sheets API.”<\/li>\n\n\n\n Once you locate it, click on the API name to access its details.<\/li>\n\n\n\n On the API details page, click the “Enable” button to activate the Google Sheets API for your project.<\/li>\n<\/ul>\n\n\n\nTo create service account<\/figcaption><\/figure>\n\n\n\nService accounts<\/figcaption><\/figure>\n\n\n\nCreating new service account<\/figcaption><\/figure>\n\n\n\nAdding new key<\/figcaption><\/figure>\n\n\n\n3. Create a New Service Account<\/strong><\/p>\n\n\n\n\nService accounts are special Google accounts used by applications to access Google Cloud resources. For interacting with the Sheets API, you’ll need a service account.<\/li>\n\n\n\n In the GCP Console navigation bar, go to “IAM & Admin” and then select “Service Accounts.”<\/li>\n\n\n\n Click the “Create Service Account” button.<\/li>\n\n\n\n Give your service account a descriptive name (e.g., “Sheets API Access”).<\/li>\n\n\n\n Optionally, describe your service account.<\/li>\n\n\n\n Click “Create” to establish the new service account.<\/li>\n\n\n\n Click on the key to add a new key<\/li>\n<\/ul>\n\n\n\n4. Download the JSON File<\/strong><\/p>\n\n\n\n\nOnce your service account is created, you need to download its credentials in JSON format. These credentials will be used to authenticate your application with Google when accessing the Sheets API.<\/li>\n\n\n\n Click on the newly created service account name in the list.<\/li>\n\n\n\n Go to the “Keys” tab.<\/li>\n\n\n\n Click the “Add Key” button and select “Create new key.”<\/li>\n\n\n\n Choose the JSON format for the key type.<\/li>\n\n\n\n Click “Create” to download the JSON file. <\/li>\n<\/ul>\n\n\n\nProgram side:<\/strong><\/p>\n\n\n\nInstalled the required libraries<\/p>\n\n\n\n
!pip install google-cloud\r\n!pip install gspread<\/code><\/pre>\n\n\n\nUpload the JSON file, which you downloaded from the google API to the working directory<\/p>\n\n\n\n
Once you uploaded, write the following program to connect to the google sheet. <\/p>\n\n\n\n
import gspread\r\nfrom google.oauth2.service_account import Credentials\r\nimport pandas as pd\r\n\r\n# Authenticate using the uploaded JSON file\r\nscope = ['https:\/\/spreadsheets.google.com\/feeds', 'https:\/\/www.googleapis.com\/auth\/drive']\r\ncreds = Credentials.from_service_account_file('\/content\/your JSON file', scopes=scope)\r\ngc = gspread.authorize(creds)\r\n\r\n# Open the Google Sheet by its URL\r\nsheet_url = 'your-url'\r\nsheet = gc.open_by_url(sheet_url)\r\n\r\n# Select the first worksheet\r\nworksheet = sheet.get_worksheet(0)\r\n\r\n# Read data from the worksheet into a Pandas DataFrame\r\ndata = worksheet.get_all_values()\r\ndf = pd.DataFrame(data[1:], columns=data[0]) # Assuming the first row contains column headers\r\n\r\n# Display the DataFrame\r\nprint(df)<\/code><\/pre>\n\n\n\nIn the above program, replace the google sheet URL and json file names according to your project. <\/p>\n\n\n\nOutput of the program<\/figcaption><\/figure>\n\n\n\n<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Here is the step-by-step guide to connecting Google Sheets to any Python program. In this tutorial, we connect it to Google Collab, but you can connect it to any application with the following the below points. Requirement: 1. Create a New Google Cloud Platform (GCP) Project 2. Enable Google Sheets API 3. Create a New Service Account 4. Download the JSON File Program side: Installed the required libraries Upload the JSON file, which you downloaded from the google API to…<\/p>\n
Read More Read More<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":3468,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[489],"tags":[592,593],"class_list":["post-3452","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-driven-applications-using-mongodb-python-and-google-colab","tag-googel-api","tag-google-sheet-api"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t
![\"\"](\"https:\/\/i0.wp.com\/afzalbadshah.com\/wp-content\/uploads\/2024\/05\/image-4.png?resize=640%2C306&ssl=1\")
![\"\"](\"https:\/\/i0.wp.com\/afzalbadshah.com\/wp-content\/uploads\/2024\/05\/image-5.png?resize=640%2C319&ssl=1\")
![\"\"](\"https:\/\/i0.wp.com\/afzalbadshah.com\/wp-content\/uploads\/2024\/05\/image-6.png?resize=640%2C311&ssl=1\")
![\"\"](\"https:\/\/i0.wp.com\/afzalbadshah.com\/wp-content\/uploads\/2024\/05\/image-8.png?resize=640%2C289&ssl=1\")
![\"\"](\"https:\/\/i0.wp.com\/afzalbadshah.com\/wp-content\/uploads\/2024\/05\/image-9.png?resize=640%2C278&ssl=1\")
![\"\"](\"https:\/\/i0.wp.com\/afzalbadshah.com\/wp-content\/uploads\/2024\/05\/image-10.png?resize=640%2C360&ssl=1\")
![\"\"](\"https:\/\/i0.wp.com\/afzalbadshah.com\/wp-content\/uploads\/2024\/05\/image-12.png?resize=640%2C323&ssl=1\")
![\"\"](\"https:\/\/i0.wp.com\/afzalbadshah.com\/wp-content\/uploads\/2024\/05\/image-2.png?resize=640%2C321&ssl=1\")