Breaking the Data Silos with a Google Looker Studio Custom Connector

In today’s data-driven world, businesses need insights from every system — not just the ones with built-in integrations. Platforms like Shopify, HubSpot, or custom CRMs often hold valuable data, but without native connectors, it remains siloed and hard to analyze.

This guide shows you how to build a custom Looker Studio connector using Google Apps Script. With it, you can seamlessly bring Shopify Orders into Looker Studio dashboards, giving you real-time visibility into metrics such as order totals, financial status, and fulfillment progress — all without manual exports or complex pipelines.

1. Understanding the Looker Studio Connector Architecture

Each connector must implement three core functions:

2. Base Connector Structure

Copy
1/**
2 * @fileoverview Looker Studio Custom Connector Template
3 * Deploy as a Google Apps Script Web App.
4 */
5
6// 1. getConfig — prompts user for input
7function getConfig(request) {
8  return {
9    configParams: [
10      {
11        name: 'apiKey',
12        displayName: 'Shopify API Key',
13        helpText: 'Private App Access Token',
14        text: true
15      },
16      {
17        name: 'storeName',
18        displayName: 'Shopify Store Name',
19        helpText: 'Example: my-store-name',
20        text: true
21      }
22    ]
23  };
24}
25
26// 2. getSchema — defines structure of Shopify Orders
27function getSchema(request) {
28  var cc = DataStudioApp.createCommunityConnector();
29  var fields = cc.getFields();
30  var types = cc.FieldType;
31
32  // Order ID
33  var orderId = fields.newDimension()
34      .setId('orderId')
35      .setName('Order ID')
36      .setDescription('Unique identifier for the Shopify order')
37      .setType(types.TEXT);
38
39  // Created At
40  var createdAt = fields.newDimension()
41      .setId('createdAt')
42      .setName('Order Created At')
43      .setDescription('Date and time when the order was created')
44      .setType(types.YEAR_MONTH_DAY);
45
46  // Total Price
47  var totalPrice = fields.newMetric()
48      .setId('totalPrice')
49      .setName('Total Price')
50      .setDescription('Total order amount in USD')
51      .setType(types.CURRENCY_USD);
52
53  // Financial Status
54  var financialStatus = fields.newDimension()
55      .setId('financialStatus')
56      .setName('Financial Status')
57      .setDescription('Status of payment for the order')
58      .setType(types.TEXT);
59
60  // Fulfillment Status
61  var fulfillmentStatus = fields.newDimension()
62      .setId('fulfillmentStatus')
63      .setName('Fulfillment Status')
64      .setDescription('Current fulfillment status of the order')
65      .setType(types.TEXT);
66
67  // Set default metric and dimension
68  fields.setDefaultMetric(totalPrice.getId());
69  fields.setDefaultDimension(createdAt.getId());
70
71  return { 'schema': fields.build() };
72}
73
74// 3. getData — fetches and returns API data
75function getData(request) {
76  var apiKey = request.configParams.apiKey;
77  var storeName = request.configParams.storeName;
78  var apiResponse = fetchExternalData(apiKey, storeName);
79  var flatRows = processApiResponse(apiResponse);
80
81  var data = flatRows.map(function(row) {
82    var values = [];
83    request.fields.forEach(function(field) {
84      values.push(row[field.name]);
85    });
86    return { values: values };
87  });
88
89  return { schema: request.fields, rows: data };
90}

3. Fetching Shopify Orders

Copy
1function fetchExternalData(apiKey, storeName) {
2  var apiVersion = '2024-04';
3  var url = 'https://' + storeName + '.myshopify.com/admin/api/' + apiVersion + '/orders.json?status=any&limit=50';
4
5  var options = {
6    method: 'GET',
7    headers: {
8      'X-Shopify-Access-Token': apiKey,
9      'Content-Type': 'application/json'
10    },
11    muteHttpExceptions: true
12  };
13
14  try {
15    var response = UrlFetchApp.fetch(url, options);
16    var json = JSON.parse(response.getContentText());
17    return json;
18  } catch (e) {
19    throw new Error('Error fetching Shopify data: ' + e);
20  }
21}

4. Flattening Nested Shopify Data

Shopify’s API returns nested JSON (orders → line_items). Looker Studio only supports flat tables, so we must flatten the data.

Copy
1function processApiResponse(apiResponse) {
2  if (!apiResponse || !apiResponse.orders) return [];
3
4  var flatRows = [];
5
6  apiResponse.orders.forEach(function(order) {
7    flatRows.push({
8      orderId: order.id,
9      createdAt: order.created_at,
10      totalPrice: parseFloat(order.total_price),
11      financialStatus: order.financial_status,
12      fulfillmentStatus: order.fulfillment_status
13    });
14  });
15
16  return flatRows;
17}

5. Testing the Connector

1. Open Google Apps Script.
2. Paste the code and deploy as a Web App (accessible to “Anyone with the link”).
3. Go to Looker Studio → Create → Data Source → Build Your Own Connector.
4. Paste your script’s deployment URL.
5. Configure API Key and Store Name → Connect → Add to Report.

You’ll now see Shopify Orders live in Looker Studio.

6. Bonus: Adding Date Range Support

Add this snippet to your getConfig() for selecting timeframes:

Copy
1{
2  name: 'startDate',
3  displayName: 'Start Date',
4  dateRange: true
5}

Then use request.dateRange inside getData() to filter orders.

7. Example Looker Studio Dashboard

Your dashboard can now show charts like:

• Orders by Month
• Total Sales by Financial Status
• Fulfillment Breakdown by Product Line