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:
Copy
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.