Unanswered

Add row to table in file name @{variables('this sunday')}_workbook..xlsx

(0) Share

Report

Posted on by Community Power Pla...

I have two flows that I have tested independently. The first flow creates a new Excel workbook every Sunday at 00:30 local time. The second flow adds a new row to a table in a workbook that is statically named in the flow.

I want to change the second flow so that it only sends updates to the most recent workbook (created in flow 1).

This is what I have tried.

Once a week, at 1230 Sunday mornings this flow clones and renames a template workbook (image1). The string variable called "weekly workbook" uses the following expression:
convertFromUtc(utcNow(), 'Eastern Standard Time', 'yyyy_MM_dd')

Once a week, on Sunday mornings create a new workbook (image1)
Problem: When a new emails arrives, add a row to a table in a statically named workbook. (image 2) How do I get this flow to dynamically update the most recent workbook?

When a new emails arrives, add a row to a table in a statically named workbook. (image 2)
I have tried to name a string variable and place it in the file path of the last step above.
formatDateTime(subtractFromTime(utcNow(), dayOfWeek(utcNow()), 'Day'), 'yyyy_MM_dd')
concat(variables('this sunday'),variables('suffix'))

Do I need to use another kind of action? Can I not place a variable in the "File" field of "Add a new row to a table"? Help!?

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (0)

tom_riha 10,185 Most Valuable Professional on at

Like (0)

Report

Hello @Anonymous ,

the problem is with the file name, you can't reference an Excel file just by its name, the connector expects the file ID. Check this article or video by @Jcook to get the Excel id for the 'Add a row..' action

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thanks, @tom_riha I have seen the @Jcook web page but it doesn't make sense to me. The video shows a different process that what is typed out on the page.

The file ID should point to a specific file, correct? The file I want to point to changes by week.

Was this reply helpful? Yes No
tom_riha 10,185 Most Valuable Professional on at

Like (0)

Report

Hello @Anonymous ,

yes, it should point to a specific ID, what Josh describes in the article is how to get the ID from a file by its name. If you change the file name every week, then you can search for a different file name every week.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
The Sharepoint folder I am operating is shared by a group.

@tom_riha I performed the step of getting the json output that appears before your screenshot. @Jcook mentions that I am looking for an id value but there is no odata.id uri in the response. The body of the output contains some of the following information, but I am unsure if its helpful? Although two id values starting with "b!...." do appear in the response data, neither works in the next step.

{ "statusCode": 200, "headers": { "Pragma": "no-cache", "Transfer-Encoding": "chunked", "Vary": "Accept-Encoding", "X-SP-SERVERSTATE": "ReadOnly=0", "ODATA-VERSION": "4.0", "Content-Type": "application/json; odata.metadata=minimal; odata.streaming=true; IEEE754Compatible=false; charset=utf-8", }, "body": { "@odata.context": "https://foo.sharepoint.com/sites/BarTeam/_api/v2.0/$metadata#drive", "value": [ { "createdBy": { "user": { "displayName": "System Account" } },

Was this reply helpful? Yes No
tom_riha 10,185 Most Valuable Professional on at

Like (0)

Report

Hello @Anonymous ,

I just tried Josh's solution and got the expected result, didn't you miss one of the steps in your flows?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

I have tested this again--completely skipping the step of dynamically naming the file. I can extract the File Id, but I cannot seem to reference the correct table?

The .xlsx workbook contains several worksheets. Do I need to reference the worksheet before referencing the table?

Was this reply helpful? Yes No
tom_riha 10,185 Most Valuable Professional on at

Like (0)

Report

@Anonymous , I don't know if you can use Excel file with multiple sheets, I want to say that the Excel file can have only a single sheet, but I might be wrong on this.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
@tom_riha I can use the filter action and view any table on any sheet, but even on single table workbooks I cannot "add a row into a table" using the dynamically called file. When I view the action for "add a row to into a table" I am not seeing the items (column names) that appear in my statically called flow.

Peeking the code shows the following:
STATIC - working

{ "inputs": { "host": { "connectionName": "shared_excelonlinebusiness", "operationId": "AddRowV2", "apiId": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness" }, "parameters": { "source": "groups/e25dd93c-a1fb-45c2-a037-073c1fd38ca4", "drive": "b!XK0ayRFAlkePKk6Xna6UeIbDId1HNqtCqd8e6j5jZ0EOkKuFLisyQpDP9tERrc_L", "file": "01KXKWXYGZF25CXYKVZ5EYYUHQYNVG4WUX", "table": "TableName", "item/Source": "@triggerOutputs()?['body/from']", "item/iso8601_receipt_date": "@triggerOutputs()?['body/receivedDateTime']", "item/conversation_id": "@triggerOutputs()?['body/conversationId']" }, "authentication": "@parameters('$authentication')" }, "metadata": { "01KXKWXYHUTETN33YQQJGY7LDL435ZVTIB": "/General/Weekly Reports/Email Response Report (Automation)/current_week_automated_email_response_report.xlsx", "tableId": "TableName", "01KXKWXYGZF25CXYKVZ5EYYUHQYNVG4WUX": "/General/Weekly Reports/Email Response Report (Automation)/current_week_automated_email_response_report.xlsx" } }

DYNAMIC - Not working

{ "inputs": { "host": { "connectionName": "shared_excelonlinebusiness", "operationId": "AddRowV2", "apiId": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness" }, "parameters": { "source": "groups/e25dd93c-a1fb-45c2-a037-073c1fd38ca4", "drive": "b!XK0ayRFAlkePKk6Xna6UeIbDId1HNqtCqd8e6j5jZ0EOkKuFLisyQpDP9tERrc_L", "file": "@outputs('Get_file_properties')?['body/{Identifier}']", "table": "TableName", "item": 0 }, "authentication": "@parameters('$authentication')" } }

Was this reply helpful? Yes No
tom_riha 10,185 Most Valuable Professional on at

Like (0)

Report
@Anonymous , based on the dynamic content in the second example, it looks to me that you're still not following Josh's method to get the file id.

"file": "@outputs('Get_file_properties')?['body/{Identifier}']",

Josh doesn't use output from 'Get file properties' as the Excel file identifier.

Was this reply helpful? Yes No