Using JS Query to iterate through URL array with Rest API

Queries and Resources
,
1

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();