How to connect Metabase with Google Sheet?

Question

I have username and password to the metabase our company use heavily. Everyday I have to download CSVs frequently and then export them to google sheets to make report or analysis. Is there any way to connect Metabase to Google Sheet so that the sheets pull CSVs automatically from Metabase url?

Answer 1

You could try and write a script using Google Apps Script for your issue. An idea would be to use the UrlFetchApp.fetch() method which allows scripts to communicate with other applications or access other resources on the web by making a request to fetch a URL, in your case the Metabase URL.

This following code snippet might give you a glimpse on what to do next:

function myFunction() {
  var sheet = SpreadsheetApp.getActive().getSheets()[0];

  var data = {
    // the data that you will be using
  };

  var options = {
    'method': 'post',
    'payload': data
  };

  var response = UrlFetchApp.fetch('https://metabase.example.com/api/card/1/query/json', options);

  var responseData = JSON.parse(response.getContentText());

  sheet.appendRow(responseData);
}

What it actually does: it uses the Metabase API to fetch the data you want by using a POST request (the reason a POST request is used is because according to the API Documentation for Metabase v0.32.2 it runs the query associated with a card, and returns its results as a file in the specified format). Then the response from the request is appended to your sheet by using .appendRow().

Furthermore, you can read more about UrlFetchApp and Apps Script here:

1. Class UrlFetchApp;

2. Apps Script;

3. Spreadsheet App.