0

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&amp;sa=D&amp;source=editors&amp;ust=1674887508573584&amp;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!

PHV
  • 39
  • 5
  • Can I ask you about the detail of `associate this comment with that particular row from which this comment was extracted`? – Tanaike Jan 28 '23 at 06:03
  • @Tanaike Suppose I extracted a comment from a cell. I want to know the row index of that particular cell with the help of the output I have specified above in my question. Thanks! – PHV Jan 28 '23 at 06:05
  • @Tanaike Does that make sense? – PHV Jan 28 '23 at 06:07
  • Thank you for replying. In your goal, you want to know the anchor cell of the comment in the Spreadsheet using comment ID. Is my understanding correct? – Tanaike Jan 28 '23 at 06:44
  • @Tanaike yes, even if the contents of the anchor cell are empty. I want to do it in Python – PHV Jan 28 '23 at 06:46
  • Thank you for replying. I understood your question. 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](https://stackoverflow.com/q/23498275) By this, I think that your goal cannot be achieved by Sheets API. I think that if the cell coordinate is retrieved from `"range":"1713668520"`, your goal can be achieved. I think that this is due to my poor skill. I have to apologize for this. – Tanaike Jan 28 '23 at 06:57
  • 1
    Thank you for replying. I will look for whether there is a workaround without using Sheets API and Drive API. If I found it, I would like to post it as an answer. – Tanaike Jan 28 '23 at 07:08
  • I proposed a workaround for closing your goal as an answer. Please confirm it. If that was not useful, I apologize. – Tanaike Jan 28 '23 at 08:46

1 Answers1

2

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.

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.

  1. Download the Google Spreadsheet using Drive API as XLSX data.
  2. Parse XLSX data using openpyxl.
  3. 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%2Fvnd.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 BytesIO

  • When 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, requests is not used.

  • When this script is run, the same value with the above script is obtained.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165