Goal:
To call a Rest API Query over an array of urls, and combine the result into a single table.
Steps:
Created a native retool query/function "getGDriveCSV":
- This is a Rest API Query which gets a csv download from google drive as a Base64 datatype, decodes the data, and parses it using papa.parse.
- This query works well when I pass the url directly into the Base URL input box.
Created a native retool JS Query: "Query5" to iterate through urls using getGDriveCSV.
- This step is giving me problems, and I am having trouble debugging using retools built in debuggers
- In theory, Query5 simply
- takes an array of url stirngs,
- extracts the ids from each string,
- pops the ID into "inputText2" (because I didn't know how to directly feed it into the RestAPI "getGDriveCSV")
- executes getGDriveCSV for each id value
- and combines the response from each call of getGDriveCSV.
Details:
I am a novice script writer and not very familiar with retool, hence setting the url string to "inputText2.value".
However, with that stated, I suspect that the problem lies in both:
- Query5 and
- in how I am trying to pass the url to the getGDriveCSV query
Query5 code:
const gDriveURLs = [
"https://docs.google.com/spreadsheets/d/1************************iE1_p-8k/export?format=csv",
"https://docs.google.com/spreadsheets/d/1************************MRZBz71LFc/export?format=csv",
"https://docs.google.com/spreadsheets/d/1************************W9iKOq27/export?format=csv"
];
// Function to extract IDs from Google Drive links
const extractedIds = gDriveURLs.map(url => {
const match = url.match(/\/d\/([a-zA-Z0-9_-]+)/); // Regex to capture ID between /d/ and /view
return match ? match[1] : null; // If match found, return the ID, else null
}).filter(id => id !== null); // Remove null values (in case of no match)
//return extractedIds
let dataConcat = [];
let index = 0
// Function to fetch and concatenate data using a while loop
const fetchData = async () => {
while (index < extractedIds.length) {
const url = extractedIds[index];
// Set the value of textInput2 to the URL
textInput2.setValue(url);
try {
// Wait for query4 to trigger and get the result
const result = await getGDriveCSV.trigger();
// Concatenate the result (assuming it's an array) to dataConcat
dataConcat = dataConcat.concat(result);
// Move to the next URL in the array
index++;
} catch (error) {
console.error("Error during query execution:", error);
break; // Exit the loop if there's an error
}
}
console.log("Data concatenated:", dataConcat); // Log the concatenated data
};
// Call the fetchData function to start the loop
fetchData();
-
Screenshots:
I think the implementation that you've shared here is very close to functional and just needs a couple of tweaks.