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!
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:
- 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:
-
go to a cURL Converter to convert cURL into Json, I used Curl Converter
-
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' \
- 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.
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.