CODEWITHSUNDEEP
pythonpandasgoogle-sheets-api
Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

how to read (or parse) google spreadsheet colums note(comment) using python

I've a Google Spreadsheet and there is a two column 

id , name
1     aaaa

and there a note for Colum name and row 1 that means in the cell(2,2) I am able to read data for tow column of the spreadsheet but could not read the note of that cell. I am using gspread library for reading data from the spreadsheet.

import gspread
from gspread_dataframe import get_as_dataframe

scope = ['https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/spreadsheets']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
            'jasonfileNlocation.json',
            scope)
google_spreadsheet_connection = gspread.authorize(credentials)

wks = google_spreadsheet_connection.open("spreadsheet_name")
worksheet = wks.get_worksheet(0)
df = get_as_dataframe(worksheet, evaluate_formulas=True, index='false')

for better understanding i have added below image enter image description here Any help will be appriciated

Upvotes: 1

Views: 3790

Answers (1)

Tanaike
Tanaike

Reputation: 201553

  • You want to retrieve notes of the cells on the Spreadsheet.
  • You want to achieve this using the Service account and Python.
  • In your script, ServiceAccountCredentials is used as from oauth2client.service_account import ServiceAccountCredentials.
  • You have already been able to put and get values for Spreadsheet using Sheets API with gspread.

If my understanding is correct, how about this answer? Unfortunately, it seems that the notes cannot be retrieved by gspread. So how about using google-api-python-client? In this answer, the notes of the cells are retrieved using the method of spreadsheets.get in Sheets API with google-api-python-client.

Modified script:

In this modification, your script was modified.

import gspread
import httplib2
from apiclient import discovery
from gspread_dataframe import get_as_dataframe
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://www.googleapis.com/auth/drive',
         'https://www.googleapis.com/auth/spreadsheets']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
            'jasonfileNlocation.json',
            scope)
google_spreadsheet_connection = gspread.authorize(credentials)
wks = google_spreadsheet_connection.open("spreadsheet_name")
worksheet = wks.get_worksheet(0)
df = get_as_dataframe(worksheet, evaluate_formulas=True, index='false')

# I added below script.
service = discovery.build(
    'sheets', 'v4', http=credentials.authorize(httplib2.Http()))

spreadsheet_id = '###'  # Please set the Spreadsheet ID here.
ranges = ['Sheet1!A2:B']  # For example, when you want to retrieve the note from the cells "A2:B" of "Sheet1", please use this.

fields = 'sheets(data(rowData(values(note,userEnteredValue))))'
request = service.spreadsheets().get(
    spreadsheetId=spreadsheet_id, ranges=ranges, fields=fields)
response = request.execute()
print(response)
  • I could understand that ID is the value of the column "A", and you want to the values of EMPID and `name together with the notes.

Result:

{
  "sheets": [
    {
      "data": [
        {
          "rowData": [
            {
              "values": [
                {"userEnteredValue": {"numberValue": 1}},
                {"note": "sample note1", "userEnteredValue": {"stringValue": "name1"}}
              ]
            },
            {
              "values": [
                {"userEnteredValue": {"numberValue": 2}},
                {"note": "sample note2", "userEnteredValue": {"stringValue": "name2"}}
              ]
            },
            ,
            ,
            ,

          ]
        }
      ]
    }
  ]
}
  • The index of rowData is the row number.
  • The index of values is the column number.

Note:

  • In this sample, the note of the cell "B2" in the sheet of "Sheet1" is retrieved. If you want to retrieve other cells, please modify this.
  • In this modified script, gspread can be also used.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 1

Related Questions