Queries

Google Sheet Uploads

This query returns a list of the GSheets-backed Datasets that have been created, with links to the Mode Dataset, and the Google Sheet. Also includes the uploader, the Collection it’s in, and a list of schedules on each GSheets-backed Dataset.

SELECT
    gs.ORG_USERNAME,
    gs.OBJECT_NAME,
    gs.ID,
    gs.NAME,
    gs.STATE,
    gs.DATASET_ID,
    gs.CREATOR_ID,
    gs.CREATED_AT_UTC,
    gs.URL,
    gs.SPREADSHEET_ID,
    gs.WORKSHEET_ID,
    gs.SPREADSHEET_URL,
    c.ID as COLLECTION_ID,
    c.NAME as COLLECTION_NAME,
    c.TYPE as COLLECTION_TYPE,
    listagg(CONCAT(rs.cadence, ' ', rs.cronline), ', ') AS GSHEET_SCHEDULES
FROM google_sheets gs
    JOIN datasets d ON
    d.id = gs.dataset_id
    JOIN collections c ON
    c.id = d.collection_id
    JOIN report_schedules rs ON
    rs.report_id = d.id
WHERE
  rs.state = 'active'
  AND d.state = 'active'
  GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
ORDER BY
  8 DESC