Allow moderators to see who has which gmail

[dofi4ka]

Describe the feature

Currently moderators have to contact us in order to investigate who is 🤡.

Suggested solution

Create page with all joined users and simple search.

Warning

Version history in Google Sheets shows usernames instead of gmails!

We need to match (username, time of change) to gmail. Best option is to input from clown' username or gmail, then find in last X revision all matches on gmail and username, and return all matches.

Code snippet to get revisions:

import pprint

from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build

# Assuming you have authenticated service already
credentials = Credentials.from_service_account_file("inh-plugin.json")
service = build("drive", "v3", credentials=credentials)


# Get revision history for spreadsheet
def get_revisions(spreadsheet_id):
    response = (
        service.revisions().list(fileId=spreadsheet_id, fields="revisions(id,modifiedTime,lastModifyingUser)").execute()
    )

    return response.get("revisions", [])


# Usage
revisions = get_revisions("...")
for revision in revisions:
    print(f"ID: {revision['id']}, Modified: {revision['modifiedTime']}, User:\n{pprint.pformat(revision['lastModifyingUser'])}")

# Will output:
"""
ID: 1, Modified: 2025-10-02T13:37:07.118Z, User:
{'displayName': 'elverodante',
 'emailAddress': 'elverodante@gmail.com',
 'kind': 'drive#user',
 'me': False,
 'permissionId': '02269580269481881084',
 'photoLink': 'https://lh3.googleusercontent.com/a-/ALV-UjWQkUK7GLs0ZQ_rqdoLixObQpzm0biF52l5Ep92J73l8Goz_cMT=s64'}

ID: 2, Modified: 2025-10-02T14:00:46.399Z, User:
{'displayName': 'elverodante',
 'emailAddress': 'elverodante@gmail.com',
 'kind': 'drive#user',
 'me': False,
 'permissionId': '02269580269481881084',
 'photoLink': 'https://lh3.googleusercontent.com/a-/ALV-UjWQkUK7GLs0ZQ_rqdoLixObQpzm0biF52l5Ep92J73l8Goz_cMT=s64'}
"""

Example route for matching:

import datetime


@router.get("/revisions")
def search_revisions(
    spreadsheet_id: str,
    revision_time: str = Query(examples=["October 2, 5:00 PM", "13 октября, 00:02"]),
) -> list:
    revisions = get_revisions(spreadsheet_id)
    try:
        revision_datetime = datetime.datetime.strptime(revision_time, "%B %d, %Y %I:%M %p")
        # TODO: Match Russian format
    except ValueError:
        raise HTTPException(status_code=400, detail="Invalid revision time format")
        
    matched = []
    for revision in revisions:
        # 2025-10-02T13:37:07.118Z
        modified_datetime = datetime.datetime.fromisoformat(revision["modifiedTime"])
        diff = modified_datetime - revision_datetime

        if abs(diff.total_seconds()) < 60 * 30: # within 30 minutes
            matched.append(revision)

    return matched

Also, we can use file.permissions from https://developers.google.com/workspace/drive/api/reference/rest/v3/files#File.

Additional context

No response