I retrieved the comments of particular cell in my google spreadsheet using their API with the OAUTH_SCOPE = "https://www.googleapis.com/auth/drive" and version 3.
I get an output which is of this form:
{'kind': 'drive#comment', 'id': 'AAAAnggKMaA', 'createdTime': '2023-01-18T08:56:39.693Z', 'modifiedTime': '2023-01-18T09:03:32.426Z', 'author': {'kind': 'drive#user', 'displayName': 'Andrew Flint', 'photoLink': '//lh3.googleusercontent.com/a/AFBCDEDF3BjIhc6Hgtsb5kDdzVt54vIjG3q0W8d1CYi=s50-c-k-no', 'me': True}, 'htmlContent': 'No version specified in current.json', 'content': 'No version specified in current.json', 'deleted': False, 'resolved': False, 'anchor': '{"type":"workbook-range","uid":0,"range":"1713668520"}', 'replies': [{'kind': 'drive#reply', 'id': 'AAAAnggKMaE', 'createdTime': '2023-01-18T09:03:32.426Z', 'modifiedTime': '2023-01-18T09:03:32.426Z', 'author': {'kind': 'drive#user', 'displayName': 'Andrew Flint', 'photoLink': '//lh3.googleusercontent.com/a/ADDDGyFTp7mR3BjIhc6Hgtsb5kDdzVt54vIjG3q0W8d1CYi=s50-c-k-no', 'me': True}, 'htmlContent': 'Unable to find a package version URLfor Mono-Extended. Found\xa0 somewhat matching package details here :\xa0<a href="https://www.google.com/url?q=https://aur.archlinux.org/packages/nerd-fonts-noto-sans-mono-extended&sa=D&source=editors&ust=1674887508573584&usg=AOvVaw3HrzXUBfEBO0hr7RB5YLxH" data-rawHref="https://aur.archlinux.org/packages/nerd-fonts-noto-sans-mono-extended" target="_blank">https://aur.archlinux.org/packages/nerd-fonts-noto-sans-mono-extended</a> but not sure if this is the intended package', 'content': 'Unable to find a package version URLfor Mono-Extended. Found\xa0 somewhat matching package details here :\xa0https://aur.archlinux.org/packages/nerd-fonts-noto-sans-mono-extended but not sure if this is the intended package', 'deleted': False}]}
I now want to associate this comment with that particular row from which this comment was extracted through a python script; i.e. I want to be able to know the row index of the cell from which this comment was extracted or the indices of the anchor cell.
At the moment, there does not seem to be an obvious way to do that. But, I suspect the comment-id might be able to help. Google does not seem to give a way to do that in an obvious way.
Any inputs on this will be deeply appreciated! Thanks!
CodePudding user response：
I believe your goal is as follows.
- You want to retrieve the row index of the row with the comment.
- You want to achieve this using python.
- From your previous question, you are using googleapis for python.
Issue and workaround:
When the anchor cell information is retrieved from the comment ID, in your showing sample, it's 'anchor': '{"type":"workbook-range","uid":0,"range":"1713668520"}. But, in the current stage, unfortunately, the anchor cell cannot be known from it. Ref By this, I thought that your goal cannot be directly achieved by Sheets API and Drive API. I think that if the cell coordinate is retrieved from "range":"1713668520", your goal can be achieved.
From the above situation, I would like to propose a workaround. My workaround is as follows.
- Download the Google Spreadsheet using Drive API as XLSX data.
- Parse XLSX data using
openpyxl. - Using
openpyxl, the comments are retrieved from XLSX data converted from Google Spreadsheet.
When this flow is reflected in a python script, how about the following sample script?
Sample script 1:
In this case, please use your script of authorization. The access token is retrieved from it. And, please set your Spreadsheet ID.
service = build("drive", "v3", credentials=creds)
access_token = creds.token # or access_token = service._http.credentials.token
spreadsheetId = "###" # Please set the Spreadsheet ID.
sheetName = "Sheet1" # Please set your sheet name.
url = "https://www.googleapis.com/drive/v3/files/" spreadsheetId "/export?mimeType=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
res = requests.get(url, headers={"Authorization": "Bearer " access_token})
workbook = openpyxl.load_workbook(filename=BytesIO(res.content), data_only=False)
worksheet = workbook[sheetName]
res = []
for i, row in enumerate(worksheet.iter_rows()):
for j, cell in enumerate(row):
if cell.comment:
res.append({"rowIndex": i, "columnIndex": j, "comment": cell.comment.text})
print(res)
In this script, please add the following libraries.
import openpyxl import requests from io import BytesIOWhen this script is run, the Google Spreadsheet is exported in XLSX format, and the XLSX data is parsed and retrieved the comments. And, the row and column indexes and the comment text are returned as an array as follows. Unfortunately, the comment ID of Drive API cannot be retrieved from XLSX data. So, I included the comment text.
[ {'rowIndex': 0, 'columnIndex': 0, 'comment': 'sample comment'}, , , , ]
Sample script 2:
As a sample script 2, in this sample script, Google Spreadsheet is exported as XLSX format using googleapis for python.
service = build("drive", "v3", credentials=creds) # Please use your client.
spreadsheetId = "###" # Please set the Spreadsheet ID.
sheetName = "Sheet1" # Please set your sheet name.
request = service.files().export_media(fileId=spreadsheetId, mimeType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
fh = BytesIO()
downloader = MediaIoBaseDownload(fh, request)
done = False
while done is False:
status, done = downloader.next_chunk()
print("Download %d%%" % int(status.progress() * 100))
fh.seek(0)
workbook = openpyxl.load_workbook(filename=fh, data_only=False)
worksheet = workbook[sheetName]
res = []
for i, row in enumerate(worksheet.iter_rows()):
for j, cell in enumerate(row):
if cell.comment:
res.append({"rowIndex": i, "columnIndex": j, "comment": cell.comment.text})
print(res)
In this case, googeapis for python is used. So,
requestsis not used.When this script is run, the same value with the above script is obtained.