How to fetch JSON data from an API into Looker Studio (Google Data Studio)

Introduction

This tutorial provides a step-by-step guide on how to fetch JSON data from an API into Looker Studio using a custom connector developed with Google Apps Script. By following this guide, you will learn to create, deploy, and connect a custom connector, making it easier to visualize and analyze data in Looker Studio.

Step 1: Overview of the API Data

• Understand the structure and format of the JSON data you want to fetch from the API.
• Familiarize yourself with the API documentation to know the endpoints and data available.
• Test the API using tools like Postman or your browser to see the response format.

Step 2: Create a New Google Apps Script Project

• Open Google Drive.
• Click on "New" > "More" > "Google Apps Script" to create a new project.
• Rename your project to something descriptive, like "Looker Studio JSON Connector".

Step 3: Edit the Manifest File

• In the Apps Script project, access the "appsscript.json" file.
• Update the manifest to define the project as a connector:

  {
    "oauthScopes": [
      "https://www.googleapis.com/auth/script.external_request",
      "https://www.googleapis.com/auth/spreadsheets"
    ],
    "addOns": {
      "common": {
        "name": "JSON Connector",
        "logoUrl": "URL_TO_LOGO",
        "homepageTrigger": {
          "runFunction": "onHomepage"
        }
      }
    }
  }
  

• Adjust the URL and other details as necessary.

Step 4: Overview of Basic Methods of a Connector

• Learn the essential methods needed for the connector:
  • getConfig(): Defines how the connector is configured.
  • getSchema(): Describes the schema of the data.
  • getData(): Fetches and returns the actual data from the API.

Step 5: Implement the Actual Code

• Write the code for your connector. Here’s a basic example:

  function getConfig() {
      return {
          configParams: [
              {
                  name: "apiUrl",
                  label: "API URL",
                  helpText: "Enter the API URL to fetch JSON data",
                  required: true
              }
          ]
      };
  }
  
  function getSchema(request) {
      return {
          schema: [
              {
                  name: "id",
                  label: "ID",
                  dataType: "NUMBER",
                  semantics: { conceptType: "DIMENSION" }
              },
              {
                  name: "name",
                  label: "Name",
                  dataType: "STRING",
                  semantics: { conceptType: "DIMENSION" }
              }
          ]
      };
  }
  
  function getData(request) {
      const response = UrlFetchApp.fetch(request.configParams.apiUrl);
      const json = JSON.parse(response.getContentText());
      // Process JSON to match schema
      return { rows: json.map(item => ({ values: [item.id, item.name] })) };
  }
  

Step 6: Assign a Google Cloud Project

• Go to the Google Cloud Console and create a new project.
• Enable the "Google Sheets API" and "Google Apps Script API".
• Note the Project ID and link it to your Apps Script project under "Resources" > "Cloud Platform project".

Step 7: Deploy Your Connector

• In your Apps Script project, click on "Deploy" > "Test deployments".
• Choose "Add-on" and fill in the necessary information.
• Click on "Deploy" to save your connector.

Step 8: Visit the Published Connector's Page

• After deployment, access the published connector by navigating to the URL provided in the deployment settings.

Step 9: Connect Your Connector to Looker Studio

• Open Looker Studio.
• Click on "Create" > "Data Source" > "Community Connector".
• Search for your deployed connector and authorize it.
• Input any necessary configuration parameters (e.g., API URL).

Step 10: Work with Your Connector in Looker Studio

• Once connected, you can create reports and dashboards using the fetched JSON data.
• Utilize Looker Studio's features to visualize the data effectively.

Conclusion

You have successfully created a custom connector to fetch JSON data from an API into Looker Studio. By following these steps, you can now visualize and analyze your data seamlessly. For further enhancements or troubleshooting, consider diving into more advanced features of Google Apps Script or consulting additional resources.