SELECT

livequery.live.udf_api(

'GET',

'https://science.flipsidecrypto.xyz/googlesheets/readsheet',

{ 'Content-Type': 'application/json' },

{

-- 'sheets_id' : '1OyUssDN960O2o-ss6UWjQ7tVZ7_IPznSEX3mH1N-yrs',

'sheets_id' : '1sZHapAIIFoCa-tlApwYYmzDjCo1oPn1hb5T29NXxhQU',

'tab_name' : 'contracts'

}

) as result

from DUAL

),

data AS (

select result:data as json_result_must_pivot from res

),

historical_contracts as

(

TO_DATE(d.value:"CREATED_BLOCK_TIMESTAMP") AS date,

TRY_CAST(NULLIF(d.value:"CREATED_TX_HASH"::STRING, '') AS VARCHAR) AS CREATED_TX_HASH,

TRY_CAST(NULLIF(d.value:"Address"::STRING, '') AS VARCHAR) AS Address,

TRY_CAST(NULLIF(d.value:"creator_address"::STRING, '') AS VARCHAR) AS creator_address,

-- TRY_CAST(NULLIF(d.value:"contract_abi"::STRING, '') AS VARCHAR) AS contract_abi,

TRY_CAST(NULLIF(d.value:"Name"::STRING, '') AS VARCHAR) AS Name,

TRY_CAST(NULLIF(d.value:"Symbol"::STRING, '') AS VARCHAR) AS Symbol

data,

LATERAL FLATTEN(input => data.json_result_must_pivot::VARIANT) d

select * from historical_contracts