Pull users and times from Excel and email if there are 5 or more in that time frame RRS feed

  • General discussion

  • Hello,

    I am trying to get a specific script working. I have an Excel sheet with data set up in the following format:

    Monday Time In Time Out Lunch In Lunch Out
    User 1 7 4 1130 1230
    User 2 8 5 130 230

    Is there a script I can use that will automatically send an email every hour when there are 5 or more of these users active and not on lunch?

    I am willing to learn, not asking for the whole script to be written or anything. I'm having issues wrapping my head around how to generally handle this. Can powershell store time ranges or can I store two different times and have powershell return true if a third variable is between the two? I'm a JavaScript programmer and this is my first go at powershell so forgive me if this seems like a novice thing to ask. 

    Thank you for any help!

    • Edited by AHVDesign Friday, October 18, 2019 1:32 PM
    • Changed type Bill_Stewart Tuesday, April 14, 2020 4:57 PM
    • Moved by Bill_Stewart Tuesday, April 14, 2020 4:57 PM This is not "scripts on demand"
    Friday, October 18, 2019 1:27 PM

All replies

  • See https://superuser.com/questions/731723/one-excel-file-multiple-editing-users-live-updates

    You are going to run into a problem with multiple users trying to update a single spreadsheet.

    Since you know JavaScript, a better solution would be to build a small ASP.Net web site that accesses a SQL database to track user's times. Then write a console based C# program that queries the database and sends the email. Use the Windows task scheduler to run the C# program every hour. 

    Or search the web for time tracking software and don't write anything. 

    Friday, October 18, 2019 2:14 PM
  • Yeah after hitting a wall for 2 days now I've been leaning on just making a totally separate method of getting this notification emailed out. So far I've managed to get powershell to at least pull info from the cells using this: 

    $excel = New-Object -Com Excel.Application
    $workbook = $excel.Workbooks.Open("C:\SecurityConcernsAndSuch\Schedule.xlsx")
    $value = $WorkbookTotal.Cells.Item(2, 2)

    So I was going to start coming up with all sorts of loops to get each user the data they need. Like having a variable per user that stores an array of numbers related to the time they are in. This would get so messy so fast though. 

    Friday, October 18, 2019 2:39 PM
  • This isn't the right place to ask others to design a solution for you.

    I agree with MotoX80 that your resources are better spent looking for a solution that someone else has already designed rather than trying to design your own.

    -- Bill Stewart [Bill_Stewart]

    Friday, October 18, 2019 2:46 PM
  • My apologies. I can see how it would seem that way but I wasn't looking for a fully designed solution, more of general thoughts on tackling this, even for parts of what I'm trying to do.

    This was intended to be some practice learning powershell and trying to step away from my web tools but it seems that I may just have to stick to my strengths here and continue to use separate platforms for these scenarios. 

    If this question isn't appropriate for these forums please remove it. I read the posting rules and believed I was still following the guidelines but I am incorrect I do not wish to cause disruption here. 

    Friday, October 18, 2019 3:00 PM
  • General thoughts are not specific enough to offer advice because there's not enough context.

    My guess is that you really have an XY problem.

    -- Bill Stewart [Bill_Stewart]

    Friday, October 18, 2019 3:24 PM
  • My guess is that you really have an XY problem

    LOL. "What's the real problem?" was my standard reply to most requests for help when I was working.

    AHVDesign, I would not say that your post is inappropriate, it's just kind of vague. Don't give up on Powershell, start with simple stuff, build on what others have shared. I follow this forum because I'm still learning myself. 


    Friday, October 18, 2019 4:08 PM
  • I should also not that you are asking for a complete solution and not asking a question about a script that you have written.


    Friday, October 18, 2019 5:45 PM