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
-
In your Google Sheet, go to “Extensions” -> “Apps Script” to open the Google Apps Script editor.
-
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);
}
-
Replace 'YOUR_WORKFLOW_ID'
with your Kadoa workflow ID and 'YOUR_API_KEY'
with your Kadoa API key.
-
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”.
-
If this is your first time running a script accessing external APIs, authorize the script to access external services.
-
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:
- In the script editor, go to “Edit” > “Current project’s triggers”.
- Click “Add Trigger” and configure the desired frequency.
- Save the trigger.
The script will now automatically fetch and update the data in your Google Sheet based on the specified schedule.