Absolute Beginner Seeking Guidance/Validation of Concept

[QuakeScott]

Hello -

First time user. Wondering if this is

• Correct Location to address my questions.
• Curious if my general logic is sound.
• Unsure how to start. (For example: with "Goal" and "Approximate Steps" as listed below is the correct method to make a 'Flow?' If so, I am unclear on some 'Flow' elements to use as one tutorial said there are 300 options. Elements like "Open email" are obvious to me but I don't know what element to select for "read a spreadsheet field's date and determine if less than 90 days away, then send an email." (That is a paraphrase, not the exact logic. See below for specifics.) 

((The webpage just failed to send after I was complete and I lost message. Back to a previous saved copy. (Note to self: always, always save before submitting!) Point is that I am frustrated and the "approximate steps" below may be rough. However, I am just reaching out to see if I am on the right path, and maybe what flow events to use.

I haven't programmed before, and want to get some familiarity with automating. So, any guidance or suggestions are appreciated, however, I am not looking for someone to do it for me. Thank you. Scott)) 

Goal:

From a spreadsheet of customers, check each site's expiration date on their service contract, and send the relevant (form letter) email to that business' contacts depending on 90-days, 60-days, and 30-days prior to that expiration date. Then, color code a selected field (red, orange, yellow, green) based on approaching expiration date.

Approximate steps:

• run daily
• open spreadsheet
• read "expiration date" field
• calculate "expiration date" as
  • <30 days from 'today'
  • 31-60 days from 'today'
  • 61-90 days from 'today'
  • >/= 91 days from 'today'
• if "expiration date" >/= 91 days from today
  • set specified field to green
  • close spreadsheet
  • end
• if "expiration date" </=90 days
• copy site contact's email address(es)
• open email 
• select corresponding email template (for 90-, 60-, 30-day)
• merge addresses
• send
• close email
• set specified field in spreadsheet (yellow, orange, red)  
• close spreadsheet 
• end

[Rhiassuring · Accepted solution]

Hello! Welcome!

• Correct Location to address my questions? Yep!
• Curious if my general logic is sound. It is sound. Some of your terminology is going to change a bit, but you'll learn as you go. I'll translate a bit for you.
• Unsure how to start. The .. beginning 😄  I am also not a programmer. I could not code my way out of a box in ANY language. A lot of Power Automate is Googling and Testing.

First things first. The terminology. The things you are saying sounds like you intend to use Power Automate Desktop (RPA) instead of Power Automate (cloud automation). 

A) Is this your intention?

B) Do you understand the difference between these 2 very different tools? Let me know if not, that's an important one!

For your steps, depending on what your scenario is, if you're using cloud, it'll translate to this:

• The trigger will be a Recurrence flow, set for "Once Daily"
• Get data from Excel by using the "List rows in table" action.
• Use a "Compose" action to determine the distance of days between today and the Expiration Date.
• Classify "expiration date" as... (few ways to do this..)
  • <30 days from 'today'
  • 31-60 days from 'today'
  • 61-90 days from 'today'
  • >/= 91 days from 'today'
• Use a condition: if "expiration date" >/= 91 days from today
  • IF YES: set specified field to green (I would recommend instead using conditional formatting in  your spreadsheet to automatically turn this to green based on a value. If you need to do this manually, then you will need to use either an Office Script or RPA, and it's much more complicated.) 
• if "expiration date" </=90 days...
• Send a New Email and add the Site Contacts from the current item into the "To" field.
• Automatically apply the appropriate Subject Title and Body for (for 90-, 60-, 30-day) templates. (This could be done by storing them elsewhere. It depends how complicated they are. Or, you could have all 3 built out and just use a "Switch")
• Set specified field in spreadsheet (yellow, orange, red)  (Again, I'd do this with conditional formatting for simplification.) 

Let me know if you're with me so far, and we can get into the step-by-steps where you get stuck.

[QuakeScott]

I didn't mean to break up my response. Anyway - 

• Thank you for the translations, and the terminology of needed actions.
• Googling and testing. Got it.
• To restate - I do not know of importance of RPA v. cloud and am happy to begin where you suggest. I will proceed then with cloud focus.
• Will proceed with conditional formatting in Excel for the coloration.
• Waiting on my IT for the SharePoint access to files (I am a new hire) to address the email template situation.

Looking forward to your future assistance. I need to research how to create / save this process and then begin implementing your suggestions. Thank you, again, Rhi.

[Rhiassuring · Accepted solution]

For RPA (robotic process automation - Power Automate Desktop) vs cloud:

Power Automate Desktop is something you download and install and create bots on. Bots take control of your computer and keyboard and perform tasks as if they are the human themselves. You can't use your computer when the bot is running, as you will see them take each step. If you need it to run on its own, you'd need to set up a virtual machine and install it there. If you want to run it unattended (ie, not going there and pressing "start") then you have to purchase bots from Microsoft.

Power Automate cloud is what you see when you go to https://make.powerautomate.com/ - the tools are all right there in your browser, no need for a VM or bots. 

You can call a Power Automate Desktop bot from a Cloud flow, though! 

Does that help? Or make it worse? haha

[QuakeScott]

It helps IF I UNDERSTAND IT CORRECTLY - haha, but yes, it helps.

I will not be using bots, so PA cloud is what I expect to be using. Given that, it sounds like I will be creating cloud flows, is that correct terminology? 

[Rhiassuring]

That's right!