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

Get Help
1

Hi,
I’m trying to call /api/card/:card-id/query/xslx of a card with a required parameter of type text.

The submitted call is something like

curl -X POST \
  -H "Content-Type: application/json" \
  -H "X-Metabase-Session: xxxx-xxx-xxx" \
  -d '{"parameters": [{"cf": "foo"}]}' \
  "https://mymetabase/api/card/168/query/xlsx"

The answare is:
You'll need to pick a value for 'codice fiscale' before this query can run.

The log is:

metabase_1  | 11-22 11:03:59 WARN middleware.process-userland-query :: Query failure {:status :failed,
metabase_1  |  :class clojure.lang.ExceptionInfo,
metabase_1  |  :error "You'll need to pick a value for 'codice fiscale' before this query can run.",
metabase_1  |  :stacktrace
metabase_1  |  ("--> query_processor.middleware.parameters.native.values$missing_required_param_exception.invokeStatic(values.clj:72)"
metabase_1  |   "query_processor.middleware.parameters.native.values$missing_required_param_exception.invoke(values.clj:71)"
metabase_1  |   "query_processor.middleware.parameters.native.values$fn__40626$default_value_for_tag__40631$fn__40635.invoke(values.clj:130)"
metabase_1  |   "query_processor.middleware.parameters.native.values$fn__40626$default_value_for_tag__40631.invoke(values.clj:124)"
metabase_1  |   "query_processor.middleware.parameters.native.values$fn__40780$value_for_tag__40785$fn__40786.invoke(values.clj:215)"
metabase_1  |   "query_processor.middleware.parameters.native.values$fn__40780$value_for_tag__40785.invoke(values.clj:209)"
metabase_1  |   "query_processor.middleware.parameters.native.values$fn__40806$query__GT_params_map__40811$fn__40815$iter__40817__40821$fn__40822.invoke(values.clj:226)"
metabase_1  |   "query_processor.middleware.parameters.native.values$fn__40806$query__GT_params_map__40811$fn__40815.invoke(values.clj:225)"
metabase_1  |   "query_processor.middleware.parameters.native.values$fn__40806$query__GT_params_map__40811.invoke(values.clj:218)"
metabase_1  |   "query_processor.middleware.parameters.native$expand_inner.invokeStatic(native.clj:51)"
metabase_1  |   "query_processor.middleware.parameters.native$expand_inner.invoke(native.clj:34)"
metabase_1  |   "query_processor.middleware.parameters$expand_native_params.invokeStatic(parameters.clj:42)"
metabase_1  |   "query_processor.middleware.parameters$expand_native_params.invoke(parameters.clj:41)"
metabase_1  |   "query_processor.middleware.parameters$expand_one.invokeStatic(parameters.clj:51)"
metabase_1  |   "query_processor.middleware.parameters$expand_one.invoke(parameters.clj:44)"
metabase_1  |   "query_processor.middleware.parameters$expand_all$replace_40898__40899.invoke(parameters.clj:62)"
metabase_1  |   "mbql.util.match$replace_in_collection$iter__20300__20304$fn__20305.invoke(match.clj:132)"
metabase_1  |   "mbql.util.match$replace_in_collection.invokeStatic(match.clj:131)"
metabase_1  |   "mbql.util.match$replace_in_collection.invoke(match.clj:126)"
metabase_1  |   "query_processor.middleware.parameters$expand_all$replace_40898__40899.invoke(parameters.clj:62)"
metabase_1  |   "query_processor.middleware.parameters$expand_all.invokeStatic(parameters.clj:62)"
metabase_1  |   "query_processor.middleware.parameters$expand_all.invoke(parameters.clj:56)"
metabase_1  |   "query_processor.middleware.parameters$expand_all.invokeStatic(parameters.clj:59)"
metabase_1  |   "query_processor.middleware.parameters$expand_all.invoke(parameters.clj:56)"
metabase_1  |   "query_processor.middleware.parameters$expand_parameters.invokeStatic(parameters.clj:80)"
metabase_1  |   "query_processor.middleware.parameters$expand_parameters.invoke(parameters.clj:76)"
metabase_1  |   "query_processor.middleware.parameters$substitute_parameters_STAR_.invokeStatic(parameters.clj:85)"
metabase_1  |   "query_processor.middleware.parameters$substitute_parameters_STAR_.invoke(parameters.clj:82)"
metabase_1  |   "query_processor.middleware.driver_specific$process_query_in_context$fn__37521.invoke(driver_specific.clj:12)"
metabase_1  |   "query_processor.middleware.resolve_driver$resolve_driver$fn__41221.invoke(resolve_driver.clj:22)"
metabase_1  |   "query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__36835$fn__36836.invoke(bind_effective_timezone.clj:9)"
metabase_1  |   "util.date$call_with_effective_timezone.invokeStatic(date.clj:88)"
metabase_1  |   "util.date$call_with_effective_timezone.invoke(date.clj:77)"
metabase_1  |   "query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__36835.invoke(bind_effective_timezone.clj:8)"
metabase_1  |   "query_processor.middleware.store$initialize_store$fn__44341$fn__44342.invoke(store.clj:11)"
metabase_1  |   "query_processor.store$do_with_store.invokeStatic(store.clj:46)"
metabase_1  |   "query_processor.store$do_with_store.invoke(store.clj:40)"
metabase_1  |   "query_processor.middleware.store$initialize_store$fn__44341.invoke(store.clj:10)"
metabase_1  |   "query_processor.middleware.async$async__GT_sync$fn__20798.invoke(async.clj:23)"
metabase_1  |   "query_processor.middleware.async_wait$runnable$fn__36561.invoke(async_wait.clj:89)"),
metabase_1  |  :query
metabase_1  |  {:constraints nil,
metabase_1  |   :type :native,
metabase_1  |   :middleware {:skip-results-metadata? true, :userland-query? true},
metabase_1  |   :native
metabase_1  |   {:query
metabase_1  |    "the query text...",
metabase_1  |    :template-tags {"cf" {:id "3234dc0b-ed2f-dc8b-4409-30cb1e087470", :name "cf", :display-name "codice fiscale", :type :text, :required true}}},
metabase_1  |   :info
metabase_1  |   {:executed-by 1,
metabase_1  |    :context :xlsx-download,
metabase_1  |    :card-id 168,
metabase_1  |    :dashboard-id nil,
metabase_1  |    :query-hash [-39, 4, 33, 71, -33, -62, -126, -110, -42, -48, -108, 85, -88, -45, 36, -30, 16, -122, -96, -79, 61, -104, 46, -103, 46, 114, 9, -68, 6, -69, 84, 11]},
metabase_1  |   :parameters nil,
metabase_1  |   :async? true,
metabase_1  |   :cache-ttl 8},
metabase_1  |  :error_type :missing-required-parameter,
metabase_1  |  :ex-data {:type :missing-required-parameter}}
metabase_1  |
metabase_1  | 11-22 11:03:59 DEBUG middleware.log :: POST /api/card/168/query/xlsx 400 536.8 ms (10 DB calls)
metabase_1  | "You'll need to pick a value for 'codice fiscale' before this query can run."

Where am I doing wrong?
thanks for any suggestions

2

Hi @ilkosta
Which version of Metabase?
When playing around with the API, I would highly recommend that you start by using the regular Metabase interface, while having your browser developer Network-tab open. It’s definitely the easiest way to learn the API.
The parameters is a little special and needs to be serialized - see the API docs:

-d 'parameters=%5B%7B%22type%22%3A%22category%22%2C%22target%22%3A%5B%22variable%22%2C%5B%22template-tag%22%2C%22cf%22%5D%5D%2C%22value%22%3A%22foo%22%7D%5D'
3

thanks @flamber. I’m using the v0.33.4 , but passing the urlencoded parameters, the message remain the same.

curl -X POST \
  -H "Content-Type: application/json" \
  -H "X-Metabase-Session: xxxx-xxx-xxx" \
  -d '{"parameters": **urlencoded_string**}' \
  "https://mymetabase/api/card/168/query/xlsx"

where the urlencoded_string parameters are copied from the browser console.
or

curl -X POST \
  -H "Content-Type: application/json" \
  -H "X-Metabase-Session: xxxx-xxx-xxx" \
  -d '"parameters": **urlencoded_string**' \
  "https://mymetabase/api/card/168/query/xlsx"

the answare is always: You'll need to pick a value for 'codice fiscale' before this query can run.

Other hints?

4

@ilkosta
What I posted actually comes directly from Metabase. I just “Copy as cURL”, which Firefox has as an option.
Notice that it’s not a JSON object. It’s a parameter called parameters with a value of a serialized JSON object.

5

I had never used that feature. a turning point. thank you very much!

6

I'm trying to send the parameters but metabase keeps ignoring the parameters
I'm doing this via google app script for google sheet
Here's the code

var questionUrl = baseUrl + "api/card/" + metabaseQuestionNum + "/query";

var data = {
  "parameters": [
    {
      "type": "date/single",
      "target": [
        "variable",
        [
          "template-tag",
          "start_date"
        ]
      ],
      "value": "2021-10-01"
    },
    {
      "type": "date/single",
      "target": [
        "variable",
        [
          "template-tag",
          "end_date"
        ]
      ],
      "value": "2021-10-15"
    }
  ]
};

var options = {
  "method": "post",
  "headers": {
    "Content-Type": "application/json",
    "X-Metabase-Session": token
  },
  "payload": JSON.stringify(data)
};

response = UrlFetchApp.fetch(questionUrl, options);

A little help please? @flamber

7

@yash1234 Try using your browser developer Network-tab to see what is being sent. You can even copy it as cURL or Fetch, which might help you out with what you're doing wrong.
I haven't used app script for several years, so don't know the syntax for that.

And you are posting about something quite different from what this topic is about (getting the export files)

8

Hey flamber,
I got it to work by doing the following 2 changes

  1. The variables are not working for csv format. they work fine without :export-format. Seems to be a metabase bug
    Removed the csv format
    var questionUrl = baseUrl + "api/card/" + metabaseQuestionNum + "/query"

  2. My mistake - used "" around the key of the key value pairs
    Changed key value pair format
    var data3 = {

    parameters: [

    {

     type: "date/single", 

 target: [

   "variable", 

   [

     "template-tag", 

     "start_date"

     ]

   ], 

   value: "2021-10-01"

    },

    {

     type: "date/single", 

 target: [

   "variable", 

   [

     "template-tag", 

     "end_date"

     ]

   ], 

 value: "2021-10-15"

    }

    ],

};

9

@flamber
Can you get the point one mentioned above raised or checked? Seems to be a problem with metabase api itself.

10

@yash1234 If the API didn't work, then the Metabase interface wouldn't work, since everything you do in Metabase is just API requests.

Please re-read the API documentation for the different endpoints:
https://github.com/metabase/metabase/blob/master/docs/api-documentation.md#post-apicardcard-idquery
https://github.com/metabase/metabase/blob/master/docs/api-documentation.md#post-apicardcard-idqueryexport-format

And then re-read this entire topic from the beginning: API POST /api/card/:card-id/query/:export-format with parameters

Also, you really need to use </> button in the forum editor, when adding code, since it's impossible to read what you post.

11

Thanks @flamber , I re-read through the the doc and I tried to find where the api might be getting triggered on the Metabase interface but I haven't found it. Can you please point me to where/how on the metabase interface I can trigger the :export-format endpoint so I will analyze it in the network tab?

12

@yash1234 Let's start over.
What are you trying to do? Ignore the API, explain with words what you're trying to do.
And post "Diagnostic Info" from Admin > Troubleshooting.

13

@flamber
Okay so I'm trying to do this

  1. https://stackoverflow.com/questions/59238677/how-to-connect-metabase-with-google-sheet
    I used this and it worked.
    My google sheet is connected to my metabase and is able to access data/cards in csv format.

  2. Next I am trying to pass in the start and end date for a question/card to filter my result within a certain date range.
    I am doing this via a payload/body/parameters

  3. This date filtering works for POST /api/card/:card-id/query
    endpoint = BaseURL + "/api/card/2/query"

Metabase gives me data filtered on the basis of date range passed in the parameters

  1. But the moment i change this to POST /api/card/:card-id/query/:export-format
    endpoint = BaseURL + "/api/card/2/query/csv"
    Metabase ignores the parameters in the payload
14

@yash1234

2): Please read my first comment in this issue again. And then read it one more time. API POST /api/card/:card-id/query/:export-format with parameters - #2 by flamber
If you go to the question in your browser, set the filters, run the query, and then export as CSV, then you'll see the exact request you're looking for.

3+4): As I already pointed, that's two different endpoints, which have different way of handling parameters, as it's also noted in the API documentation:

Note that this expects the parameters as serialized JSON in the 'parameters' parameter.

16

I'm sending it as mentioned in the first comment by you and it is still not working, can you please check this out?
{payload={"parameters":"%5B%7Btype%3A%20%22date%2Fsingle%22%2Ctarget%3A%5B%22variable%22%2C%5B%22template-tag%22%2C%22start_date%22%5D%5D%2Cvalue%3A%20%222021-10-01%22%7D%2C%7Btype%3A%22date%2Fsingle%22%2Ctarget%3A%20%5B%22variable%22%2C%5B%22template-tag%22%2C%22end_date%22%5D%5D%2Cvalue%3A%222021-10-15%22%7D%5D"}, headers={X-Metabase-Session=sessID, Content-Type=application/json}, method=post, contentType=application/json}

17

@yash1234 Let's do this. Go and do the operation manually in Metabase, and copy the cURL request and post it here.

18

Worked.
So here is what worked. I was previously sending it as a json object that was converted into string in the payload.
when all I had to send was simply a string of "key(parameters)=value(url_encoded)" in the payload

Thanx a lot for your help @flamber

2 Likes
19

Also thanx a lot for your patience, wouldn't have been able to do it without you. It was kinda a dumb problem but it's hard to see the right answer if you don't know where to look at even if it right in front of you :slight_smile: @flamber

20

@yash1234 The API can be quite difficult to understand, which is why I always recommend just using Metabase regularly and viewing the browser developer Network-tab to see the requests. It's a lot easier to copy or adjust something that already works instead of trying to write your own request from scratch. And then you also have something to compare to.

21

Hi I'm trying to achieve the same thing, by providing a string parameter to a querry in Metabase via appscript on google sheets.. how did you manage to solve it?
I need to use POST /api/card/:card-id/query/:export-format
Thanks!