Abstract
This report introduces the method for retrieving the Emoji reactions from the comments in Google Docs files (Google Documents, Google Slides, and Google Spreadsheets) using Google Apps Script.
Introduction
Recently, the Emoji reactions have been implemented in the comments on Google Docs files (Google Documents, Google Slides, and Google Spreadsheets). Ref With this implementation, the collaborative work has been higher. Here, it is considered that when the Emoji reactions can be retrieved from the Google Docs files, the statistics of the reactions will be also more useful for increasing collaboration. This report introduces a sample script for retrieving Comments including the Emoji reactions from Google Docs files.
Principle
Unfortunately, in the current stage, even when “Method: comments.list” of Drive API is used, the Emoji reactions cannot be retrieved while the comments can be retrieved. But, as a workaround, it was found that when the following process is used, the comments including the Emoji reactions can be retrieved.
- Convert Google Docs files (Google Documents, Google Slides, and Google Spreadsheets) to Microsoft Docs (Microsoft Word, Microsoft PowerPoint, and Microsoft Excel).
- Convert Microsoft Docs (Microsoft Word, Microsoft PowerPoint, and Microsoft Excel) to Google Docs files (Google Documents, Google Slides, and Google Spreadsheets)
By this process, the comments including the Emoji reactions can be retrieved by a script. After the next section, the sample scripts of Google Apps Script for this process are introduced.
Google Document
Sample Document
The sample Document is as follows.
Sample script
Please copy and paste the following script to the script editor. And, please enable Drive API at Advanced Google services.
function sample1() {
const docId = DocumentApp.getActiveDocument().getId();
const url = `https://docs.google.com/feeds/download/documents/export/Export?exportFormat=docx&id=${docId}`;
const blob = UrlFetchApp.fetch(url, {
headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
}).getBlob();
const { id } = Drive.Files.insert(
{ title: "temp", mimeType: MimeType.GOOGLE_DOCS },
blob
);
const { items } = Drive.Comments.list(id);
Drive.Files.remove(id);
const res = items.map(({ content, replies }) => ({
content,
replies: replies.map(({ content, author: { displayName } }) => [
displayName,
content,
]),
}));
console.log(res);
}
Result
When this script is run to the above sample Document, the following result is obtained.
[
{
"content": "sample comment 1\n1 total reaction\nKanshi Tanaike reacted with 👍 at 2023-10-25 22:16 PM",
"replies": [
[
"Kanshi Tanaike",
"sample comment 2\n2 total reactions\nKanshi Tanaike reacted with 👏 at 2023-10-25 22:16 PM\nKanshi Tanaike reacted with 😀 at 2023-10-25 23:12 PM"
]
]
}
]
When console.log(res[0].content) and console.log(res[0].replies[0][1]) are run, the following results are obtained, respectively.
sample comment 1
1 total reaction
Kanshi Tanaike reacted with 👍 at 2023-10-25 22:16 PM
sample comment 2
2 total reactions
Kanshi Tanaike reacted with 👏 at 2023-10-25 22:16 PM
Kanshi Tanaike reacted with 😀 at 2023-10-25 23:12 PM
From these values, you can create the statistics of the Emoji reactions for each comment.
Google Slide
Sample Slide
The sample Slide is as follows.
Sample script
Please copy and paste the following script to the script editor. And, please enable Drive API at Advanced Google services.
function sample2() {
const sId = SlidesApp.getActivePresentation().getId();
const url = `https://docs.google.com/feeds/download/presentations/Export?exportFormat=pptx&id=${sId}`;
const blob = UrlFetchApp.fetch(url, {
headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
}).getBlob();
const { id } = Drive.Files.insert(
{ title: "temp", mimeType: MimeType.GOOGLE_SLIDES },
blob
);
const { items } = Drive.Comments.list(id);
console.log(JSON.stringify(items));
Drive.Files.remove(id);
const res = items.map(({ content, replies }) => ({
content,
replies: replies.map(({ content, author: { displayName } }) => [
displayName,
content,
]),
}));
console.log(res);
}
Result
When this script is run to the above sample Slide, the following result is obtained.
[
{
"content": "sample comment 1\n1 total reaction\nKanshi Tanaike reacted with 👍 at 2023-10-25 22:15 PM\n-Kanshi Tanaike",
"replies": []
},
{
"content": "sample comment 2\n2 total reactions\nKanshi Tanaike reacted with 👏 at 2023-10-25 22:15 PM\nKanshi Tanaike reacted with 😀 at 2023-10-25 23:23 PM\n-Kanshi Tanaike",
"replies": []
}
]
When console.log(res[0].content) and console.log(res[1].content) are run, the following results are obtained, respectively.
sample comment 1
1 total reaction
Kanshi Tanaike reacted with 👍 at 2023-10-25 22:15 PM
-Kanshi Tanaike
sample comment 2
2 total reactions
Kanshi Tanaike reacted with 👏 at 2023-10-25 22:15 PM
Kanshi Tanaike reacted with 😀 at 2023-10-25 23:23 PM
-Kanshi Tanaike
From these values, you can create the statistics of the Emoji reactions for each comment.
Google Spreadsheet
Sample Spreadsheet
The sample Spreadsheet is as follows.
Sample script
Please copy and paste the following script to the script editor. And, please enable Drive API at Advanced Google services.
function sample3() {
const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
const url = `https://docs.google.com/spreadsheets/export?exportFormat=xlsx&id=${ssId}`;
const blob = UrlFetchApp.fetch(url, {
headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
}).getBlob();
const { id } = Drive.Files.insert(
{ title: "temp", mimeType: MimeType.GOOGLE_SHEETS },
blob
);
const comments = SpreadsheetApp.openById(id)
.getSheets()[0]
.getDataRange()
.getNotes()
.map((r, i) =>
r.map((c, j) => ({ rowIndex: i, columnIndex: j, comment: c }))
);
Drive.Files.remove(id);
console.log(comments);
}
Result
When this script is run to the above sample Spreadsheet, the following result is obtained.
[
[
{
"rowIndex": 0,
"columnIndex": 0,
"comment": "sample comment 1\n1 total reaction\nKanshi Tanaike reacted with 👍 at 2023-10-25 22:39 PM\n\t-Kanshi Tanaike\nsample comment 2\n2 total reactions\nKanshi Tanaike reacted with 👏 at 2023-10-25 21:50 PM\nKanshi Tanaike reacted with 😀 at 2023-10-25 22:39 PM\n\t-Kanshi Tanaike"
}
]
]
When console.log(comments[0][0].comment) is run, the following result is obtained.
sample comment 1
1 total reaction
Kanshi Tanaike reacted with 👍 at 2023-10-25 22:39 PM
-Kanshi Tanaike
sample comment 2
2 total reactions
Kanshi Tanaike reacted with 👏 at 2023-10-25 21:50 PM
Kanshi Tanaike reacted with 😀 at 2023-10-25 22:39 PM
-Kanshi Tanaike
From this value, you can create the statistics of the Emoji reactions for each comment.
IMPORTANT
In the current stage, when a Google Spreadsheet including the comments is manually exported as an XLSX file with the browser, the comments are left. But, when a Google Spreadsheet including the comments on the cells is exported as an XLSX file with a script using the above endpoint, it seems that the comments are automatically converted to notes on the cells. I’m not sure whether this is the current specification.