Power Automate SharePoint list to Excel

I am using power automate to regularly update a SharePoint list based on an excel file that has a SQL query connection which refreshes weekly. I have set up my flow in a way that existing items get updated or new items get added if they are not already present in the list. I want to flag [maybe use conditional formatting?] the values in SharePoint list that have been updated due to the automate import from Excel. Has anyone tried this before and how does that work? I appreciate your time!

------------------------------
Disha Jariwala
Medicare Star Business Technologist
------------------------------

Hi @Disha Jariwala,

There are a number of ways you can approach this, but all will be data driven. Because you are successfully adding/modifying records in the SharePoint list, I assume you are likely tracking a "last modified date" or something similar in order to detect changes. You could use this date to do your flagging, or you could create a custom date field. I would not advise using a non-date field to indicate updates because you would then have to re-modify the field once a record falls out of the date range you want to highlight.

Once you've selected a field, and assuming you're using SharePoint Online, you can go to your SharePoint list, select the date column you want to use for formatting, click the drop-down in the column header, ​select Column Settings and then Format This Column. Make whatever setting updates you'd like - you can use an icon, color or both to highlight recently updated records.

Below are a set of screenshots for this that I recently setup for a client in the banking industry:

1. Click the date column header and choose Column Settings ? Format this column.



2. Choose what field to apply formatting to [or format the whole row]. And select the "Format dates" option. In your case, I suggest you format the entire row since your users may be more focused on columns other than the "last modified date" column.


3. Under the "Format dates" card, click Edit styles. Add any styles you like based on the date. You can use relative dates here. Note that I set mine based on before today, today, and after today - for red/yellow/green highlights. I also added an icon to the red highlighting. When done, click the Save button.



------------------------------
Geoff Ables
Managing Partner
C5 Insight
Charlotte NC
704-281-8067
------------------------------

Original Message:
Sent: Jun 13, 2020 10:27 PM
From: Disha Jariwala
Subject: Power Automate Excel to SharePoint List

I am using power automate to regularly update a SharePoint list based on an excel file that has a SQL query connection which refreshes weekly. I have set up my flow in a way that existing items get updated or new items get added if they are not already present in the list. I want to flag [maybe use conditional formatting?] the values in SharePoint list that have been updated due to the automate import from Excel. Has anyone tried this before and how does that work? I appreciate your time!

------------------------------
Disha Jariwala
Medicare Star Business Technologist
------------------------------

Hi Geoff! Thank you so much for the detailed response. I was able to use this technique! Appreciate your help!

------------------------------
Disha Jariwala
Business Technologist
------------------------------

Original Message:
Sent: Jun 15, 2020 09:42 AM
From: Geoff Ables
Subject: Power Automate Excel to SharePoint List

Hi @Disha Jariwala,

There are a number of ways you can approach this, but all will be data driven. Because you are successfully adding/modifying records in the SharePoint list, I assume you are likely tracking a "last modified date" or something similar in order to detect changes. You could use this date to do your flagging, or you could create a custom date field. I would not advise using a non-date field to indicate updates because you would then have to re-modify the field once a record falls out of the date range you want to highlight.

Once you've selected a field, and assuming you're using SharePoint Online, you can go to your SharePoint list, select the date column you want to use for formatting, click the drop-down in the column header, ​select Column Settings and then Format This Column. Make whatever setting updates you'd like - you can use an icon, color or both to highlight recently updated records.

Below are a set of screenshots for this that I recently setup for a client in the banking industry:

1. Click the date column header and choose Column Settings ? Format this column.

2. Choose what field to apply formatting to [or format the whole row]. And select the "Format dates" option. In your case, I suggest you format the entire row since your users may be more focused on columns other than the "last modified date" column.

3. Under the "Format dates" card, click Edit styles. Add any styles you like based on the date. You can use relative dates here. Note that I set mine based on before today, today, and after today - for red/yellow/green highlights. I also added an icon to the red highlighting. When done, click the Save button.

NOTE that these changes are saved as a part of the view. So be sure you are on the view you want the formatting to be applied to when you make the updates.

------------------------------
Geoff Ables
Managing Partner
C5 Insight
Charlotte NC
704-281-8067

Original Message:
Sent: Jun 13, 2020 10:27 PM
From: Disha Jariwala
Subject: Power Automate Excel to SharePoint List

I am using power automate to regularly update a SharePoint list based on an excel file that has a SQL query connection which refreshes weekly. I have set up my flow in a way that existing items get updated or new items get added if they are not already present in the list. I want to flag [maybe use conditional formatting?] the values in SharePoint list that have been updated due to the automate import from Excel. Has anyone tried this before and how does that work? I appreciate your time!

------------------------------
Disha Jariwala
Medicare Star Business Technologist
------------------------------

If your Power automate license includes the use of premium connectors then you can eliminate the Excel file from the process and talk directly the SQL server table and the sharepoint list. Don't even need Power Automate for that, actually.

------------------------------
Lutz
------------------------------

Original Message:
Sent: Jun 13, 2020 10:27 PM
From: Disha Jariwala
Subject: Power Automate Excel to SharePoint List

I am using power automate to regularly update a SharePoint list based on an excel file that has a SQL query connection which refreshes weekly. I have set up my flow in a way that existing items get updated or new items get added if they are not already present in the list. I want to flag [maybe use conditional formatting?] the values in SharePoint list that have been updated due to the automate import from Excel. Has anyone tried this before and how does that work? I appreciate your time!

------------------------------
Disha Jariwala
Medicare Star Business Technologist
------------------------------

Unfortunately it doesnot include premium connectors, however this is definitely something I can keep note of for furture purposes. Thank you so much!

------------------------------
Disha Jariwala
Medicare Star Business Technologist
------------------------------

Original Message:
Sent: Jun 16, 2020 10:40 AM
From: Lutz Bendlin
Subject: Power Automate Excel to SharePoint List

If your Power automate license includes the use of premium connectors then you can eliminate the Excel file from the process and talk directly the SQL server table and the sharepoint list. Don't even need Power Automate for that, actually.

------------------------------
Lutz

Original Message:
Sent: Jun 13, 2020 10:27 PM
From: Disha Jariwala
Subject: Power Automate Excel to SharePoint List

I am using power automate to regularly update a SharePoint list based on an excel file that has a SQL query connection which refreshes weekly. I have set up my flow in a way that existing items get updated or new items get added if they are not already present in the list. I want to flag [maybe use conditional formatting?] the values in SharePoint list that have been updated due to the automate import from Excel. Has anyone tried this before and how does that work? I appreciate your time!

------------------------------
Disha Jariwala
Medicare Star Business Technologist
------------------------------