Google Sheets Integration

Overview

In this guide, we’ll walk through the process of fetching data from the Kadoa API and adding it to a Google Sheet using Google Apps Script.

This workaround is a temporary solution until Kadoa releases an official Google Sheets integration.

Prerequisites

Before you begin, make sure you have the following:

• A Google account
• A Google Sheet where you want to store the data
• Access to the Kadoa API and your API key

Step-by-Step Guide

1. In your Google Sheet, go to “Extensions” -> “Apps Script” to open the Google Apps Script editor.

2. Replace the contents of the script editor with the following code:

function fetchDataFromKadoa() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var workflowId = 'YOUR_WORKFLOW_ID'; // Add Kadoa workflow ID
  var apiKey = 'YOUR_API_KEY'; // Add Kadoa API key
  var limit = 50; // Choose record limit
  var page = 1; // Starting page
  var baseUrl = `https://api.kadoa.com/v4/workflows/${workflowId}/data`;
  var headers = {
    "x-api-key": apiKey
  };

  var options = {
    method: "get",
    headers: headers,
  };

  var allData = [];

  do {
    var url = `${baseUrl}?limit=${limit}&page=${page}`;
    var response = UrlFetchApp.fetch(url, options);
    var responseData = JSON.parse(response.getContentText());

    // Extract the data array from the response
    var data = responseData.data;
    var pagination = responseData.pagination;

    if (data.length === 0) {
      Logger.log("No data found.");
      break;
    }

    // Add current page data to allData array
    allData = allData.concat(data);

    // Check if there are more pages
    page++;
  } while (page <= pagination.totalPages);

  if (allData.length === 0) {
    Logger.log("No data found across all pages.");
    return;
  }

  // Get column headers dynamically from the first object in the data array
  var headers = Object.keys(allData[0]);

  // Write headers to the sheet
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  // Write data to the sheet
  var rowData = [];
  allData.forEach(function (item) {
    var row = [];
    headers.forEach(function (header) {
      row.push(item[header] || ""); // Add a fallback if some data is missing
    });
    rowData.push(row);
  });

  // Write the data starting from the second row
  sheet.getRange(2, 1, rowData.length, headers.length).setValues(rowData);
}

3. Replace 'YOUR_WORKFLOW_ID' with your Kadoa workflow ID and 'YOUR_API_KEY' with your Kadoa API key.

4. Save the script and run the fetchDataFromKadoa function either by clicking the play button in the toolbar or by going to “Run” -> “Run function” -> “fetchDataFromKadoa”.

5. If this is your first time running a script accessing external APIs, authorize the script to access external services.

6. Check your Google Sheet. The data from the Kadoa API should now be populated.

Scheduling Automatic Updates (Optional)

To keep your Google Sheet data in sync with Kadoa, you can set up a trigger to run the script on a schedule:

1. In the script editor, go to “Edit” > “Current project’s triggers”.
2. Click “Add Trigger” and configure the desired frequency.
3. Save the trigger.

The script will now automatically fetch and update the data in your Google Sheet based on the specified schedule.