transfer_qual_codes_template.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "aa2d21fe",
   "metadata": {},
   "source": [
    "# Google Docs Comment Exporter\n",
    "\n",
    "This script transfers comments from a set of Google documents into a CSV file.\n",
    "\n",
    "## Setup\n",
    "\n",
    "1. **Create a Google Cloud Platform account** at [console.cloud.google.com](console.cloud.google.com). I've had problems using my academic account so I use a personal Gmail account.\n",
    "2. **Create a GCP project**, then select it to work within that project.\n",
    "3. **Enable the Google Drive API.** Go to `APIs and Services > Library` in the sidebar, find Google Drive API, and enable it for the GCP project. (You shouldn't need to worry about payment - this use case shouldn't use anywhere close to the free tier maximum.)\n",
    "4. **Authorize your Google Drive account for OAuth access.** Since this isn't a published application, you need to designate the Google account that contains your transcripts as a \"test user\" for the application. To do this, go to `APIs and Services > OAuth consent screen`, then find the \"Test users\" section. Add a user with the email address corresponding to your Google Drive account.\n",
    "5. **Download OAuth credentials.** Go to `APIs and Services > Credentials`. Click Create Credentials, then OAuth Client ID. For Application Type, choose Desktop App. Once the client is created, copy the client ID into the client_id variable below, and click Download JSON to save the client secret. Input the client secret path into the client_secret variable below. \n",
    "6. **Install the Python Google Drive API.** Run `pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4156599",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "import requests\n",
    "import html\n",
    "import re\n",
    "import pandas as pd\n",
    "from googleapiclient.discovery import build\n",
    "from google_auth_oauthlib.flow import InstalledAppFlow\n",
    "from google.auth.transport.requests import Request\n",
    "from google.oauth2.credentials import Credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fb9600a",
   "metadata": {},
   "outputs": [],
   "source": [
    "client_id = \"ABCDEFG.apps.googleusercontent.com\" # Client ID from GCP console\n",
    "client_secret = \"client_secret.json\" # Path to client secret JSON file downloaded from GCP console, relative to this notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32b1cf44",
   "metadata": {},
   "source": [
    "## Initializing Google Drive API\n",
    "\n",
    "Running the following cell should open an OAuth window to log you in. You should log in to the account that has the files you need to read from Google Drive, authorize this application, and then paste the authorization code into the text box in the cell output. \n",
    "\n",
    "If the authentication worked, it should print a sample of some files in your Drive -- don't worry, this is just for your verification and the files won't be used anywhere."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8fe19994",
   "metadata": {},
   "outputs": [],
   "source": [
    "# If modifying these scopes, delete the file token.json.\n",
    "SCOPES = ['https://www.googleapis.com/auth/drive.readonly']\n",
    "\n",
    "creds = None\n",
    "# The file token.json stores the user's access and refresh tokens, and is\n",
    "# created automatically when the authorization flow completes for the first\n",
    "# time.\n",
    "if os.path.exists('token.json'):\n",
    "    creds = Credentials.from_authorized_user_file('token.json', SCOPES)\n",
    "# If there are no (valid) credentials available, let the user log in.\n",
    "if not creds or not creds.valid:\n",
    "    if creds and creds.expired and creds.refresh_token:\n",
    "        creds.refresh(Request())\n",
    "    else:\n",
    "        flow = InstalledAppFlow.from_client_secrets_file(client_secret, SCOPES)\n",
    "        creds = flow.run_console()\n",
    "    # Save the credentials for the next run\n",
    "    with open('token.json', 'w') as token:\n",
    "        token.write(creds.to_json())\n",
    "\n",
    "service = build('drive', 'v3', credentials=creds)\n",
    "\n",
    "# Call the Drive v3 API\n",
    "results = service.files().list(\n",
    "    pageSize=10, fields=\"nextPageToken, files(id, name)\").execute()\n",
    "items = results.get('files', [])\n",
    "\n",
    "if not items:\n",
    "    print('No files found.')\n",
    "else:\n",
    "    print('Files:')\n",
    "    for item in items:\n",
    "        print(u'{0} ({1})'.format(item['name'], item['id']))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee2b8ba0",
   "metadata": {},
   "source": [
    "## Reading individual file comments\n",
    "\n",
    "To test how the comment structure works, try the following cell. You'll need to fill in a file ID from your Drive (it's in the document's URL, like `https://docs.google.com/document/d/<FILE_ID>/edit?usp=sharing`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aac59451",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_id = \"\" # something like \"15dR04q_whT11lHKDSsSVFkYsxoC8_wKCDUb0eh9vyvc\"\n",
    "result = service.comments().list(fileId=file_id, fields=\"*\").execute()\n",
    "for comment in result['comments']:\n",
    "    if comment.get('resolved'): continue\n",
    "    print(comment['author']['displayName'], comment['anchor'])\n",
    "    print(comment)\n",
    "    print(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7ddf212",
   "metadata": {},
   "source": [
    "## Finding all files in a folder\n",
    "\n",
    "The following code builds a list of files from a Google Drive directory. We will extract comments from the files stored in the `files` list.\n",
    "\n",
    "To populate the `parent_folder` variable, get the folder ID by opening the folder in Google Drive and then copy the ID in the URL bar. For example, the URL might look like `https://drive.google.com/drive/u/0/folders/<folder ID>`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b443fb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "parent_folder = \"\" # Folder ID (in the URL when you view that folder in Drive)\n",
    "\n",
    "# The q parameter below (query) can be modified to further limit the results. For example, we used:\n",
    "# f\"'{parent_folder}' in parents and mimeType = 'application/vnd.google-apps.document' and (name contains 'Interview' or name contains 'Inquiry')\"\n",
    "parent_result = service.files().list(q=f\"'{parent_folder}' in parents\").execute()\n",
    "files = []\n",
    "\n",
    "for file in sorted(parent_result['files'], key=lambda x: x['name']):\n",
    "    # Optionally filter file names by regex here\n",
    "#     if not re.search(r'^(C|S)\\d+ (Interview|Inquiry)', file['name']) or 'old' in file['name']:\n",
    "#         continue\n",
    "    print(file['name'], file['id'])\n",
    "    files.append(file)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19a9c0f6",
   "metadata": {},
   "source": [
    "## Writing out the comments\n",
    "\n",
    "The following code iterates over the list of `files` built above, and requests comments for each document iteratively (because Drive comments are returned in pages if there are too many). It writes the resulting comments dataframe to CSV at the specified `out_path`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b690b76a",
   "metadata": {},
   "outputs": [],
   "source": [
    "out_path = \"comments.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "887bcbb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = []\n",
    "for file in files:\n",
    "    print(file['name'])\n",
    "    token = 'first'\n",
    "    while token:\n",
    "        result = service.comments().list(fileId=file['id'], fields=\"*\", pageToken=token if token != 'first' else None).execute()\n",
    "\n",
    "        for comment in result['comments']:\n",
    "            # This code filters out anything that is resolved, deleted, or where the comment begins with MEMO.\n",
    "            # We used MEMO to specify when a comment on the doc was not supposed to be included in the qualitative analysis.\n",
    "            if comment.get('resolved', False) or comment.get('deleted', False) or comment['content'].startswith(\"MEMO\"): continue\n",
    "            tag = re.search(r\"@(\\S+)\", comment['htmlContent'])\n",
    "            has_replies = any(r['content'] != '+1' and not r['content'].startswith(\"MEMO\") for r in comment.get('replies', []))\n",
    "            \n",
    "            data.append({\n",
    "                \"Summary\": \"{} ({}, {})\".format(\n",
    "                    html.unescape(comment['content']),\n",
    "                    file['name'],\n",
    "                    comment['id']),\n",
    "                \"Attn (Replies other than +1 or Tag)\": has_replies or tag is not None,\n",
    "                \"Author\": comment['author']['displayName'],\n",
    "                \"Source\": file['name'],\n",
    "                \"Link\": \"https://docs.google.com/document/d/{}?disco={}\".format(file['id'], comment['id']),\n",
    "                \"Quote\": html.unescape(comment['quotedFileContent']['value'] if 'quotedFileContent' in comment else ''),\n",
    "                \"Comment\": html.unescape(comment['content']),\n",
    "                \"Replies\": str(len(comment.get('replies', []))),\n",
    "                \"Tag\": tag.group(1) if tag is not None else '',\n",
    "            })\n",
    "            \n",
    "        token = result.get('nextPageToken', None)\n",
    "\n",
    "pd.DataFrame(data).to_csv(out_path)\n",
    "print(\"Done.\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}