My team uses multiple Google documents and spreadsheets for project management. We add comments to different sections and cells as we work.
I need to gather all these comments into one central spreadsheet so we can track outstanding comments from all our files. The plan is to run this automatically so our comment list stays current.
I built this script but I’m having trouble with the comment retrieval functions. The API docs aren’t clear on how to actually fetch the comments. Any suggestions?
function collectOpenComments() {
// Access the current sheet
var currentSheet = SpreadsheetApp.getActiveSheet();
var fileUrls = currentSheet.getRange("M2:M").getValues().flat();
// Use same sheet for results
var outputSheet = currentSheet;
// Clear previous data but keep column M intact
outputSheet.getRange(2, 1, outputSheet.getLastRow() - 1, outputSheet.getLastColumn() - 1).clearContent();
// Create column headers
outputSheet.getRange(1, 1).setValue("Link to Comment");
outputSheet.getRange(1, 2).setValue("Author");
outputSheet.getRange(1, 3).setValue("Comment Content");
var currentRow = 2;
for (var index = 0; index < fileUrls.length; index++) {
var fileUrl = fileUrls[index];
// Check file type from URL
if (fileUrl.includes('/docs.google.com/document/d/')) {
// Handle Google Document
var docId = fileUrl.split('/d/')[1].split('/')[0];
var doc = DocumentApp.openById(docId);
var docComments = DriveApp.getComments.list(docId).items;
} else if (fileUrl.includes('/docs.google.com/spreadsheets/d/')) {
// Handle Google Spreadsheet
var workbook = SpreadsheetApp.openByUrl(fileUrl);
var activeTab = workbook.getActiveSheet();
var sheetComments = DriveApp.getComments.list(docId).items;
} else {
// Skip unknown file types
Logger.log('Unknown file type: ' + fileUrl);
continue;
}
for (var commentIndex = 0; commentIndex < docComments.length; commentIndex++) {
var singleComment = docComments[commentIndex];
if (!singleComment.isResolved()) {
var authorEmail = singleComment.getUser().getEmail();
var commentBody = singleComment.getContent();
var directLink = singleComment.getUrl();
outputSheet.getRange(currentRow, 1).setValue(directLink);
outputSheet.getRange(currentRow, 2).setValue(authorEmail);
outputSheet.getRange(currentRow, 3).setValue(commentBody);
currentRow++;
}
}
}
}
ive been working on something simular recently and had to deal with auth issues too. you’ll need to handle permissions properly since the drive api requires oauth scopes that regular apps script dosnt have by default. make sure your manifest includes the drive.readonly scope at minimum.
I’ve dealt with similar comment extraction challenges and found that the Drive API v3 is indeed the way to go, but there are some gotchas beyond what’s already mentioned. First, make sure you’re handling the pagination properly when calling Drive.Comments.list(fileId). The API returns results in pages, so you’ll need to check for nextPageToken and loop through all pages to get every comment. I missed this initially and was only getting partial results. Another thing - for Google Docs comments, you’ll want to include the fields parameter in your API call to get all the data you need: Drive.Comments.list(fileId, {fields: 'comments(id,content,author,resolved,quotedFileContent,htmlContent)'}). The quotedFileContent field is particularly useful as it shows the text that was highlighted when the comment was made. For spreadsheets, comments work differently than regular cell notes. You might also encounter comments that are anchored to ranges rather than single cells, so the comment location data will include range information. One more consideration - if your files have heavy comment activity, you might hit quota limits. I had to implement exponential backoff and caching to handle this in production.
The main issue with your script is that you’re trying to use DriveApp.getComments.list() which doesn’t exist in Apps Script. Google hasn’t exposed comment APIs directly through the standard Apps Script services unfortunately.
I ran into this exact problem last year when trying to build something similar. The workaround I found was using the Drive API v3 through the advanced Google services. You need to enable it first in your Apps Script project under Services, then you can call Drive.Comments.list(fileId) to get the comments.
However there’s another issue - your code is mixing up variables. You’re defining docComments for documents but then using DriveApp.getComments.list(docId) for spreadsheets too, plus docId isn’t defined in the spreadsheet section.
For the Drive API approach, the comment objects have different properties than what you’re calling. Instead of getUser().getEmail() you’d access comment.author.emailAddress and instead of getContent() it would be comment.content. The resolved status is checked with comment.resolved boolean.
Might be worth checking if your use case really needs automated extraction or if manual export would work better given the API limitations.