google sheet reading pytthon

1. Create a Google Cloud Platform (GCP) project

Go to the Google Cloud Console by clicking the link below :

Create a Google Cloud project | Google Workspace | Google for Developers

A Google Cloud project is required to use Google Workspace APIs and build Google Workspace add-ons or apps. This…

developers.google.com

The link will direct you to this page.

Create a new project by clicking ‘Go to create a project button on the above page’ or directly by using the link below. If you already have a project, you can select that one instead.

Google Cloud Platform

Google Cloud Platform lets you build, deploy, and scale applications, websites, and services on the same infrastructure…

console.cloud.google.com

The link will direct you to this page. Enter a project name and create it.

2. Enable the Google Sheets API

In the Cloud Console, navigate to the “APIs & Services” > “Library” section.

Search for “Google Sheets API” and enable it for your project.

Click on the ‘Enable’ button.

3. Create credentials

After enabling the API, go to the “APIs & Services” > “Credentials” section.

Click on the ‘Create credentials’ button and generate an API key.

Copy the API key for future use.

4. Manage Google Sheet Access Permissions

By default, Google Sheets are private. To make them accessible, you’ll need to share the Google Sheet publicly. Navigate to your Google Sheet and click on the ‘Share’ button.

Ensure you choose ‘Anyone with the link’ and set the access to ‘View.

5. Access Sheet Data

Below is the API endpoint to access sheet data. Please update the sheet ID, sheet name, and API key accordingly. You can find the sheet ID in the URL of your Google Sheet page. Look for the string of characters between ‘/d/’ and ‘/edit’ in the URL, and that is your sheet ID.

https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}/values/{SHEET_NAME}!A1:Z?alt=json&key={API_KEY}

Next, open the provided URL in your web browser, and you will receive a response similar to this.

{
  'range': 'demo_sheet!A1:Z1000',
  'majorDimension': 'ROWS',
  'values': [
    [
      'Email id',
      'Password',
      'count',
      'id'
    ],
    [
      'demo@gmail.com',
      'demopass',
      '60',
      '0'
    ]
  ]
}

6. Retrieve Sheet Data Using Python

Now, let’s write Python code to access this data.

import requests

def get_google_sheet_data(spreadsheet_id,sheet_name, api_key):
    # Construct the URL for the Google Sheets API
    url = f'https://sheets.googleapis.com/v4/spreadsheets/{spreadsheet_id}/values/{sheet_name}!A1:Z?alt=json&key={api_key}'

    try:
        # Make a GET request to retrieve data from the Google Sheets API
        response = requests.get(url)
        response.raise_for_status()  # Raise an exception for HTTP errors

        # Parse the JSON response
        data = response.json()
        return data

    except requests.exceptions.RequestException as e:
        # Handle any errors that occur during the request
        print(f"An error occurred: {e}")
        return None


# configurations
spreadsheet_id = ''
api_key = ''
sheet_name = "Sheet1"

sheet_data = get_google_sheet_data(spreadsheet_id,sheet_name, api_key)

if sheet_data:
    print(sheet_data)
else:
    print("Failed to fetch data from Google Sheets API.")

That’s all for now. Thank you for reading! Please feel free to share your suggestions in the comments below.

Python

Google Sheets

 
https://medium.com/@techworldthink/accessing-google-sheet-data-with-python-a-practical-guide-using-the-google-sheets-api-dc57759d387a