Help

Re: query metabase

477 0
cancel
Showing results for 
Search instead for 
Did you mean: 
logtecs
4 - Data Explorer
4 - Data Explorer

hey guys,

I have a huge database of customers in metabase.com
what i want to achieve is a script that will access metabase via api, query the bookings table and based on some criteria return value from field ID (booking id).

https://www.metabase.com/docs/latest/api/card#post-apicardcard-idquery


Now, this is what i've done so far:

1. I managed to create my session token via postman
2. I run this script in airtable:

 

 

const METABASE_API_URL = 'https://CCCCCCCCCC.metabaseapp.com/api/card/AAAAAAAA/query';
const METABASE_TOKEN = 'BBBBBBBBBBBBBBBBBB';

async function fetchMetabaseData() {
    try {
        const queryPayload = JSON.stringify({
            type: 'native',
            native: {
                query: "SELECT id FROM bookings WHERE client_id = '102827'"
            }
        });

        const headers = {
            'Content-Type': 'application/json',
            'Authorization': `Bearer ${METABASE_TOKEN}`
        };

        const response = await fetch(METABASE_API_URL, {
            method: 'POST',
            headers: headers,
            body: queryPayload  // The body must be a string
        });

        if (!response.ok) {
            console.error('HTTP Error:', response.status, await response.text());
            throw new Error('Failed to fetch data from Metabase: ' + response.statusText);
        }

        const data = await response.json();

        if (data && data.data.length > 0) {
            let bookingId = data.data[0].id;
            console.log('Booking ID:', bookingId);
        } else {
            console.log('No bookings found for the specified client_id.');
        }
    } catch (error) {
        console.error('Error fetching data from Metabase:', error);
    }
}

fetchMetabaseData();

 

where:
CCCCCCC - company name 
AAAAAAA - question ID
BBBBBBB - token from postman


the script itself is running successfully but the console is returning:

"HTTP Error:" 401 "Unauthenticated"

"Error fetching data from Metabase:" Error {name: "Error"}



please help, im really stuck in it 

1 Reply 1
Kyrstin_Graves
7 - App Architect
7 - App Architect

That error message indicated that your authentication with the Metabase API failed. Usually, this is a problem with the way the authentication token is used or with the token itself. You can try a few things:

  1. Ensure the token you are using in your script is still valid. Tokens can expire or may have been invalidated for some reason, so you may have to generate a new token. 
  2. Double-check there are no leading or trailing spaces in your Authorization header that could be messing it up.
  3. Verify that the Metabase_API_URL is correct. If there is a mistake in the URL, like an incorrect domain name or path, your requests could go to the wrong endpoint where you don't have permission with your token.
  4. Ensure your token has the required permissions to execute queries on the Metabase deployment. Sometimes tokens are restricted to certain actions or datasets. 
  5. If you test in Postman, with the exact same script, and it works, the issue might be with how your script is executing the request. 

Additionally, it could be where you're executing in Airtable. Are you executing in the scripting block extension or in an automation?

I made 2 changes to your script. I added a console.log for the full response data to help give more information on the issue. I also add data.data to your booking ID section to endure that your data object contains a valid data property before it attempts to access data.data.length. This helps prevent runtime errors that could occur if data.data were undefined or null.

```

const METABASE_API_URL = 'https://CCCCCCCCCC.metabaseapp.com/api/card/AAAAAAAA/query';
const METABASE_TOKEN = 'BBBBBBBBBBBBBBBBBB';

async function fetchMetabaseData() {
try {
const queryPayload = JSON.stringify({
type: 'native',
native: {
query: "SELECT id FROM bookings WHERE client_id = '102827'"
}
});

const headers = {
'Content-Type': 'application/json',
'Authorization': `Bearer ${METABASE_TOKEN}`
};

const response = await fetch(METABASE_API_URL, {
method: 'POST',
headers: headers,
body: queryPayload
});

if (!response.ok) {
console.error('HTTP Error:', response.status, await response.text());
throw new Error('Failed to fetch data from Metabase: ' + response.statusText);
}

const data = await response.json();
console.log('Response Data:', data); // Added to log the full response data

if (data && data.data && data.data.length > 0) {
let bookingId = data.data[0].id;
console.log('Booking ID:', bookingId);
} else {
console.log('No bookings found for the specified client_id.');
}
} catch (error) {
console.error('Error fetching data from Metabase:', error);
}
}

fetchMetabaseData();

```