Google API - Extracted comments from a google spreadsheet using Python number only 20?

Question

I have a google spreadsheet with around 3000 rows and I am trying to extract comments from this spreadsheet using the following code:

import requests
from apiclient import errors
from apiclient import discovery
from apiclient.discovery import build
from oauth2client.client import OAuth2WebServerFlow
import httplib2

CLIENT_ID = "xxxxxyyyy"
CLIENT_SECRET = "xxxxxxx"
OAUTH_SCOPE = "https://www.googleapis.com/auth/drive"
REDIRECT_URI = 'urn:ietf:wg:oauth:2.0:oob'
file-id = "zzzzzz"
def retrieve_comments(service, file_id):
  """Retrieve a list of comments.

  Args:
    service: Drive API service instance.
    file_id: ID of the file to retrieve comments for.
  Returns:
    List of comments.
  """
  try:
    comments = service.comments().list(fileId=file_id).execute()
    return comments.get('items', [])
  except errors.HttpError as error:
    print(f'An error occurred: {error}')
  return None
# ...

flow = OAuth2WebServerFlow(CLIENT_ID,CLIENT_SECRET,OAUTH_SCOPE)
flow.redirect_uri = REDIRECT_URI
authorize_url = flow.step1_get_authorize_url()
print("Go to the following link in your web browser "+ authorize_url)
code = input("Enter verfication code : ").strip()
credentials = flow.step2_exchange(code)

http = httplib2.Http()
http = credentials.authorize(http)

service = build('drive', 'v2', http=http)

comments = retrieve_comments(service, file-id)

However, the length of the list comments is only 20 whereas the spreadsheet surely contains more comments. Could someone explain which parameter I would need to tweak to retrieve all the comments in the spreadsheet? Thanks!

@LindaLawton-DaImTo Thanks! I have edited my code. Would you be able to help me with another small question please? I want to link the comment-id with that particular row in the spreadsheet. Do you know of a way to do that? Thanks! — PHV, Jan 28 '23 at 05:42

Tanaike · Accepted Answer · 2023-01-27T05:23:08.043

In the current stage, the default value of maxResults (Drive API v2) or pageSize (Drive API v3) of "Comments: list" of Drive API v3 is 20. I thought that this might be the reason for your current issue of However, the length of the list comments is only 20 whereas the spreadsheet surely contains more comments.. In this case, how about the following modification?

From:

comments = service.comments().list(fileId=file_id).execute()
return comments.get('items', [])

To:

From your script, when you want to use Drive API v2, please modify it as follows.

file_id = "###" # Please set your file ID.

res = []
page_token = None
while True:
    obj = service.comments().list(fileId=file_id, pageToken=page_token, maxResults=100, fields="*").execute()
    if len(obj.get("items", [])) > 0:
        res = [*res, *obj.get("items", [])]
    page_token = obj.get("nextPageToken")
    if not page_token:
        break
return res

When you want to use Drive API v3, please modify it as follows.

file_id = "###" # Please set your file ID.

res = []
page_token = None
while True:
    obj = service.comments().list(fileId=file_id, pageToken=page_token, pageSize=100, fields="*").execute()
    if len(obj.get("comments", [])) > 0:
        res = [*res, *obj.get("comments", [])]
    page_token = obj.get("nextPageToken")
    if not page_token:
        break
return res

In this modification, the all comments in the Spreadsheet is returned as an array.

References:

I had an another small related question. Please feel free to let me know if you think I should ask this as a separate question. I want to link the comment-id with that particular row in the spreadsheet. Would you know of a way to do that? Thanks! — PHV, Jan 28 '23 at 05:48
About your new question of `I want to link the comment-id with that particular row in the spreadsheet.`, I would like to support you. In this case, I would like to recommend posting it as a new question. In that case, please include more information for helping to understand your new question. — Tanaike, Jan 28 '23 at 05:50
Please feel free to answer this over here: https://stackoverflow.com/questions/75265700/google-drive-api-linking-the-comment-id-of-to-the-row-in-the-spreadsheet — PHV, Jan 28 '23 at 05:59

Google API - Extracted comments from a google spreadsheet using Python number only 20?

1 Answers1

From:

To:

References:

Linked