👩💻 How to use Office Scripts in Excel & Power Automate
HTML-код
- Опубликовано: 5 июл 2024
- 🔥 Learn Excel in just 2 hours: kevinstratvert.thinkific.com
In this step-by-step tutorial, learn how you can use Office Scripts in Excel on the web to automate repetitive and mundane tasks. Additionally, learn how you can trigger you scripts automatically using Power Automate. With Power Automate, you can set a Teams message, an email, or other app to trigger your script.
👋 Additional resources
- Sample Excel data to follow along: 1drv.ms/x/s!AmxrofZZlZ-whLBdx...
- Fundamentals of Programming in Python: • 👩💻 Python for Beginne...
- Macros Tutorial in Microsoft Excel Desktop: • How to Create Macros i...
- Introduction to Power Automate: • Power Automate Tutoria...
- Microsoft Office Scripts documentation: docs.microsoft.com/en-us/offi...
- Office Scripts overview:
support.microsoft.com/en-us/o...
- Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
⌚ Timestamps
0:00 Introduction
2:18 Requirements
2:42 Access Office Scripts
5:23 Record actions & run script
8:59 Check where script is saved
9:33 Run script on another worksheet
10:09 Code editor to tweak script
16:05 Share script with others
17:02 Trigger script with Power Automate
23:10 Advanced Power Automate
24:01 Additional resources
24:42 Wrap up
📃 Watch related playlists and videos
- Playlist with all my videos on Excel: • ❎ How to use Excel
- Playlist with all my videos on Power Automate: • Power Automate
🚩 Connect with me on social
- LinkedIn: / kevinstratvert
- Twitter: / kevstrat
- Facebook: / kevin-stratvert-101912...
- TikTok: / kevinstratvert
🔔 Subscribe to my RUclips channel
ruclips.net/user/kevlers?...
🎬 Want to watch again? Navigate back to my RUclips channel quickly
www.kevinstratvert.com
🛍 Support me with your Amazon purchases: amzn.to/3kCP2yz
⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.
#stratvert 
Наука
![[ GUILTY GEAR STRIVE: DUAL RULERS ] Teaser Trailer](http://i.ytimg.com/vi/e4TDqaE3XAg/mqdefault.jpg)
🏫 Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
⏭ Watch next - playlist with all my free tutorial videos on how to use Excel: ruclips.net/p/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY
Since we are working everyday it excel, maybe you can showcase power automate or power automate desktop on how to build flows to copy data from one excel to another, delete row/columns update pivots, save and send via email as attachment. That will be amazing
It's amazing what Microsoft can do to help automate one's workflow -- very curious to learn more!
Great video Kevin. Never heard of this word Office Scripts but you've now explained Office scripts are useful to automate your day-to-day tasks and how it works. Thanks Kevin 😀
Great to hear!
This channel is really helpful it should get more attention. I hope you will reach 1 mill some day
Hi Kevin. This is great!!! I'd love it if you would do videos showcasing more advanced applications of Power Automate and Office Scripts. Thanks so much!
Great video, Kevin! Thank you very much! As someone that has several repetitive monthly reports to do, I've been seeking a way to automate them for a long time. I've developed a lot of macros to help with automation, but was unfamiliar with Scripts until this video. My question is how to use both macros and scripts to completely automate this process.
great stuff Kevin! one thing i must say is that you got really good voice to deliver the training. normally i cannot bear any vid over 15 min, but this 25 min, it felt pretty short. if you got any courses available in udemy or elsewhere, please let me know. thanks.
Very.much interesting. I will start today itself and it will save tons of time. Many thanks Kevin for your invaluable information in this video
I never thought you can program in Microsoft office this is just very useful to me
Might be best to convert sample dataset to a table, that way if new rows are added your profit column calculation formulae won't be impacted.
This is fun!!! thanks for the content. Looking forward for more from you :D
This is exactly what I was looking for... 😍👏
Very insight tutorial on Office Script
Yes, it's very useful, its level more than macro, but a lot to learn for using this😁
0:05 - Hi Kevin! 👋
24:53 - 238th thumbs up from me! 👍 Cheers!
holy crap, this is brilliant.
That is pretty cool. Thanks and keep them coming.
More to come!
Hi Kevin, your video are very interesting with simple and to the point explanation.
Thank you for your video which help us to understand this excel with interest.
Hi Kevin. This was a great introduction to Office scripts. Please suggest if there is a way to run these scripts on desktop files
Hi Kevin, thanks for sharing this video. Can you please advise how to look up data in the active worksheet from another worksheet and how to put in the flow.
Awesome!! TQVM!
Very nice intro video about Office Scripts!
Hi Kevin! I just found your channel and really appreciate your videos. I JUST learned a little about VBA, wrote (copied and tweaked) a couple of macros and I am super proud of the work I have done. Now we decided to use a particular file in Teams so there is one macro I need to convert from VBA to Script. I wrote the script and when I run it, I am not getting any errors but it also doesn't accomplish my goal. I can't record the script because I am trying to enable outlining on a protected sheet which is something I needed to accomplish via macro. Do you know if this can even be done in a script or is it something that can only be done in VBA in the desktop version of excel? Thanks for your thoughts!
i am so hooked watching your vids
Nice basic presentation. I would like to know what I can't do with Office Scripts compared to VBA on the desktop. For example, can I clear filters when a user exits from Excel?
Great video, thank you! I was wondering if it's possible to use the script to refresh Power Query or DAX queries?
Thanks you kevin for this video you are the best youtuber
Wow, thanks!
Great video and very easy to follow. ExcelScript seem to have lots of capability working inside a workbook. I need to export/print/save the first worksheet to a PDF. Does ExcelScript's capabilities extend to file handling?
Hi Kevin, Thank you for tutorial. Is it possible to edit the Office Script offline on a code editor like VS Code locally on the desktop and then again upload to the cloud , where the initial one was.
Also is it possible to expand the code editor for Office Script on web to cover the entire screen so as to give more screen real estate
Hey Kevin, great video. As an insider (I know, not any more), any idea when Microsoft will support Excel Online auto refresh connections?
Very useful video! I have some source data in text file that I need to distribute to other teams in excel file using automate on a daily basis. Do you know if there is a way to convert text file to excel using the script? Thanks
Great video! Is it possible to use type script in excel for desktop as well? And create scripts online and use on desktop version and vice versa?
Hi Kevin, is there a way to save this into a new workbook with a dynamic name? or to create a new workbook using office scripts? Thanks!
Thank you much Kevin! 🥂
Hi Kevin. Very clear introduction. I would like to insert data from an Excel online spreadsheet to a Word online document (I do non use desktop office). Is that possible? In word online I do not see the developer tab and do not know how to activetate it or if it's possible to activate it. Thanks for any suggestion.
Thanks a lot Kevin, Can you give a guide to Script Lab as well, It works in desktop versions as well..
Beautiful video, Thank you Kevin Stratvert & You are the best
Thanks Maalouf!
@@KevinStratvert You are the best
Nice!
Thanks Kevin.
Thanks for watching!
Great Video. I need to use Power Automate calling an Officescript that would pivot a dynamic table. The Original table created by PowerAutomate has Year-weeks on the rows and sme Student info ( attendance) on the columns. I need to pivot the table, so that the Year-weeks ( changing every year) are columns and the rows are Student plus attendance. I have implemented this scenario using SSIS Packages and also calling a Script but now I am converting the SSIS package to a PowerAutomate flow using cloud data. Do you have a video or an example over how to achieve this? Thanks
Kevin, you are awesome!
it is a great illustration and very enlightening dear @KevinStratvert !
Thank you so much !
I just wanted to know, how we can get data ( text, numbers and some pictures ) from the available pdf documents and create new Word documents as per our conditions ( if, then, Loop) 😊
Could you please suggest a way out ?
Appreciate your support !
You looks like my little brother. Love your video, very high quality
Great video. Thanks. Can office scripts connect to SQL databases to run stored procedures? I have VBA written and need to convert to office script for web based excel users. Thanks in advance.
Hey Kevin, I would like to thank you first for all your videos, tutorials and all helpful stuff your are sharing. May i do a request? I m trying to send PDF files to one email address. I would like to use Power Automate or mailmerge. I followed your helpful tutorial (how to send mails with attachment with mailmerge) with succes! Do you have any suggestions how to do this? The title of the mails is the name of the pdf file, there has to be no Body text, maybe just the word archive on the titel as prefex for each mail. Thanks in advance! Keep the good work
Greetings Simo
Hi @Kevin!
Thank you very much for a great video! I plan to use this at work :-). Can you pls show how can we change a range. For example you bolded the first column A4:A9. If my range changes everyday, how can I say rage A2: A “end”of the row? Thanks a lot and you are the best! Cheers, E.
Excellent. Thanks
Well, there is nothing to say! You rock!
Thanks Dark Dudes AI... appreciate all your comments!
Thank you for sharing this. Please do you know if we can insert multi select drop down list in Excel web? I can't find an answer ou there :/
Cool video. Can scripts automatically identify keywords within workbooks before running?
Can we use Power Automate to export sales funnel charts created in Excel for multiple categories/brands so that they all appear in one slide?
Thanks, what version did Excel make Javascript default over VBA? I'm in 2016 & VBA is still default. I know you can reference external libraries in VBA; I'm just asking out of general interest.
Could you do a piece on how to calculate CAGR% on a series of periods for an investment?
Thanks for the video. Great info. There's a very annoying problem with office scripts and power automate. Scripts use the ID of the file and not the name of the file, even though you select by name. That means you cannot run the script on new files, even of the same name, as it comes back with workbook not found. I have an email that comes in every day with purchase order information which I need to reformat and email onwards however I cannot automate it as the file that gets created from saving the attachment has a new file id every time it is saved therefore the script fails. You have to open the file and run the script manually so it's utility with Power Automate seems very constrained as files would have to be changed in Excel in order for it treat it as the same file or you have to do as I do and run the script manually. Not so great for automation.
Another gem! Keep up the good work!
Much appreciated!
Similar to macro. Great tip! Can you please do a video on how to create and run a script with Google Sheets?
Great suggestion!
Hi Kevin,
Could you please create a video on how to set up meta, hashtag and keywords for youtube video ?
what should be the maximum numbers for it ?
and also need assistance on adding transcript (video section) in it.
Lots of love from India..
Hi Kevin..
Thank you for the above guide, I want to merge multiple excel files existing in share point document folder to one Master file and each time new file is updated in share point folder Flow need to pick up the latest excel file & copy to my Master file. Can you please help.
@Kevin S. is there a way to migrate Excel VBA code into Office Scripts?
Great video
Great video!
Can Office Scripts or Power Automate work with vlookup from two separate spreadsheets?
Is it possible to automate between MS apps. Like copy text from Excel then paste into Word?
Office Script is great!
The weak spot is the inability of running them if internet is unavailable.
If a file is synced to disk, and already has a Script associated with it, it should be possible to run them offline.
Hi Kevin, Thanks for the useful info...I'm struggling splitting a number in excel using scripts...I would like to split in thousands, hundreds and dozens in the respective cells...ex: 1234 splitted in 1 uK, 2 h, 3da and 4u, showing the result in the relative cells...I tried to use a script in js but don't know why doesn't recognize a variable and script goes in error. Please, any advise? Appreciate the support, Thanks
Thanks!
Thanks Kevin. How do I get "Automate" to the excel online? I use Microsoft 365 family.
Great great video
Excellent video thanks!!! Is it possible to make a video of Script Lab, for those who don't have a business or school version of Office 365? Thanks very much!!!
Thanks Toni! Great suggestion, I'll add to my list. Cheers!
Hi Kevin, I have a requirement like excell with color based on status when download excell data from gallery items. can we do it like that using power automate?
Please let me know if any possible ways. thanks in advance.
Great video and it could help with some of our processes. But our organization has a mix of Business Premium and Business Basic accounts. Only the Business Premium accounts have access to automate in Excel. If a Premium user creates a script for a spreadsheet and shares it with a Basic user with full edit permissions, would the Basic user be able to run the scripts?
Same question here. Got any info about this already? In my team we all have basic 365 so we are wondering if upgrading just one license to make scripts will allow other users with access to the workbook to run them (for example, making a button that registers data from a userform into a database so that every member in the team can use it). Thanks!
Great vedio Kevin, I have the files in sharepoint server instead of onedrive. Is it possible to run office scripts for files which is present in sharepoint server using power automate flow? When I for the files present in onedrive it worked but not for files present in sharepoint. I would be better if I got calrity on this. Thanks in advance!
what options do ppl have who own the 2021 office version (not 365)? can you use timed script that run weekly or daily?
Can you create custom functions in office script? if not how would you accomplish that in excel online?
It is possible to run a Office Script that has one or more input parameters in the main function directly from excel?
Seems like Office Scripts is the love child of: Macros + Office VBA + Nice User Interface = Office Scripts.
I suppose this is a good thing.
Just curious. Is Office Script also have buttons as the macros in Excel app?
Hello everyone,
Please i have a question about VBA vs Sheets scripts for 365 office.
In company we use both and i‘ve been learning vba for 1 year. I have excel macros on my workbook and this workbook is shared through 2 departments which can work at the same time, but there is some problems with saved worbooks through departments..
i heard about 365 office which can work with other people, but i need some protected cells, automaticaly fill some cells and other and other.
What is it better for use it if i want to use my workbook in realtime with others? And can scripts the same as macro or are scripts better?
And sorry for my english, Greetings from Czech! 😊
Can you run script's in shared mailboxes in outlook?
thanks bro
Anyone know how he formats the script code (10:38) so that the sections for .getFormat() .GetFont() .setBold(true) are all on separate lines? Visually it reads more clearly for me
Could you do a tutorial about Adobe premiere pro
hey kevin, can u make a video on filmora X
HI Kevin， I love your videos, but why I can not see the automate tab in my office 365? neither in desktop or web
Hey, may I ask, I already made the office script to my excel online file and share to other team member under same organization. However, when she copied the file, the script wont show and I need to click share again. Is there anyway I could attach the script to the file?
Hi Kevin when I create the script on power automate like you did i have an error message “body is required” and i don’t understand the error ! Can you or someone help me ? Thank you in advance! 🤗
Can you do the same with Outlook?
can u make a tutorial on how can i give permision on users who can edit my worksheet per selected cell range like in google sheet you can select and restrict users what cells they can edit and cannot. thanks in advance
Great tutorial! I am getting an error of 'The file format is not recognized. Please check that the selected workbook is valid.
clientRequestId: d2ded74b-3b89-4ebc-a5dc-b3cdde83738e.' The Excel file is .xlsx saved in the correct SP library. When running the script outside of Power Automate, works perfectly well. Thanks!
How does this differ from power query? And when would you use one vs the other? In your example what it the number of rows varied from report to report?
Power Query is ideal for formatting data before importing into Excel. With Office Scripts, you can set up logic to do all sorts of things (e.g. when someone fills out a Microsoft Form, enter the data into Excel and then update the worksheet based on the form responses). For variable number of rows, you can select the entire column or alternatively you can set up a loop to go through row by row until it hits an empty row.
Thank u
awesome video
Do you know how to use Power Apps? I am just learning and wanting to build an app like a shopping cart click and collect for my canteen
Here's my intro to Power Apps: ruclips.net/video/2RzCbd5XgJg/видео.html
I'm trying to figure out a script to hide columns based on the date. I know it would be easy to do with a macro, but I'm at a bit of a loss on scripts and haven't found a lot of good resources for it yet. Anyone that could help would be much appreciated. Thanks!
I'm a writer as a hobby and I also deal with NDAs at work so any documents I create needs to be secure and only o. My laptop.
It's all word and excel but Ive recently come to Microsoft from Apple and was frustrated with having to buy office. Ive come to terms with it but I need to know it's secure and private before singing up.
Hello Kevin can u make a video on automating excel to send an automated email with power automate and scripts , u gave an insight , pleas share that in detail
Check out this video: ruclips.net/video/Nl5AoLusNR4/видео.html
Hi! Is there anything like this for Google sheets?
Amazing
Thank you! Cheers!
@@KevinStratvert your welcome
Hi Kevin. I do not have the Automate Tab on my version. Can you give me some ideas please. I am in Office Insider.
I have no automate tap in my excel. I am using MS Office Professional Plus. Any way to add this tap?
Can i assign script in a shape to trigger the code..??
Please explain about the "developer" tab in PowerPoint.
Thanks for the suggestion! I've added this to my list. Stay tuned. Cheers!