Skip to content

Instantly share code, notes, and snippets.

@tanaikech

tanaikech/submit.md

Created October 26, 2023 06:47
Show Gist options
  • Save tanaikech/7713944094af5643fcf13b8d362d0c68 to your computer and use it in GitHub Desktop.
Save tanaikech/7713944094af5643fcf13b8d362d0c68 to your computer and use it in GitHub Desktop.
Download ZIP
Retrieve Comments with Emoji Reactions from Google Documents, Google Slides, and Google Spreadsheets using Google Apps Script
Raw
submit.md

Retrieve Comments with Emoji Reactions from Google Documents, Google Slides, and Google Spreadsheets using Google Apps Script

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.

  1. Convert Google Docs files (Google Documents, Google Slides, and Google Spreadsheets) to Microsoft Docs (Microsoft Word, Microsoft PowerPoint, and Microsoft Excel).
  2. 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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment