Issue Passing Function Block Array/Object Output to Google Sheet/Doc Blocks

[spawnupe]

Hi MindStudio Community,

We’re encountering a persistent issue when trying to populate Google Sheets or Google Docs using data generated by a “Run Function” block (using JavaScript).

Workflow Goal:
The goal is to fetch data, process it using AI and custom functions, format the results into an array-of-arrays structure suitable for Google Sheets, and then either create a new Google Sheet or update an existing one with this array data.

The Problem:

1. Function Block Success: Our “Run Function” block executes successfully. Using console.log, we have verified that it correctly processes inputs (from ai.vars) and returns the data in the expected format (either a direct array-of-arrays [[...],[...]] or an object containing the array like { sheetData: [[...],[...]] }).
2. Integration Block Input Failure: We then try to use this output variable in the Data/Content/Values field of either a “Create Google Sheet”, “Update Google Sheet”, or “Create Google Document” block. We have tried both:
   • Referencing the direct output: {{functionBlockName}} (when the function returns the array directly).
   • Using the get helper: {{get functionBlockName "$.sheetData"}} (when the function returns { sheetData: [...] }).
3. Observed Behavior: In all these cases, the logs for the Google Sheet/Doc block show that the Content input resolves to an empty string (Content: ""). Consequently, the sheet/doc is created empty, or the update/doc creation fails because no data is provided.
4. Working Cases: We’ve confirmed that:
   • These Google integration blocks do work correctly if we provide static data directly in the Content field (e.g., [["A", "B"], ["C", "D"]]).
   • The “Update Google Sheet” block can successfully use variables set by other block types (like a simple string from an AI block) to update a single cell’s content.

Conclusion:
The issue seems specific to passing complex data structures (arrays/objects containing arrays) generated as output from a “Run Function” block into the main Data/Content field of the Google integration blocks. The variable seems to resolve as empty within the context of that specific input field.

Question:
Is this a known limitation? Is there a different syntax or method required to correctly pass an array-of-arrays generated by a “Run Function” block into the “Create/Update Google Sheet” or “Create Google Document” blocks?

[Alex_MindStudio]

Hi @spawnupe, thanks for the detailed post and sorry to hear your agent isn’t working as expected.

Here are a few details that would help us better understand what’s going on:

• Walkthrough of the automations and structure of your agent
• Screenshots or a screen recording of the agent and the debugger

In the meantime, a couple of things you can try:

1. If your function returns an array of objects, try using {{json yourVariable}} in the Spreadsheet Content field
2. If you’re pulling nested values, {{get yourVariable “$.0.yourKey”}} can help extract them

Also, here’s a link to the Help Doc on variables: Variables | MindStudio Docs

Please share more details so we can take a look!

[spawnupe]

Agent Walkthrough: Keyword Research & Automated Sheet Creation (Attempted)

Overall Goal:
This workflow was intended to read the next ‘Pending’ keyword from a Google Sheet, use an AI model to find relevant questions asked by veterans on Reddit, parse and format those questions into a structured array (including a ‘Status’ column), create a new Google Sheet automatically populated with this data, and finally update the status of the keyword in the original Google Sheet.

Automation Structure & Block Configuration (During Failed Attempt - Run ID …27ce):

1. Start Block:

   • Purpose: Initiates the workflow.
   • Output: Sets standard variables like {{currentDate}}.

2. Fetch Google Sheet Block:

   • Purpose: Reads data from the tracking sheet.
   • Configuration: Sheet ID: 1GRSIeM1izLvlxaWm5eW1g6LHiWLh-_nTPP7tafie1a8, Range: A1:I1000, Export Format: JSON.
   • Output Variable: sheetData

3. Run Function Block (FuncfindPendingKeywordFromSheet):

   • Purpose: Finds the first row with “Pending” status, extracts Keyword and Row Index.
   • Code: (Provided in previous user message - uses ai.vars.sheetData, sets ai.vars.current_keyword, ai.vars.current_row_index).
   • Output: Sets workflow variables current_keyword and current_row_index.

4. Logic Block:

   • Purpose: Branches based on whether current_keyword was found.
   • Conditions: Proceeds if current_keyword has a value, otherwise ends.

5. Generate Text block (Background Message - Get Reddit Questions):

   • Purpose: Searches Reddit forums via Sonar Large Online for questions about {{current_keyword}}.
   • Prompt: Used the strict prompt asking for verbatim questions (the one starting “You are an expert research assistant specializing in veterans’ issues…”).
   • Output Variable: reddit_questions_ai. (Log confirmed this block ran successfully and returned text containing questions).

6. Run Function Block (formatQuestionsForSheet):

   • Purpose: Parses the raw text from reddit_questions_ai, cleans it, and formats it into a 2D array ([[HeaderRow], [DataRow1], ...]) with columns “Keyword”, “Question”, “Status” (initialized to “Pending”).
   • Code: Used v2.1 (as provided in my previous message), which accessed ai.vars.current_keyword and ai.vars.reddit_questions_ai.
   • Output: The function explicitly returned an object: return { sheetData: outputArray };. Logs confirmed outputArray contained the correctly structured data before returning. The output of this block is implicitly available as {{formatQuestionsForSheet}}.

7. Create Google Sheet Block:

   • Purpose: To create a new spreadsheet and populate it with the formatted questions array.
   • Configuration:
     • Spreadsheet Name: Questions for {{current_keyword}} - {{currentDate}}
     • Sheet/Tab Name: Questions
     • Header Row Option: Disabled/Unchecked.
     • Data / Content / Values: Configured using the get helper to access the array within the object returned by the previous step: {{get formatQuestionsForSheet "$.sheetData"}}
   • Output Variable: new_sheet_url
   • Observed Result (Failure Point): This block successfully created the Google Sheet file with the correct name and generated the new_sheet_url. However, the block’s internal log recorded Content: "" for its input data, indicating it failed to resolve/retrieve the array using the get helper on the function’s output variable. Consequently, the created Google Sheet was empty.

8. Update Google Sheet Block (Original Sheet Status):

   • Purpose: Mark the processed row as “Done” in the original tracking sheet.
   • Placement: After the “Create Google Sheet” block.
   • Configuration: Operation: Update by Range, Sheet ID: 1GRSIeM1izLvlxaWm5eW1g6LHiWLh-_nTPP7tafie1a8, Range: Sheet1!C{{current_row_index}}, Content: Done.
   • Observed Result: This block executed successfully.

9. End Session Block:

   • Purpose: Terminates the workflow.
   • Configuration: Included email notifications.

Problem Summary for Support:
The core issue observed is that the “Create Google Sheet” block (and previously tested “Update Google Sheet” and “Create Google Document” blocks) consistently fails to correctly read or resolve array/object data provided via a variable when that variable is the output of a preceding “Run Function” block. This happens whether the variable is referenced directly ({{functionBlock}}) or using the {{get}} helper ({{get functionBlock "$.key"}}). The block logs indicate it receives an empty string (Content: "") for its data input in this specific scenario, even when logs from the function block confirm the data was correctly generated and returned in the expected structure just before the integration block ran.

[spawnupe]

Run Block to the Logic Block

Logic block

You are an expert prompt( generate text)

Run block to format Questions

Create google sheet( which worked perfectly) BUT the Conent was “”

Then it updated the original google sheet where it fetched the keyword

a

[spawnupe]

This was the code that I used. We also followed the recommendations of json variable and the pulled nested value recommendations… same issue. Here is the code we wrote for the array. // Function: formatQuestionsForSheet_v2.1
// Purpose: Parses AI-generated question text and formats it for Google Sheet creation,
// including a ‘Status’ column initialized to “Pending”.
// RETURNS AN OBJECT { sheetData: […] } for compatibility with blocks.
// Inputs: current_keyword, reddit_questions_ai (via ai.vars)
// Output: Returns an object { sheetData: outputArray }

async function formatQuestionsForSheet_v2_1() {
console.log(“— formatQuestionsForSheet_v2.1 START —”);
const keyword = ai.vars.current_keyword || “Unknown Keyword”;
// *** Ensure ‘reddit_questions_ai’ is the correct variable name from the AI step ***
const questionsText = ai.vars.reddit_questions_ai || “”;

console.log("Input Keyword:", keyword);
console.log("Input Questions Text (start):", questionsText.substring(0, 200));

const outputArray = [];
// Add Header Row
outputArray.push(["Keyword", "Question", "Status"]);

let questionsList = [];
if (questionsText && typeof questionsText === 'string') {
    // Attempt to parse the questions list
    questionsList = questionsText.split('\n')
        .map(line => line.trim()) // Trim whitespace
        .filter(line => line.length > 0) // Remove empty lines
        .map(line => {
            // Remove common list markers and markdown formatting
            let cleaned = line.replace(/^#+\s*\d*\.?\s*\*{0,2}/, '') // Remove leading ### 1. ** etc.
                             .replace(/^[\s*•-]*\d*[\.\)]*\s*/, '') // Remove leading bullets, numbers, spaces
                             .replace(/\*{0,2}$/, '') // Remove trailing **
                             .replace(/<sup class=.*<\/sup>/, '') // Remove citations
                             .trim();
            // Remove surrounding quotes if AI added them
            if (cleaned.startsWith('"') && cleaned.endsWith('"')) {
                cleaned = cleaned.substring(1, cleaned.length - 1).trim();
            }
            return cleaned;
        })
         // Filter out common intro/outro lines from the AI - adjust if needed
        .filter(line => line.length > 0 &&
                       !line.toLowerCase().includes("top questions on") &&
                       !line.toLowerCase().includes("distinct questions that veterans ask") &&
                       !line.toLowerCase().includes("here are the top") &&
                       !line.toLowerCase().includes("reflect the common concerns") );

} else {
     console.log("Questions text is empty or not a string.");
}


if (questionsList.length > 0) {
    console.log(`Parsed ${questionsList.length} potentially valid question lines.`);
    // Add the first question row with the keyword and status
    outputArray.push([keyword, questionsList[0], "Pending"]);
    console.log(`Added Row 1: ${keyword}, ${questionsList[0]}, Pending`);

    // Add subsequent question rows (keyword column is empty based on original thought, changed per perplexity analysis)
     for (let i = 1; i < questionsList.length; i++) { // Start loop from 1 for subsequent questions
         // Perplexity suggested adding keyword to every row, let's adopt that here for the support ticket
         outputArray.push([keyword, questionsList[i], "Pending"]);
         console.log(`Added Row ${i + 1}: ${keyword}, ${questionsList[i]}, Pending`);
     }
    // If Keyword only on first row was intended:
    // for (let i = 1; i < questionsList.length; i++) {
    //     outputArray.push(["", questionsList[i], "Pending"]);
    //     console.log(`Added Row ${i + 1}: "", ${questionsList[i]}, Pending`);
    // }
} else {
    console.log("No valid question lines found after parsing.");
     // Add a row indicating no questions found, still include keyword context
     outputArray.push([keyword, "No specific questions extracted from AI output.", ""]);
}

console.log("Formatted Output Array (first few rows):", JSON.stringify(outputArray.slice(0, 5)));
console.log("--- formatQuestionsForSheet_v2.1 END ---");

// *** Return an OBJECT containing the array ***
return {
    sheetData: outputArray
};

}

// Call the function
await formatQuestionsForSheet_v2_1(); // Ensure function name matches if you changed it

[Alex_MindStudio]

Hi @spawnupe, thank you for all these details!

Could you also share a few screenshots of the Automations canvas, showing how the Run Function and Create Google Sheets blocks are configured?

In the meantime, I put together a quick Agent example that searches Google, trims the results, and adds them to a new Google Sheet. Feel free to remix it here:
https://app.mindstudio.ai/agents/custom-function-to-google-sheets-cb3cab3c/remix

[spawnupe]

So…. I am doing more research on the run workflow and map array capabilities. I am wondering if I should have done this first. Can anyone explain what the map array process ?