cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
conAK
New Member

Tracking emails with Excel

Hi everyone,

 

I'm a complete noob to MSPA. I've found a few flows here in this community that track emails in Excel, but they all seem to require a specific format (i.e. specific columns) for the Excel table and don't do exactly what i'm looking for.

 

I have an existing Excel table with my business contacts (like a mini-CRM). What I'd love for MSPA to do is to check all incoming Outlook emails against a column in my existing Excel file (or potentially several different sheets in the same Excel file). If the email address is already contained in the list / column, then put today's date in another column (overwrite the date that's already in there). If the email address is not containd, do nothing.

 

What this is supposed to do is to update my table of "when did which contract write me last".

 

Any clue how this might be feasible?

 

Thanks a ton for any suggesstion,

Alex

7 REPLIES 7
ChristianAbata
Super User II
Super User II

hi @conAK  I reply want you need and this is the solution.

 

from.PNG

 

Please as a note: when you are going to compare you need to compare the exact value for example in you excel list have christianabata@hotmail.com and in from needs to be christianabata@hotmail.com if the from has Capital letters the answer of the compare will be false.



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA
RTW
Advocate II
Advocate II

I recently discovered that you can query Exchange like any other data source and pull email info into excel that way. I use it to track requests that go into a folder based on the sender. I think that this, paired with a pivot chart might work for you.

v-alzhan-msft
Community Support
Community Support

Hi @conAK ,

 

Please take a try with @ChristianAbata 's suggestion and let me know if the issue could be solved.

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Christian,

 

thanks a ton, I just can't get it to work and have a ton of questions...

 

- Does your screenshot show the full flow? Shouldn't it start with "receive an email"?

- I don't know anything about Json and parsing. Plus I have not found that option in my MSPA menu - where can I find it. And when I just put a condition after "receive an email", I don't get the option of comparing the sender to a value in an Excel sheet. I only get options that relate to the email itself, as I understand it.

 

Seems like I'm lacking some deeper understanding here - is there anything I should read before to better understand your suggestion?

 

Thanks a lot,

Alex

hi @conAK  my screanshot just show how you cloud get the values in your excel table and then compare it with the email that was received.

So the flow starts with. When a new email arrives, thats where I get the parameter From.

Then to use Json just you need to add an acction then write Json in search so to use it the first part is pass the Array list an the a schema, that schema is the output of get rows.

Here and example.. first pass the output from Excel then clic on generate template and paste the output from excel

json.png



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA

Hey Christian,

 

thanks a lot for all your explanations, but this seems way above my head. If there isn't an easier solution, I guess I'll just stick to doing it manually. It would take a lot of back and forth to truly understand this and get it set up and I wouldn't wanna take up too much of your doing.

 

All the best,

Alex

 

Don't get discouraged. Stuff like JSON and parsing and some of the expressions were fairly intimidating but with a bit of practice you can wrap your head around it. I studied English and Theater and while I wouldn't call myself an expert at all, in a few months I know it well enough to do some interesting things in Power Automate.

 

JSON is Java Script Object Notation - its just a way of communicating information.

 

Let's say I had a table called Party in an Excel file, it would look something like this.

 

CharacterSpeciesClasses
Merle HighchurchDwarfCleric
Magnus BurnsidesHumanFighter, Rouge
TaakoHigh ElfWizard
Brad BradsonOrcBard, Human Resources

 

JSON would be one way you could transmit this application from one to another. The table would look something like this.

 

 

[ 
  { 
    "Character":"Merle Highchurch",
    "Species":"Dwarf",
    "Classes":[ 
      "Cleric"
    ]
  },
  { 
    "Character":"Magnus Burnsides",
    "Species":"Human",
    "Classes":[ 
      "Fighter",
      "Rouge"
    ]
  },
  { 
    "Character":"Taako",
    "Species":"High Elf",
    "Classes":[ 
      "Wizard"
    ]
  },
  { 
    "Character":"Brad Bradson",
    "Species":"Orc",
    "Classes":[ 
      "Bard",
      "Human Resources"
    ]
  }
]

 

So, it looks fairly complex so lets break it down. This is a JSON array - basically tabular data expressed in JSON. An array is closed off by square brackets - [] and is made up of objects which are closed in curly brackets {}. Each objects have different properties. Going back to the table - the array is the whole table. An object is a single row on the table, and a property is a column on the table.

 

A simple array would just be a list of numbers - [ 1 , 2 , 3 ]. Lets say you needed to track multiple properties for the same object, like a store. You might design the objects like this. {"Item": 1, "Name": "Widget", "Cost": 20} - string together multiple of those objects and you got an array.

 

Arrays can be nested in each other as well. As you can see, a character can have more than one class, so instead of just storing it as "Class 1" and "Class 2" it made more sense to create a small array in the larger one. This way I don't need to change the schema when a character add's a third or fourth class.

 

Now, a schema is like a map. Its a set of instructions so that the program (or flow) you are working on will know what data to expect. The schema for the data above would look like this.

 

 

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Character": {
                "type": "string"
            },
            "Species": {
                "type": "string"
            },
            "Classes": {
                "type": "array",
                "items": {
                    "type": "string"
                }
            }
        },
        "required": [
            "Character",
            "Species",
            "Classes"
        ]
    }
}

 

This tells flow that I am going to send it an array. The array will have multiple objects with 3 properties. Two of them are strings, one of them is an array of strings.

 

What the Parse JSON action does is allow you to take a block of JSON information and use it in later sections. This allows you to take the information from the "List Rows" and access that data as properties down the road. You would be able to down the road on your flow, select a property and perform an action on it.

 

A lot of information and I hope its helpful! Don't get discouraged - if you don't have a background in this sort of thing it can be kind of hard to get started but when it works it feels like magic.

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (49,326)