Create an Effective Payment Reminder Message Sample for Life Sciences

Payment reminder message sample for Life Sciences

welcome everyone to Microsoft 365 virtual marathon thank you for attending and we hope you're having a great conference it's now been over 13 hours of non-stop sessions and we really hope you enjoy the rest of the conference as well I'd like to introduce our next session speaker Hansol Gamache Hansa will be presenting a session titled a payment reminder using power apps Excel and as your functions take it away non-stop yeah thank you okay so in this session let's talk about how simple the power apps are and also how we can easily integrate it with different services may be you may be new to power apps maybe you are expert in power-ups but at the end of the session let's try to learn something new so this next the sharepoint conference that we have in next year and these are the sponsors and this is little bit about myself so i am handsome ali khamenei from Sri Lanka I'm a Microsoft MVP in Nashua and also I am Microsoft certified trainer so in my leisure time I am blogging and I'm speaking and traveling all those stuff and if you want to discuss so if you want to ask any questions or maybe to discuss some interesting techy stuff you can reach me out from these the resources available in social media maybe on Twitter in my blog post or maybe in my you can check out my github repos as well most of the reports are on natural functions and Microsoft 365 resources like a mess graph and all so this is the there will be a raffle draw and you will be getting this type of exciting gifts so if you are eligible and yeah okay now let's move into the session okay so we will not be like to be more interactive in this session throughout this session but let's try not to be sleepy and try to learn something new on power-ups so power-ups is like collection of applications oh maybe you can say collection of services you can create your power apps using the Verity of connectors and data providers and the speciality it's available in powerup sees it allows you to develop applications rapidly and even a non-technical guy can work with power-ups can create a power-ups and you can release your MVP you're the first release into your users with these power apps you don't have to like worry about creating really deleting those type of crud operations even a non-technical guy can create a powerup and release it into they are users there are business users and also you can connect with your existing SharePoint hub and your Excel sheets whatever the data store available and office 365 resources like your onedrive your Excel files your plan your calendar your meetings you can connect with those stuff as well in power apps and also if you have on Prem data server maybe data are available in issuer you can connect with that too and this is like it's like hype to convert your manual processors to digital automated processes and even a non-technical kai guy can create that means that's like wow right and also you can even a user can blink their own applications without writing any single line of code so those for the non-technical guys so what about us for us for the developers we can programmatically interact with this data we can create custom connectors and we can integrate with the external data sources as well and you can run these powerups in browser as well as in your mobile phones and you can sign up for us so if you if you want to start with power apps if you don't you have to like sign up for a 30 days trial account or else you can go for the community plan and it allows individual it allows to use for a individual users so that means you can if you don't want to spend something on power-ups you just want to have a look you can go for the trial account and also you can go for the community plan that allows for individual yours so if you the power apps impress you if if it's going to put some energy and put some value into your business you can continue with the whatever the pricing options that suitable for you okay so I'm not going to into detail into what are the baby feeds and what are the disadvantages and those and door stuff so we'll directly move into an interesting case study so in this case study we are going to develop a power app and it's going to add some data into the Excel so the ataxic that excel file is available in your onedrive in under your office 365 account so so that means it's a payment reminder application so from your maybe that powerup can be run in the browser and also it can run in a mobile device in your mobile phone or maybe in a tab so you can enter whatever the expenses for a month maybe for a year then that will be inserted that will be added into the excel file then in the background you can run a su a function and let's say every 28th of the month you want to get a payment reminder so what is the total B total amount of bills you want to pay for the entire month so you can get to reminder you can get a reminder email to your gmail account why you see this as you a function f so that's what the case study we are going to be lit today so if you are not a developer you can start with the power and if you are if you are interested in actual functions and these are the way that we can handle these kind of remind the kind of applications so you are welcome to go through this case study and I will I will be able to share you a github repo for this issue a function map so after this session also you can have a look okay so the technologies we are going to discussed about these case studies power apps and then I will be using Microsoft graph API so Microsoft graph API you can access the Microsoft relay that resources in your organization and also if you have a personal account you can access your data available in the onedrive maybe in the meetings in your calendar your tasks everything with a simple nod this Microsoft graph so what you have to do is if you if you haven't used that you can go for these so I will be showing that in the demo you can just navigate into the graph Explorer and you can try out the available methods in there so you have a like a playground to play with the data available in your organization or in your personal account be this Microsoft graph and the actual function I will be using Ashura function so I am building that own visual studio 2019 community edition on the.net framework 3.1 the latest release and I will be deploying it to assure and consume in the data from the excel file that's available inside our inside our Microsoft account so I will be accessing the excel file through the Microsoft graph and from the actual function I will be sending us email using this SendGrid API okay so the [Music] demo so yeah so first of all let's start with the power so I will share my browser here so I have already created the app let's check whether these works right so I will I will go here this is my app I will say edit you key so my PowerUp will be loaded like this let's wait for a minute okay so I will run this one and I will enter let's say online shopping for the month of May I hope all of you do there's a online shopping right and I really look it like $800 that's pretty high amount but let's say this okay it's available here and if I go into my office 365 account and check my excel files available in my onedrive this is the one so you can see the online shopping entry has been added okay now let's move into the azure portal so this is my actual portal so I have disabled these functions or will enable it and I will go to the monitor tab and we'll navigate to the logs so I will show you a sample email I got so this is sample email I got from this Astro function so this is a I used a time a trigger template for the actual function that means I can give a time stamp to run this function maybe you can shed you this function to run on here on 28th of each month for the testing purposes I have like asked the usher function to run any each each and every minute ok so let's build the app I think we have got the email so I will check say X F so you have to pay this much for the electricity bill this and this so if I check if you check the you check the excel file we have to pay the electricity bill on May and not the water bill so the this one this one and online shopping so with the total amount so if you check go to the Ashraf function so this is the log file it has been created when the function is fashion is running so I will disable this one because it runs each and every minute so that's a bit of a headache so I will disable this one and I will move into the development okay the power ups so the let's see how you can easily create power ups so you have to move it's a power apps so you you don't know anything about power apps now you are going to start with that so let's click on this link so you have to sign in with your credentials okay then I want to create a power app I want to start from data soit's you can say the data is like the heart of the application right so let's start with the data so I want to use Excel online so let's click on that and you have set of connections to connect your Excel files so let's click on new connection your excel file if I see my excel file it's available in my onedrive so then you have to create a connection to your onedrive okay so as one drive you can click create a connection to your onedrive it's there now you can see the available files and you can click on the excel sheet you want and it shows if I move into the excel sheet it shows the tab the tabs available in your excel sheet sorry a table right so I really click on connect so your behind-the-scene your application is getting created right okay so you can see Europe has been created with the data available in the excel sheet so if you check this tray view in the left side so you can see the other these three screens available the first one is brow screen so the developers we talk we tell it like the sunny sphere maybe the grid this is a list view so in the list view you can see each and every component the search box these the label those things you can change this if you want and you will be able to see the other two screens detail screens and the Edit screens this is the detail screens and this is the edit screen so let's try to run this one so I will move into the I will start from the prowl screen and run so you can search from here and you can do the basic crud operations create read delete and update operations so you can create a new item here hey my let's see that's a good person I'm going to save some money for my future pussied some counts so I want to do that on maybe 30th so you can remove this ID column if you want you don't want to have put the ID you know so but for this one I will put a random number for the price I say $500 then this will be inserted into your excel file oh sorry so this message it says the requested resource is locked that means when I click on save this right icon so it's going to save into the excel file but it's locked because I have opened this one so I will close this and try again it says the file is being locked yeah maybe something wrong just let's forget about that one try to run it again and say say oh it still says the the excel file is locked okay just something wrong just forget about that one and we'll move into the next step okay so this is the the detail screen this one you can edit if you want so in my application so if I here so this is my application I created previously in there I have I have removed this some of the fields in here so let me say it okay I think I have to I have to go with this one or else this field override because I'm connecting to the same mic certified okay so if I go to the edit screen in here I have removed the ID field if you don't want you can remove that okay so this is the most simplest powerup you can ever create so it's it's going to add some data into the excel sheet oh okay so here now I will move into the actual function so now we we saw how to create the powerup and that was inserted it was inserting data into the excel sheet and now let's move into the actual function so maybe you guys are experts in oshawa so this is the actual function I created so I will go through the code so let's so first of all I have created a time a trigger so you can create any type of trigger here but I want to get a reminder in for a in a specific day in a specific date so for that I created a time metric ax you can create you can have HTTP trigger you can have various type of templates here - whoa function and you can check the way different type of show function templates available so this is the I got a timer trigger and I am passing this excel file name and the worksheet and the table name here okay then I'm going to check this method the retrial data one so in here in here it's going to connect to this the file through Microsoft graph so now let's move into the Microsoft graph you have to say a micro soft graph so then you will get the graph Explorer so I will be navigating to the graph Explorer this is like a playground you can play with your data you just want to log into this with your credentials so now if you click on run query' you will get your information so so this is the getting started examples you have so if you haven't tried with the graphics Prada that's a cool you can do some cools coolest stuff in with this so just to have a look on this so if you want to have some excel related stuff so this is that's the one we are doing here so you can check these methods available so in here we are going to get the excel sheet so let's navigate to here and I will copy this one you I really hope and on pad plus plus you I will pass this one so the drive so now you got your information so if you say drive so what are the available drives in my office 365 account so you can see these things then it's route and children let's read the route you get this data you can see your the URL to access that with your account name here right so this one library this one your codename yeah I'm going to check what are the available items in here so you can see these are the items available in my onedrive so the excel file we are checking means these one payments excel file so it's there so now I want to get this fire I really check the children I will copy the name here run query you get that and to do these things you have to have some permissions right so different set of permissions to query only this part that means your information you don't want to have file read permissions but to do this one you want to have file read permissions right so you can do that one from here so if you go if you go into the files category you can see the file read/write the permissions are available for your account if you want to do something with let's say with emails you want to have this mail read mail right you if you want to send the emails you want to have this mail sent permission if you want to do something with your meetings with maybe tasks you have to have this tasks read and task read/write permission okay okay so so if you check the permissions here it's files this one files will be dry it's already given okay now I have have came into this point that week I can access the excel file now I want to move into its work its workbook and the worksheets right before doing the ing and before going through any other thing it's really easy you can come to the graph Explorer and you can set up your playground with the permissions and with your account details and you can check these stuff from just passing the URLs right now I will say workbook so the query still running you got the workbook details and the other URLs for that and I want a deep dive going to worksheets you can see the worksheet properties the name and the visibility and those things then I want to pass the worksheet name so what is the worksheet name is it's this one the tab named Excel tab name I would say payments and run query you okay and I want to check the tables available in that sheet you oh you can see the tables the table name as payments so then I really I want to get this table so this permission dialog box is like bitter tea annoying so I will go here then the payments this is the payments table write theta so p.m. tables and the table name and then I want to move into the rows you so you can see the rose available in that table right so now we are ready with the web URL to read the the Excel table we want now what I'm going to do is I'm going to move into the postman and try out with this method the URL we got from the graph Explorer it says this access has expired so in the graph Explorer you logged into your Microsoft account right but these so in here if you check the graph Explorer you can see the access for this right you want to provide this access to the postman so in the authorization section so you can go for the bearer and paste this let's end this one and you can get the data if not you can go with the authorization and select both to zero and get new access you want to get the new access without using the graph Explorer one right even in your application you have to give this access if you check the settings in here you have to give the access else you can get get the oven support and oh we know then you can provide the oven authentication and retry the access through the code without pasting it in here right okay so so how we can generate this ex stalker so you can give a name so you want to get totalization you want to follow the authorization code as the grant type and you have to provide a callback URL like this whatever the callback you are because in here in this application after the authentication has happened it's not going to call back to that application so you can give any type of callback URL and you have to provide these ad as the o'the authorization URL this we are following OAuth 2 v2 and this is the endpoint authorized and for the URL you have to provide the endpoint and then you have to provide the client ID and client secret to access this excel data so that we have to do with the a2 directory you have to create app application in no active directory so I've been moving to my ashwa you you you I will check I will go to the portal here I'm sorry that all me will add my password top it works yeah yeah this is I don't have any subscriptions for this account so but I can access the active directory right so I have created the application from app registrations here there precious trations you can create a new application by providing the name and you can select with this application be the we are getting can be accessed only for a single tenant only for the this organization and directory oil's you are going to provide it for any Active Directory multi tenant or else you are allowing personal accounts as well can go in the personal accounts and the redirect URI you have to prove the redirect URI same as this one the callback URL ok so now so I will check this one so this is my vacation name then you will get a client ID and this is the redirect URI I have ordered this one and then you have to provide in here you have to provide the client secret right so the client secret you can create the client secret here from certificates and secrets so I have already created a one secret so you can create a new client secret and put that into here then you have to provide a scope that means what is the permissions for what is the permission that should allow for the stalker so I won't the file read all permission and I'm going to request the to curl here so then I will get the screen like this so I'm going to login to this one and provide my password and you got the talk so I wear this copy this one and I will say use talkin and send the request you will be getting this date so we have to pass this from the settings file ok so now we got to know about the graph Explorer how to play with these graph explorer and the data so now we went through we go we went through these type of URLs and finally came into this point to get all the rows inside of excel file and you can get this URL into your coding environment right so I am using shoots to tour to 2019 Community Edition 1.9 framework 3.1 so let's move into this method so I am passing the URL so I'm passing the URL here and with the access so I'm getting the response I have used the rest client so you can't go with the graph is the key for dotnet as well but I'm going to be the rest client and I'm using these rest API calls so then I have done have added some login to identify we are these function apiece going the whether this is working fine and I'm getting these the values also if I so so it's bit off if you can see this one it's the kind of a 2d array right the data you are getting is a 2d array since the first row the second row it's kind of a 2d array to map that I have used helper class so I'm getting the date I am DMDC realizing the data this is the helper class I used with the index and this values 2d array and I'm looping through that and I didn't it to a list the help of this class okay then I am doing some filtration I want to get the expenses for this current month from first to the 30th from the first date of the month to the end day okay now then returning these expenses and then generate in the email from here right so I'm going through these expenses and add appending the expense description and add in the prices the amounts for the expenses and generating the email body like this and I am using SendGrid account to send the email so you can check this is the this is how you are going to build the email message and these these are the configurations I'm retrieving to send the email so as the so this is the access we talked about so this is the email server and the port and this is the my credentials so I will show you how to create a central account I will move into the portal again ah so if you are confused on my actual portals actually disease these these slow game is way I'm using this login to create my application to access the office 365 data the excel sheet that we connected with the power app so to host my actual function and to create the same grid a car I don't have any credits in here so then I'm moving to my the other issue account [Music] I will select my subscriptions and I will create a SendGrid account so you can go here home and create a resource and say st. great can create the same great account here right you have to provide the name and your password you have to remember these stuff and your informations and your sync read account will be created so I will check the SendGrid account we have linked in here so it's this one so you can go to the also this the centered' account and can click on the man eh then it will be redirected to your send great so in here you can see all of your user name and your email email address and your keys as well right so the API keys under settings so it can create API key so I think I have gone with the the free tier so this will not charge anything for you so so if you have a shoe account like I guess you all have a shoe account you can try out these stuff or I think it's a pre sometime this environment and the time is beat not usual for us it's not we are hosting webinars every day we are just having a face-to-face discussions and conferences so but we can get something good out of this time you can you have plenty of time to test and work on new things that's available so you have participated in these webinar and you have got something you have learned something out of from these then you can in your free time you can try out these stuff so this is about the SendGrid account so if I go to the e is funny up so this is the the the things the email configurations we want and then we can send the email the try-catch block for the safe side okay so so this code is available in my github or you will be getting the link when when I share my PowerPoint slides so this is available in here alright so you can clone this so these are some code snippets I just pasted here so this fashion and the this one to general get the excel data and to generate the email everything is available here so you can try out when you have time okay so so these are the technologies we covered here and the demo so this is my github repo so if you have any questions I think we can have time for the questions Thank You Hansa for that presentation lovely stepping into his various tech over there I'm looking at the Q&A I haven't got any questions come up yet but yeah for sure I think if anybody wants to post a question please do so and also I want your feedback so this one I'm sorry so if you have a mobile device we do when you have a QR code reader you can just scan this one and then you will get the screen to enter your feedback for me please provide that then that will be actually really helpful for me to build my speaking profile and once you've done your feedback you will get an email for your email account and then just go there and confirm that you are the person who has given me this feedback so thank you very much