Microsoft Teams, Powershell and Excel

Long, Michael (Contractor) 26 Reputation points
2022-03-02T20:29:49.83+00:00

I prototyped a working powershell script that will enable the extraction of a specific named worksheet from an xslx file (it's not the first sheet), then update that same worksheet's workbook with updated values. When I try to run my script against the same file, that is now uploaded to Microsoft Teams, I only see the first worksheet of the file in powershell. This means my scripting fails to run by denoting an invalid index error (when trying to select the named worksheet I want). With viewing the same file online in teams, all the worksheets are visible including the one I want to read & update.

Is there an existing Powershell way, or a special MS Teams Powershell Excel toolset, that will enable the reading and updating of a specific worksheet within an excel file that is stored in MS teams via powershell scripting? Optionally, I would consider a visual studio solution if that's the only way to accomplish the task.

Any suggestions would be greatly appreciated!

Microsoft 365 and Office | Development | Other
Windows for business | Windows Server | User experience | PowerShell
Microsoft Teams | Microsoft Teams for business | Other
0 comments No comments
{count} votes

Accepted answer
  1. Yuki Sun-MSFT 41,376 Reputation points Moderator
    2022-03-03T05:22:39.053+00:00

    Hi @Long, Michael (Contractor) ,

    Great to see that you've managed to sorted the issue out and really appreciated it for your sharing!

    Since the Microsoft Q&A forum has a policy that the question author cannot accept their own answer, they can only accept answers by others. And according to the scenario introduced here: Answering your own questions on Microsoft Q&A, I would make a brief summary of this thread so that it can be easier for other community users to find useful information:

    [Microsoft Teams, Powershell and Excel - Summary]

    Issue Symptom:
    "I prototyped a working powershell script that will enable the extraction of a specific named worksheet from an xslx file (it's not the first sheet), then update that same worksheet's workbook with updated values. When I try to run my script against the same file, that is now uploaded to Microsoft Teams, I only see the first worksheet of the file in powershell. "

    Solution:
    "From MS Teams, you can right-mouse-click a shared file, and get it's link. That direct link is the one I was successfully connecting to with PS ... but it only had one worksheet.

    So I took another look at the link I copied and noticed a lot of trailing information beyond the file name like:
    .xlsx?d=wa791f45b0e1f478397e9b573d6d1061a&csf=1&web=1&e=5IMunM

    Back to my test script, I decided to update the excel file path link and remove all the html parameters starting with the ? and beyond.
    Then I re-ran my test script which counts all the worksheets.
    This time, all the worksheets were available!"

    You could "Accept Answer" for this summary to close this thread, and your action would be helpful to other users who encounter the same issue and read this thread. Thanks for your understanding!


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Long, Michael (Contractor) 26 Reputation points
    2022-03-02T21:53:19.017+00:00

    Thank you @Rich Matheisen , I have worked with the ImportExcel module which has some nice benefits too!

    I decided to try something and it ended up working!

    From MS Teams, you can right-mouse-click a shared file, and get it's link. That direct link is the one I was successfully connecting to with PS ... but it only had one worksheet.

    So I took another look at the link I copied and noticed a lot of trailing information beyond the file name like:
    .xlsx?d=wa791f45b0e1f478397e9b573d6d1061a&csf=1&web=1&e=5IMunM

    Back to my test script, I decided to update the excel file path link and remove all the html parameters starting with the ? and beyond.
    Then I re-ran my test script which counts all the worksheets.
    This time, all the worksheets were available!

    Sometimes the most obvious thing ... takes a second look to see it clearly.

    I hope this answer can help someone else that needs to update a shared excel file on MS Team with powershell.

    1 person found this answer helpful.
    0 comments No comments

  2. Rich Matheisen 47,901 Reputation points
    2022-03-02T21:10:39.593+00:00

    I can't speak to the MS Teams part, but for PowerShell have a look at the ImportExcel module. If you've been using the Excel COM stuff you'll find it a whole lot easier.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.