permissions_to_sheet.py

import json
from google.cloud import recommender_v1
from google.auth import default
import gspread
from google import auth
import pandas as pd


from google.protobuf.json_format import MessageToJson


def extract_permissions_and_user(data):
    # Extract the username
    data_dict = dict(data)
    username = MessageToJson(data_dict['member'])
    permssion_json = MessageToJson(data_dict['exercisedPermissions'])

    exercised_permissions = [item['permission']
                             for item in json.loads(permssion_json)]
    return username, exercised_permissions


def get_excess_permissions(project_id):
    """
    Retrieve excess permissions insights for a given project.

    Args:
        project_id (str): The ID of the GCP project.

    Returns:
        list: A list of dictionaries containing excess permission details.
    """
    client = recommender_v1.RecommenderClient()
    parent = f"projects/{project_id}/locations/global/insightTypes/google.iam.policy.Insight"

    excess_permissions = []

    try:
        for insight in client.list_insights(parent=parent):
            description = insight.description
            content = insight.content.pb
            data = extract_permissions_and_user(content)
            print(data)

            excess_permissions.append({
                "project_id": project_id,
                "user": data[0],
                "exercised_permissions": data[1]
            })
    except Exception as e:
        print(f"Error processing project {project_id}: {str(e)}")

    return excess_permissions


def create_dataframe(project_data):
    data = {item['user']: item['exercised_permissions']
            for item in project_data}

    # Find the maximum number of permissions
    max_permissions = max(len(permissions) for permissions in data.values())

    # Pad shorter lists with empty strings
    padded_data = {user: permissions + [''] * (max_permissions - len(permissions))
                   for user, permissions in data.items()}

    df = pd.DataFrame(padded_data)
    return df


def write_to_google_sheet(data, sheet_key):
    credentials, project_id = auth.default()
    gc = gspread.authorize(credentials)
    try:
        spreadsheet = gc.open_by_key(sheet_key)
    except gspread.SpreadsheetNotFound:
        print(f"Spreadsheet with key {
              sheet_key} not found. Please check the key and try again.")
        return

    for project_data in data:
        project_id = project_data[0]['project_id']

        # Create a new worksheet for the project if it doesn't exist
        try:
            worksheet = spreadsheet.worksheet(project_id)
        except gspread.WorksheetNotFound:
            worksheet = spreadsheet.add_worksheet(
                title=project_id, rows=1, cols=1)

        # Prepare the data for writing
        dataframe = create_dataframe(project_data)

        # Write the data to the worksheet
        worksheet.clear()
        worksheet.update([dataframe.columns.values.tolist()
                          ] + dataframe.values.tolist())


def main(projects, sheet_key):
    all_project_data = []
    for project_id in projects:
        project_data = get_excess_permissions(project_id)
        if project_data:
            all_project_data.append(project_data)

    write_to_google_sheet(all_project_data, sheet_key)


if __name__ == "__main__":
    # Add your project IDs here
    projects = ["list_allproject_ids"]
    # Add your Google Sheet key here
    sheet_key = "replacewithspreadsheetid"
    main(projects, sheet_key)

Scopes for local usage:

gcloud auth application-default login \
            --scopes =openid,https://www.googleapis.com/auth/userinfo.email,https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/spreadsheets