I have a Microsoft Form that has a few questions on it, which also captures the email address of the submitter. I have an Excel file set up to capture the Form responses, and the responses come through just fine. These responses are duplicated on a separate worksheet that also contains other data relative to the responder, their email, and their responses.
As an example, the responses comes in on the sheet formResponses. These are copied over to a second worksheet called data, and there are extra columns after the responses, which use a VLOOKUP function to query a different sheet that contains employee info (ID, manager, manager email, location, etc.). That relative info is populated on data on the same row as the submitter's responses, and that all works just fine. There's no problem getting the info to show up correctly.
The problem I've been running into is trying to retrieve some of that relative data and sending it back to a Power Automate workflow, to use in a condition step. Here's the workflow I'm using:
-[Forms] When a new response is submitted
-Apply to each (List of response notifications)
-Get response details (List of response notifications Response Id)
-Run script
-Condition (body/result > is equal to > OK
-If True...
-If False...
Here's the Excel script I'm using:
function main(workbook: ExcelScript.Workbook, email: string): string {
var data = workbook.getWorksheet('data');
var row = data.getRange("F:F").find(email, {completeMatch: true}).getRowIndex();
var match = data.getRangeByIndexes(row, 10, 1, 1).getValue().toString();
return match;
}
This workflow is passing the responder's email to the script, which looks at the data sheet, finds the row in which the email is listed, and pulls the value from the 11th column (index 10), returning it to the workflow to use in a true/false condition. Each time the form is filled out, I receive this error:
Action 'Run_script' failed: Bad Request Runtime error: Line 5: Cannot read properties of undefined (reading 'getRowIndex')
I've toyed with this a bit, and for my testing, I've used a field input on the Form as an email input, inputting a different fake email address each time, just to simulate unique submitters filling out the Form. So the workflow passes this fake email to the script, and the script is looking at the corresponding column to match it up. If I fill out the Form and use a fake email that's already on the sheet, there's no error at all. The script and workflow go through perfectly, condition rule is triggered correctly, and the corresponding action fires without issue. With that, my assumption is that the workflow and script are being triggered too quickly, before the responses are given time to populate on the Excel sheet.
To try and correct this, I've added a Delay step in the workflow before the 'Apply to each' step, trying increments up to 2 minutes, and yet this issue persists. I also tried inserting workbook.refreshAllDataConnections(); at the beginning of the script, in an attempt to force a refresh of the data (Ã la SpreadsheetApp.flush() from Google Apps Script), but that also did nothing to resolve the error.
I should also say that this workbook, where the responses are coming in, will not be open all the time while this Form is being used. This file will just exist out there on my drive while responses are being submitted through the Form.
Is there a way to force that refresh or otherwise wait until the Form responses are populated on the file so the script doesn't fail?
Thanks in advance, I'm just wading into the Power Automate / Excel script world after having spent many years in the Google Apps Script realm, trying to translate tools from one environment to the other.
