API POST /api/card/:card-id/query/:export-format with parameters

[sebascar]

Hello! @yash1234
I having the same problem using the same appscript code on google sheets, can you help us sharing the code you use to provide a string paramater?
Thank you!

[sebascar]

Hello there! I finally solved it, thank you @yash1234 and @flamber for your comments in the post, it helped me a lot.

I am going to post a kind of tutorial if someone comes here and have the same problem trying to filter a query using this code (https://metabase-google-sheets-add-on:

1. As flamber said go to your query and use your browser developer Network-tab, so, filter your query and then export it, in the Network-tab you will see this line:
   
   

do right click and copy cURL:

2. go to a cURL Converter to convert cURL into Json, I used Curl Converter

3. Copy the cURL.
   in my case the CURL look like this:

curl 'https://stats.metabase.ai/api/card/1054/query/csv' \
  -H 'authority: stats.metabase.ai' \
  -H 'accept: */*' \
  -H 'sec-fetch-mode: cors' \
  -H 'sec-fetch-site: same-origin' \
  --data-raw 'parameters=%5B%7B%22type%22%3A%22id%22%2C%22value%22%3A%5B20000%5D%2C%22target%22%3A%5B%22dimension%22%2C%5B%22template-tag%22%2C%22position_id%22%5D%5D%2C%22id%22%3A%22b410a943-2bf2-b49d-ee21-da5760bdafbb%22%7D%5D' \

4. now it is time to edit our Gsheet code:

4.1 first edit the "questionUrl" variable

by default it comes like this:

var questionUrl = baseUrl + "api/card/" + metabaseQuestionNum + '/query/csv'

you have to add your JSON serialized parameters at the end of the variable, the Serialized will be visible in cURL, the variable now should look like this:

var questionUrl = baseUrl + "api/card/" + metabaseQuestionNum + '/query/csv?parameters=%5B%7B%22type%22%3A%22id%22%2C%22value%22%3A%5B20000%5D%2C%22target%22%3A%5B%22dimension%22%2C%5B%22template-tag%22%2C%22position_id%22%5D%5D%2C%22id%22%3A%22b410a943-2bf2-b49d-ee21-da5760bdafbb%22%7D%5D'

4.2 create a parameters variable with Json coverted cURL:

at the end of the coverted cURL you will see the parameters as Json, in my case look like this:

so I created a variable called "para" with the parameters in Json format,
look like this:

var para = '[{\"type\":\"id\",\"value\":[20000],\"target\":[\"dimension\",[\"template-tag\",\"position_id\"]]';

4.3 Add Json parameters through payload

by default the options to "getQuestionAndFillSheetimport" funtion comes like this:

  var options = {
    "method": "post",
    "headers": {
      "Content-Type": "application/json",
      "X-Metabase-Session": token
    },
    "muteHttpExceptions": true
  };

so you have to add a payload in the options loading the Json parameter variable we created before in my case look like this:

  var options = {
    "method": "post",
    "headers": {
      "Content-Type": "application/json",
      "X-Metabase-Session": token
    },
    "muteHttpExceptions": true,

    "payload": JSON.stringify({
      parameters : para})
  };

and that´s it. this is how I solved. I hope it helps.

thank you.

[Hieu]

@sebascar i was tried to like u said. but it 's fail .

Pls help me to fix this.
Many thanks <3

[EsmaelMaher]

I am trying to do this but in python, I am new to APIs.

I learned from your conversation with flamber and the other discussions in the community for the same issue which is retrieving data with parameters.

I created a function that works fine on cards with no parameters. I don't know how to include the params or where to include it.

Here's my function for reference:

def ret_metabase(question):
    question_id = str(question)
    file_name = 'meta_results.csv'
    # Do not modify below this
    base_url = 'https://bi.maxab.info/api'
    base_headers = {'Content-Type' : 'application/json'}
    
    os.environ['METABASE_USER_NAME'] = 'myuser'
    os.environ['METABASE_PASSWORD'] = 'mypass*'

    try:
        s_response = requests.post(base_url + '/session', 
                                data = json.dumps({'username': os.environ["METABASE_USER_NAME"], 'password': os.environ["METABASE_PASSWORD"]}), 
                                headers=base_headers)
        s_response.raise_for_status()

        session_token = s_response.json()['id']                        
        base_headers['X-Metabase-Session'] = session_token

        p_response = requests.post(base_url + '/card/' + question_id + '/query/csv', headers=base_headers)
        p_response.raise_for_status()
        
        my_dict = p_response.content
        s = str(my_dict,'utf-8')
        my_dict = StringIO(s) 
        df = pd.read_csv(my_dict)
        return(df)

    except requests.exceptions.HTTPError as errh:
        return(errh)
    except requests.exceptions.ConnectionError as errc:
        return(errc)
    except requests.exceptions.Timeout as errt:
        return(errt)
    except requests.exceptions.RequestException as err:
        return(err)

For example, A question I want to get data from has this : `

"data": {
        "parameters": "[{\"type\":\"date/single\",\"value\":\"2023-04-01\",\"target\":[\"variable\",[\"template-tag\",\"P1_start_date\"]],\"id\":\"ddf08c20-c2a9-0082-dc28-d3e1a906295f\"},{\"type\":\"date/single\",\"value\":\"2023-04-01\",\"target\":[\"variable\",[\"template-tag\",\"P1_end_date\"]],\"id\":\"822476a9-d612-0e8b-d00f-e8a5d8e9adad\"}]"`

I know I am so close, Can you guys help me with this, any tip would be awesome.

[tvanneufville]

Thanks @sebascar I was exactly looking for this, you made my day!