[BUG] Timeout listing folders on Microsoft Excel Account

[lymanepp]

Describe the bug
I'm trying to use "Add A Worksheet Tablerow" in a workflow. I have an authenticated Microsoft Account. I consistently get a timeout when listing the Folder Ids.

To Reproduce
Steps to reproduce the behavior:

1. Go to workflow
2. Click on '+' in workflow to Add Action.
3. Select "Microsoft Excel"
4. Select "Add A Worksheet Tablerow"
5. Select Microsoft Excel Account
6. Click on "Folder Id" to get list of folders
7. Times out waiting for list of folders

Expected behavior
Presented with a list of folders so I can add a row to an existing Worksheet.

Screenshots

Desktop (please complete the following information):

• OS: Windows 11
• Browser: Firefox
• Version: 130

Additional context
N/A

[vunguyenhung]

Hi @lymanepp, may I ask how many folder do you have?

[lymanepp]

Hi @vunguyenhung, there are a LOT of folders. I created a test that recurses through the drive using "/me/drive/root/children?$filter=folder ne null" via REST API. It timed out after 5 minutes (just like the bug I reported here). Recursing through the entire drive to present a list of folders is terribly inefficient with the Microsoft Graph API and only works when there aren't a LOT of folders. Other approaches such as entering the path directly or navigating through the tree one level at a time would work.

[lymanepp]

Here's a solution that works much faster and completes successfully. It appears that there are 4,049 folders. It ran in 44015ms.

import { axios } from "@pipedream/platform";

const GRAPH_API_BASE_URL = "https://graph.microsoft.com/v1.0";
const BATCH_LIMIT = 20; // Maximum requests per batch

export default defineComponent({
  props: {
    microsoft_excel: {
      type: "app",
      app: "microsoft_excel",
    }
  },
  async run({ steps, $ }) {
    const headers = {
      Authorization: `Bearer ${this.microsoft_excel.$auth.oauth_access_token}`,
      "Content-Type": "application/json",
    };

    async function batchCall(requests) {
      const url = GRAPH_API_BASE_URL + "/$batch";
      const body = { requests };

      // console.log("Sending batch request with", requests.length, "requests...");

      try {
        const response = await axios($, {
          method: "POST",
          url,
          headers,
          data: body,
        });

        if (response.responses) {
          return response.responses;
        } else {
          console.error("Batch response did not include expected 'responses':", response);
          throw new Error("Invalid batch response format");
        }
      } catch (error) {
        console.error("Error with batch call:", error.response ? error.response.data : error.message);
        throw error;
      }
    }

    async function findAllFolders() {
      let allFolders = [];
      let foldersToProcess = [];

      // Fetch top-level folders
      try {
        let topLevelResponse = await axios($, {
          url: GRAPH_API_BASE_URL + "/me/drive/root/children?$filter=folder ne null",
          headers,
        });

        console.log(topLevelResponse);
        let topLevelFolders = topLevelResponse.value;
        topLevelFolders.forEach(folder => {
          folder.fullPath = folder.name; // Initialize fullPath for top-level folders
        });
        allFolders.push(...topLevelFolders);
        foldersToProcess.push(...topLevelFolders);
      } catch (error) {
        console.error("Error fetching top-level folders:", error.response ? error.response.data : error.message);
        throw error;
      }

      // Process folders recursively
      while (foldersToProcess.length > 0) {
        const currentBatch = [];
        while (foldersToProcess.length > 0 && currentBatch.length < BATCH_LIMIT) {
          const folder = foldersToProcess.shift(); // Get the next folder to process
          currentBatch.push({
            id: folder.id,
            method: "GET",
            url: `/me/drive/items/${folder.id}/children?$filter=folder ne null`,
          });
        }

        // console.log(`Sending batch of ${currentBatch.length} requests...`);

        try {
          const batchResponses = await batchCall(currentBatch);

          // Process batch responses
          batchResponses.forEach((batchResponse) => {
            if (batchResponse.status === 200) {
              const subfolders = batchResponse.body.value;
              if (subfolders) {
                subfolders.forEach(subfolder => {
                  // Build full path for subfolders using the parent's fullPath
                  const parentFolder = allFolders.find(f => f.id === subfolder.parentReference.id);
                  subfolder.fullPath = `${parentFolder.fullPath}/${subfolder.name}`;
                });
                allFolders.push(...subfolders); // Add subfolders to the main list
                foldersToProcess.push(...subfolders); // Add new subfolders to the processing queue
              } else {
                console.error(`No folders found in response for batch ${batchResponse.id}.`);
              }
            } else {
              console.error(`Error in batch request ${batchResponse.id}:`, batchResponse.body);
            }
          });
        } catch (error) {
          console.error("Batch call failed:", error.message);
        }
      }

      // Convert allFolders to only include fullPath
      const fullPathFolders = allFolders.map(folder => ({ fullPath: folder.fullPath }));

      console.log(`Total number of folders: ${fullPathFolders.length}`);
      console.log(JSON.stringify(fullPathFolders.slice(0, 10), null, 2));

      const matchingFolders = fullPathFolders.filter(folder => 
        folder.fullPath && folder.fullPath.includes('Private Practice')
      );
      
      console.log(`Number of folders containing 'Private Practice': ${matchingFolders.length}`);
      console.log(JSON.stringify(matchingFolders, null, 2));
      
      return fullPathFolders; // Return only fullPath properties
    }

    try {
      const allFolders = await findAllFolders();
      return allFolders; // Return all folders found
    } catch (error) {
      console.error("General error occurred:", error.message);
    }
  },
});

[vunguyenhung]

Hi @lymanepp, thank you so much for your information! I'll add this to Pipedream Component backlog

[jcortes]

Hi @vunguyenhung and @lymanepp I would like if possible the user to try out the latest PR I pushed to see if that works, if that's not the case I would build on top of it with the batch request approach the user recommended. So please let me know in any case, thanks!

[lymanepp]

Hi @vunguyenhung and @lymanepp I would like if possible the user to try out the latest PR I pushed to see if that works, if that's not the case I would build on top of it with the batch request approach the user recommended. So please let me know in any case, thanks!

Sure, I can try it. Has it been deployed to prod already? If so, I'm still getting a timeout listing folders.

Edit: I looked at the PR. You will need to incorporate batching to get any performance improvements.