Asset inventoryをBQにダンプしたデータからクエリする
WITH

  projects AS (

    SELECT

      resource.data AS rsc,

      ancestor_path

    FROM

      prj.cloud_asset_inventory.cloud_asset_inventory_org_resource_now

    WHERE

      asset_type = 'cloudresourcemanager.googleapis.com/Project'

  ),

  projects_info AS (

    SELECT

      JSON_EXTRACT_SCALAR(rsc, '$.projectId') AS projectid,

      JSON_EXTRACT_SCALAR(rsc, '$.lifecycleState') AS lifecycleState,

      ancestor_path

    FROM

      projects

  ),

  projects_efficient AS (

    SELECT

      *

    FROM

      projects_info

    WHERE

      NOT REGEXP_CONTAINS(ancestor_path, "folders/apps-script")

  ),

  projects_num_adm_mgr AS (

    SELECT

      REPLACE(name, '//cloudresourcemanager.googleapis.com/projects/', '') AS project_num,

      REPLACE(b.role, 'organizations/1234567/roles/', '') AS role_value,

      STRING_AGG(REPLACE(m, 'user:', ''), ', ') AS member_value

    FROM

      prj.cloud_asset_inventory.cloud_asset_inventory_org_iam_policy_now,

      UNNEST(iam_policy.bindings) AS b,

      UNNEST(b.members) AS m

    WHERE

      asset_type = 'cloudresourcemanager.googleapis.com/Project'

      AND (role LIKE '%Project_Admin%' OR role LIKE '%Project_Manager%')

    GROUP BY

      project_num,

      role_value

  ),

  projects_adm_mgr AS (

    SELECT

      JSON_EXTRACT_SCALAR(resource.data, '$.projectId') AS project,

      projects_num_adm_mgr.role_value,

      projects_num_adm_mgr.member_value

    FROM

      projects_num_adm_mgr

    LEFT JOIN

      prj.cloud_asset_inventory.cloud_asset_inventory_org_resource_now AS res

    ON

      projects_num_adm_mgr.project_num = REPLACE(res.name, '//cloudresourcemanager.googleapis.com/projects/', '')

  )

SELECT

  projects_efficient.projectid,

  projects_efficient.lifecycleState,

  CONCAT(projects_efficient.projectid, ', ', projects_adm_mgr.role_value) AS role_value,

  projects_adm_mgr.member_value

FROM

  projects_efficient

LEFT OUTER JOIN

  projects_adm_mgr

ON

  projects_efficient.projectid = projects_adm_mgr.project

ORDER BY

  lifecycleState DESC,

  projectid;