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

[Harpreet_Tohra]

Hi 

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

[MichaelAnnis]

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

[Anonymous]

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.

[Harpreet_Tohra]

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.

[vamsi_varanasi]

@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. 

[Anonymous · Accepted solution]

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.

[Harpreet_Tohra]

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.

[vamsi_varanasi]

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, 

[Anonymous · Accepted solution]

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.

[Anonymous]

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.

[Anonymous]

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.

[vamsi_varanasi]

Hi @Anonymous ,

Yeah, Thanks for your response again. this is the problem. I rectified it. 

The error I am facing after resolving the syntax error : I think some thing related to access DB and share point list sync. any clue ? 

Thanks,

[Anonymous]

Hello @vamsi_varanasi 

It looks like column name reference issue.

What is the exact column name of your SharePoint list?

Also could you show us the SQL query you want to run?

Thank you.

[vamsi_varanasi]

@Anonymous , These are the below list and the columns 

This is the SQL query from PAD - 

in both SQL query cases, I faced the same issue. 

Thanks 

[Anonymous]

Hello @vamsi_varanasi 

So, [Fun_Non-Fun_Status] is a list name, right?

Are you sure the list ID specified in the connection string is matching the list name specified in the INSERT statement of SQL?

Per my experiment, following SQL query was worked just fine for me:

INSERT INTO [Fun_Non-Fun_Status] (Title, TestCaseName, Source, Expected, Actual_UTC, Status) VALUES ('Item01', 'TEST123', 'TEST456', 'TEST123', 'TEST343', 'TEST223')

Thank you.

[vamsi_varanasi]

Hi @Anonymous ,

yes, this is list name. I given the list id in the connection string as below

I removed the %7B in front of ID and %7D rear of the ID and given the rest of the ID 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};

I given { LIST ID GUID} to the list id , is this correct ? 

Thanks

[Anonymous]

Hello @vamsi_varanasi 

Your connection string seems looks fine.

It should work with or without curly braces.

Try to use different SharePoint list, if you did not try yet?

If you're still having trouble with SQL or SharePoint lists, since this community is for PAD, it's a good idea to find other places to ask your question to experts in that area. 🙂

Thanks

[Harpreet_Tohra]

Hi @vamsi_varanasi @Anonymous 

I was trying to connect SharePoint List of another tenant. But I am getting the same error as Vamsi getting i.e. List object not found.

@vamsi_varanasi  Have you solved the issue. Could anyone help me to solve this.

@Anonymous All things are working well in one tenant but not working for list in Another SharePoint tenant.

[vamsi_varanasi]

@Harpreet_Tohra - No, not resolved yet. but what I have observed , it is completely dependent on Share point version, driver in PAD.

I am able to solve in one system and unable to access in other system. 

[Harpreet_Tohra]

Hi @Anonymous I was trying to get attachments from SharePoint list Using Open SQL connection But As we know it retrieves data in the from of Datatable. So it is showing true instead of attachment. Is there any way to retrieve attachment file from SharePoint using open SQL connection  action in Power Automate Desktop.