Import Kadoa Data to Google Sheets

​

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.

​

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 url = `https://api.kadoa.com/v3/workflows/${workflowId}/data`;
  var headers = {
    "x-api-key": apiKey
  };

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

  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());

  // Get column headers dynamically
  var headers = [];
  if (data.length > 0) {
    headers = Object.keys(data[0].data[0]);
  }

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

  // Write data to the sheet
  var rowData = [];
  data.forEach(function (item) {
    var row = [];
    Object.keys(item.data[0]).forEach(function (key) {
      row.push(item.data[0][key]);
    });
    rowData.push(row);
  });

  sheet.getRange(sheet.getLastRow() + 1, 1, rowData.length, rowData[0].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.