Sending Varibale value from Power Automate Desktop Loop to Power Automate Cloud

(0) Share

Report

Posted on by Harpreet_Tohra

I am new to Power Automate Desktop and Power Automate. Could anyone help me to solve the issues.

I am extracting a data table from website using Power Automate Desktop. Table has rows and columns properly.

The extracted data is being stored in DataTable variable in the form of Data Table.

Now the thing is, I want to send this DataTable to Power Automate Cloud and use the values by particular row and column and fill that data into a SharePoint List.

I am able to extract data but I am not able to send it as a data table to cloud flow. I guess it sends the data in array or string form and I am not sure about that.

If I am using loop to send the data to cloud it is passing only last value from loop.

As you can see in the above SS loop is running but it is sending the NetRate, Unit_Type and Date at the end the loop to cloud flow, But I want it should send the variable values after each iteration to Cloud flow , then go to the next iteration.

OR If I could fill SharePoint directly from Automate Desktop that could also be beneficial

Could anyone help me over this.

Thanks in advance

Categories:

Power Automate Desktop

I have the same question (0)

All responses (22)

Answers (2)

Sort by

MichaelAnnis 5,727 Moderator on at

Like (2)

Report
Copy link

Link copied!

In PAD, we are able to create environmental variables use "Set Environment variable", but I don't know if this translates to cloud; seems like it should.

I don't deal with cloud, so I am sure there is a better solution, but if environment variables don't work, this might be a workaround:

Write your data table to Excel (to SharePointe, if you wish)
Have Power Automate Cloud pick up the data table from Excel

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

Like (4)

Report
Copy link

Link copied!

Hello @Harpreet_Tohra

You can connect to your SharePoint List and run SQL query from PAD to INSERT your data directly into SharePoint List.

[Connection String]
Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes; DATABASE=https://TENANT_NAME.sharepoint.com/sites/SITE_NAME/;LIST=LIST_ID_GUID;

[SQL Statement]
INSERT INTO list (Title, FirstName, LastName, PhoneNumber) VALUES ('Item01', 'Alice', 'White', '0123')

As a result, you see those new items are added to your SharePoint List.

Thank you.

Was this reply helpful? Yes No
Harpreet_Tohra 71 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous I want to try this. Could you please tell me more about this. I searched on Google and article I found is asking to download some ODBC and i guess that is paid. could you please help me over this. To connect Power Automate Desktop with SharePoint using this ODBC, If it is free to use.
I shall be thankful to you.

Was this reply helpful? Yes No
vamsi_varanasi 152 on at

Like (0)

Report
Copy link

Link copied!

@Harpreet_Tohra and @Anonymous -

Which Driver do we need to select in order to connect cloud share point list from PAD?

I am trying to create the connection string here to connect sharepoint.

please advise.

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hello @Harpreet_Tohra and @vamsi_varanasi

I have read below article by @MiyakeMito posted in Qiita.com.
It is written in Japanese, however, contains very useful information regarding this scenario.
Power Automate Desktop で SharePoint Onlineリストのデータをダイレクトに取得や操作する方法 - Qiita

All you need is "Microsoft Access Database Engine 2010 Redistributable" which you can download from here:
Download Microsoft Access Database Engine 2010 Redistributable

I suppose I am using "Microsoft Office 12.0 Access Database Engine OLE DB Provider" as provider, but I don't know how to configure the connection further in this UI panel...

So, I enter the connection string directly to the PAD action "Open SQL connection" as below:

[Connection String]
Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes; DATABASE=https://TENANT_NAME.sharepoint.com/sites/SITE_NAME/;LIST=LIST_ID_GUID;

Thank you.

Was this reply helpful? Yes No
Harpreet_Tohra 71 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous Thanks for the solution. It worked for me. Could you please tell me about connecting a created View for SharePoint List. I was trying to connect but it is unable to connect.
I am using this.
Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes; DATABASE=https://Tenat_Name.sharepoint.com/sites/Site_Name/Lists/List_Name/;VIEW=GUID;

Could you help me over this.

Was this reply helpful? Yes No
vamsi_varanasi 152 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous @Harpreet_Tohra ,

Thanks for your response.

We have installed Microsoft Access Database Engine 2016 Redistributable as 2010 support has been discontinued.

I am getting the below error from SQL query execution , do you think is any configuration change required ?

Thanks,

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hello @Harpreet_Tohra

Per my experiment, following connection string worked for me:

[Connection String]
Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://TENANT_NAME.sharepoint.com/sites/SITE_NAME/;LIST=LIST_ID_GUID;VIEW=VIEW_ID_GUID;

[SQL Query]
SELECT * FROM [VIEW_NAME]

Please try it.
Thank you.

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

Like (1)

Report
Copy link

Link copied!

Hello @vamsi_varanasi

I suppose your problem is "Parentheses" used in the column name "Actual(UTC)" in your example.

I am able to insert a new item to SharePoint List using following SQL statement:

INSERT INTO list (Title, FirstName, LastName, PhoneNumber, [DateTime (UTC)]) VALUES ('Item04', 'Daniel', 'Orange', '4567', '2021-10-02 16:16')

The point is to enclose the column name with a bracket symbol "[" and "]".

Thank you.

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

Like (0)

Report
Copy link

Link copied!

Hello @vamsi_varanasi

Please change the provider name as below:

Provider=Microsoft.ACE.OLEDB.16.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://TENANT_NAME.sharepoint.com/sites/SITE_NAME/;LIST=LIST_ID_GUID;

Thank you.

Was this reply helpful? Yes No