Good evening KT Community, hope you’re all doing well? I’m trying to find a simple and reliable API solution to pull all data from KoBo projects through to GoogleSheets, I’ve been using synchronous data exports before, however data sets are getting too large for them.
I’m now working with API Connector and had some success pulling the data sets through, however the format is not the same as when it’s asynchronous (which I usually did via csv), e.g. text data will read as wurzel_grumage rather thatn Wurzel Grumage and column headings will have their grouping formatting i.e. results.group_en8fu57/What_is_the_clients_gender rather than “What is the clients gender?”
Can anybody advise how I can have the data pulled through in a asynchronous/csv style via API-Connector?
Hi @wroos thanks for coming back to me. Apologies for the error, I’m using synchronous data exports. I need to use something which can pull data from KoBo to GoogleSheets.
Hi, i discovered method that one can use to pull all data from Kobo to googlesheet using Appscript. i Developed the script where it also order columns to match what is in the xlsform during pulling. The advantage of using appscript is that even if the googlesheet has been messed up with during the next pull everything with be pulled up and it will not require another time to configure csv or xls. I have been using this method for over a year and its 100% perfect since it picks and update new fields automatically. If one would like to try it out i can help. This method i shared it on my website and am not sure how to share resources here without admin permission.
Thanks for the feedback here and apologies for the delayed response. Not been on the community for a while as KoBo’s been ticking away.
@samc125 so the issue was more that certain columns can’t be pulled through via API connector, e.g. if there’s a categorical variable questions, only the column with the response selected will be available to pull. Whereas there’s boolean columns for each individual category that are visible on KoBoToolbox and downloadable from the website, but aren’t available on API connector.
@luyali thanks for the heads-up on appsscript possibility, I’ve been using for a few other wrangling and sorting bits (with the help of AI) and it’s impressive, good to know this is an option. If it’s feasible to send your website through the chat please go ahead.
I’m not 100% clear on what you are referring to here, but provided you setup your Download export settings with
Export Select Many questions as = Single and separate columns
then the resulting synchronous data link for, say XLSX, will include these additional expanded ‘boolean columns’ for every multi-select choice option - indicating whether that choice was selected or not - along with the usual single question response column containing the raw space-separated value
I have worked on appscript and work just perectly and don’t require a lot of effort and can be customizd. here is the script remember this is v2 API: Copy and paste the following Google Apps Script into the editor. Replace the placeholder values with your real token, form UID, and sheet ID:
function importKoboData() {
const TOKEN = "YOUR_KOBO_API_TOKEN"; // Paste your KoboToolbox API token here
const ASSET_UID = "YOUR_KOBO_FORM_UID"; // Paste your form UID here
const SHEET_ID = "YOUR_GOOGLE_SHEET_ID"; // Paste your Google Sheet ID here
const BASE_URL = "https://kf.kobotoolbox.org";
let url = `${BASE_URL}/api/v2/assets/${ASSET_UID}/data/`;
const options = {
method: "get",
headers: {
Authorization: `Token ${TOKEN}`
},
muteHttpExceptions: true
};
let allResults = [];
// Loop through all pages of results (handles more than 1,000 submissions)
while (url) {
const response = UrlFetchApp.fetch(url, options);
const status = response.getResponseCode();
if (status !== 200) {
throw new Error(`Kobo API error ${status}: ${response.getContentText()}`);
}
const json = JSON.parse(response.getContentText());
if (json.results && json.results.length > 0) {
allResults = allResults.concat(json.results);
}
url = json.next; // Move to next page or null to stop
}
if (allResults.length === 0) {
Logger.log("No data found");
return;
}
const ss = SpreadsheetApp.openById(SHEET_ID);
let sheet = ss.getSheetByName("KoboData") || ss.insertSheet("KoboData");
sheet.clearContents();
const headers = Object.keys(allResults[0]);
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
const rows = allResults.map(record =>
headers.map(header => {
const value = record[header];
return typeof value === "object" && value !== null
? JSON.stringify(value)
: value;
})
);
sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
Logger.log(`${rows.length} records imported successfully.`);
}
i have a website that i always post this and more challenging kobo and AI integration. Appscript can also help to:
1) Remove duplicate automatically - when user makes mistake can just redo the survey with correct data and old data submitted will be automatically be replaced by new data submitted hence no more task or deleting old data before another is submitted.
2)Its faster to sync since triggers can be defined hence best for making dashboards and share with team who will see results in real-time rather than waiting for data to be shared. For any assistance in Kobo Automation am available to assist and i also have projects i have automated. for the above tutorial can be found here: https://formtoinsight.com/how-to-link-kobotoolbox-to-google-sheets-api-v2
