In today's data-driven world, businesses often need to access information from specialized or proprietary sources. This is where custom APIs and tools like Superjoin's Custom API Connector become invaluable, allowing seamless integration of unique data sources with platforms like Google Sheets.

What is a Custom API Connector?

Custom API (Application Programming Interface) is a tailored interface that enables communication between different software systems, designed to meet specific business needs. Unlike standard public APIs, custom APIs often provide access to proprietary data or specialized functionalities.

Superjoin's Custom API Connector allows users to integrate these custom APIs directly with Google Sheets, enabling data pull from any REST API into spreadsheets for analysis and reporting.

Advantages of Using Custom APIs

• Flexibility: Custom APIs can connect to almost any online service, providing tailored data retrieval.

• Automation: Automatically update your data in Google Sheets, saving time and reducing manual effort.

• Scalability: Handle large volumes of data efficiently, making it suitable for various business needs.

• Real-Time Data: Access real-time data, ensuring your analysis is based on the most current information.

• Integration: Easily integrate with other tools and services, enhancing your overall data ecosystem.

Methods to Integrate Any API Into Google Sheets

Method 1: Without Code - Using Superjoin

To connect any API to Google Sheets using Superjoin, follow these detailed steps:

Step 1: Click Extensions from the Google Sheets menu. Select Add-ons and choose to Get Add-ons. This will bring you to the Google Workspace Marketplace.

Step 2: Look for Superjoin in the Google Marketplace pop-up window and install the app by following the prompts.

Step 3: Navigate back to the Extensions menu tab and launch Superjoin from there. You should see Superjoin running as a Google Sheets side panel on the right side of your screen. 

Step 4: From Sources, select Custom API from the list.

Step 5: Choose Authorization Type

• No Auth: Use if the API does not require any authentication.

  
  

• Basic: Enter a username and password if the API requires these for access.

  
  

• Bearer Token: Input a token if the API uses token-based authentication.

  
  

• OAuth2.0: Follow OAuth2.0 setup if the API uses this authentication method.

  
  

Step 6: Select Request Method

• GET: Use to retrieve data from the API.

• POST: Use to send data to the API.

and Input the endpoint URL of the API you are connecting to.

Step 7: Configure Request Details and Connect

• Add Headers (Optional): If the API requires headers, add them as key-value pairs (e.g., Content-Type: application/json).

  
  

• Enter Request Body (Optional): Provide the request body in JSON format or as key-value pairs if needed, typically for POST requests.

  
  

• Connect: Click 'Connect' to establish the connection and preview the data fetched from the API.

  
  

Step 8: After authorization, you'll see a list of objects in the Data Preview Window.

Step 9: Setup Pagination (Optional)

Pagination is crucial for managing large datasets by breaking them into smaller segments, ensuring efficient data handling, and preventing system overload, here’s how to set it up:

• Offset should be set through: Determine where the offset value should be placed in your API request (query parameters, headers, or body).

  
  

• Offset should be set as the property name: Specify the exact name of the field that the API expects for the offset value (e.g., offset, start, skip).

  
  

• No. of records per page: Define the number of records to fetch on each page.

  
  

• Append offset in first API request as well: Decide whether to include the offset in the first request (default is false).

For detailed information on setting up pagination features, please refer to Superjoin's Documentation.

Step 10: After pulling your data Superjoin lets you refresh your imported data instantly with one click or set it to auto-refresh on a pre-set schedule.

This way, you won't need to manually update your data in Google Sheets to reflect changes from the source.

Method 2: Without Code - Using Google App Script

Integrating a Custom API with Google Sheets using Google Apps Script provides a powerful way to automate data fetching and keep your data up-to-date. Here’s an in-depth guide on how to set this up:

Step 1: Open Google Sheets and Navigate to Apps Script

• Open your Google Sheets document.

• Navigate to Extensions > Apps Script. This opens the Google Apps Script editor in a new tab.

Step 2: Write a Custom Script Using UrlFetchApp

• Clear the Default Code:

In the Apps Script editor, you will see some default code. Clear this to start with a blank script and write the Script, use UrlFetchApp to fetch data from the API. Here’s a sample script to get you started:

function fetchData() {
  var url = 'YOUR_API_ENDPOINT';  // Replace with your API endpoint
  var options = {
    'method': 'get',  // Use 'post' for POST requests
    'headers': {
      'Authorization': 'Bearer YOUR_ACCESS_TOKEN'  // Replace with your API's authentication method
    }
  };
  
  try {
    var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response.getContentText());
    
    // Log the data to check if it is fetched correctly
    Logger.log(data);
    
    // Process and write the data to the sheet
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var range = sheet.getRange(1, 1, data.length, Object.keys(data[0]).length);  // Adjust the range based on your data structure
    var values = data.map(function(item) {
      return Object.values(item);  // Adjust based on your data structure
    });
    
    range.setValues(values);
  } catch (e) {
    Logger.log('Error: ' + e.toString());
  }
}

Step 3: Handle Authentication

The options object in the script includes headers for authentication. Modify this based on your API’s requirements. Common authentication methods include API keys, bearer tokens, or basic authentication.

Step 4: Set Up Time-Driven Triggers to Automate Data Fetching

In the Apps Script editor, click the clock icon on the left sidebar to open the "Triggers" page.

• Click on + Add Trigger in the bottom right.

  
  

• Configure the settings:

  • Function to Run: Select your function (e.g., fetchData).

  • Deployment: Select Head.

  • Select Event Source: Choose Time-driven.

  • Type of Time-based Trigger: Select an interval (e.g., hourly, daily).

    
    

• Click Save to activate the trigger.

Sample Trigger Setup Code

Alternatively, you can set up triggers programmatically:

function createTimeDrivenTriggers() {
  ScriptApp.newTrigger('fetchData')
    .timeBased()
    .everyHours(1)  // Adjust the interval as needed
    .create();
}

Run this function once to set up the time-driven trigger.

Why Choose Superjoin?

Superjoin's Custom API Connector method is superior to using Google Apps Script for integrating APIs into Google Sheets.

• User-Friendly: No coding required, easy setup.

• Advanced Features: Supports multiple authentication methods.

• Efficient Data Handling: Manages large datasets with pagination.

• Real-Time Updates: Auto-refreshes data without manual effort.

• Seamless Integration: Quick integration with Google Sheets.

• Time-Saving: Automates data fetching, reducing manual work.

Integrating API data into Google Sheets using Superjoin's Custom API Connector opens up a world of possibilities for data analysis and automation. By following this guide, you've learned how to set up Superjoin, make and automate API requests, handle imported data, and utilize advanced features.

As you become more comfortable with Superjoin, explore different APIs and push the boundaries of what's possible with your data. The combination of real-time API data and Google Sheets' powerful analysis tools can transform your decision-making processes and drive your business forward.