Introduction to Office Scripts & How to Tweak Macros to Automate Tasks in Excel
HTML-код
- Опубликовано: 2 июн 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Get started with Office Scripts. Similar to Excel VBA macros, Office Scripts allow users to automate tasks in Excel. The fundamental difference is that VBA macros are developed for desktop solutions and Office Scripts are currently only supported by Excel on the web (and in Excel for Teams).
We'll record a macro to automatically create a column chart for different data ranges. I'll also show you how you can review and edit your code in the code editor. For example how to make the fixed selected range dynamic by adjusting the recorded macro in Office Scripts. We will use variables and getSurroundingRegion to create dynamic ranges. I will also show you how to use Console.log to debug your code and how to make your chart title dynamic by using a cell reference instead of a fixed title.
00:00 Introduction to Office Scripts
00:38 How to Record a Macro in Office Scripts (Record Actions)
04:15 Review the Code in the Code Editor
06:00 How to Run a Macro
06:34 How to Adjust the Script for Dynamic Ranges
08:26 Dynamic Ranges with Office Scripts
09:36 How to Debug Code with Console.Log
11:03 Use Existing Scripts on Different Spreadsheets
12:09 Script Editing Options
13:16 How to Run Office Scripts in Microsoft Teams
13:47 Wrap Up
LINK to video comparing VBA to Office Scripts: Excel VBA VS Office Scripts: • Do You Need to SWITCH ...
LINK to Excel VBA video that records a similar macro with VBA: • How to Create & Use Ex...
Link to the complete blog post: www.xelplus.com/office-script...
★★★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel #officescripts
Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter
GREAT Teacher. Kudos. Thank you Leila. I have allways viewed your videos and learned. You make a difference. With Gratitude
Really nice & short example of Office Script. Appreciate your work!
Nice video to get started on Office Script! You are a great teacher Leila, taking one step by step through the procedure, while explaining it so well! Appreciated and looking forward for more! 😊👍
Thank you so much!
Your video is easy to follow and a great jumping off point for me, as I'm new to Excel for web and spent ages trying to figure out how to workaround lack of macro and VBA support. Then I found the magic words 'Office Scripts'. Thank you very much!
Glad it was helpful!
Great video. I hope you continue this series of videos about Office Scripts.
Dear Leila,
Good afternoon,
Your teaching style is very simple and easy to understand.
Thanks
Hey Leila, I have attended your VBA course for Excel last time. That can't overstate how great your course is. I still use it to this day and I still go back to the course from time to time for what you've taught me.
Yes, I honestly feel the need to have the VBA power on the web. If you are planning to have Office Scripts course, I will definitely be the first student in your class!
You made my day :) I'm glad you found the VBA course useful. Thank you for sharing.
I also took the VBA course on udemy. Great teacher. God bless you 🙂
This was a great intro I have been wanting to learn this but have been afraid to start. This was a great toe-dip into the water and I'll be all the way into the pool eventually!
Super helpful, thanks Leila!!
Great stuff. Your videos are always so informative and insightful. I've become way more productive on my job. Thanks for your awesome videos
This needs to be tested. Thank you. Good instruction!
Thank you for taking the time to record this!!
My pleasure!
I apreciate so much your videos! Thank you.
New VBA macros have never been so well commented when recording. Office script doesn't look bad.
Leila, you are my hero!!! Thanks a lot!
Great to know stuff about office scripts! Hope can develop custom functions and share within teams across the web!
New handy and useful tips as usual, Thank you Leila 🤩
You’re welcome 😊
I like this approach, need to test it.
Very cool, good explanations!
this is a great tutorial, thanks!
Cool! It's future VBA on web office
Thank you very much, I really find your videos very worthy and helpful, really appreciate your efforts in sharing the knowledge.
You're very welcome!
Brilliant work. Thanks for the share.
Thank you! Cheers!
Great video. Very useful!
Thanks, Gary!
Very helpful....
Really helpful, thank you a million!
You're welcome!
Hey Leila, I am just new with this script and it helps me to lil bit understand what actaually it is. I felt it is helpful for me, I checked few more tutorials for the same but your tutorial has something, thanks :)
Glad it was helpful :)
I hope that the office script language will be quickly included in Excel Desktop to have the same level of functionalities everywhere.
I hope so too.
I can imagine that they may not do so in order to fit into a longer term strategy to eventually make excel web the primary version of excel rather than the desktop verison. It would make sense among the strategies from the big tech players. Even if it doesn't add value for a long time for many users, it certainly adds value in the area of data storage. Offline business process means that you're duplicating large files many many times while you're emailing or otherwise sharing it. And while digitalization keeps advancing as it does then the cost of this storage will certainly add up to amounts that may not be feasible. Now i don't have enough knowledge to say whether these costs relate directly to how many GB's are on servers or whether it's more complex but i know that the costs are definitely there.
Do you think this would be the death of VBA
@@timolff9239 Microsoft sells Office 365 for local servers? If not most big companies will not use excel online.
Now they have
Great video!
U r simply superb ma'am... actually I was looking to learn
Refer me to basics of computer,like Mail,and other social media to use
You are the best ever ❤️
Hi Leila. Thanks for this. Learned so much from Practical Applications and clear explanation of step-by-step process.
Btw, using this example, how can you code the line that can change the gap width of a bar graph.
I used the record action button and changed the gap width. Although the recorded script shows error that it can't specify that action that I did.
There is a getgapwidth property, but how can I use this one?
Great video thanks a lot ! I have a question : If I already have my formatting, my chart, and other functions on my sheet, and I want to duplicate this formatting onto other sheets in my workbook, do I have to start from scratch by recording my actions? Is there a solution for sheets that have already been created?
Amazing tool!
It's Helpful and Amazing, Thank You So much, It helped a lot!!
2nd Viewer 2nd Comment
Thank you! Cheers!
Leila, thanks for sharing this truly informative learning. Would it possible to have Office Timeline? Thanks
Thank you.
Awesome.
Hi Leila saw your ad for a job , just wondering if you guys take apprentice. I just love to learn and I help other people through excel. Take care
Great tutorial, thanks.
just for understanding: There are semicolons at the end of each generated code-line, but in your new inserted lines at 9:38 you haven't add them.
Therefore not necessary?
Honestly (in my opinion) you became an advertiser more than a teacher.
Honestly too (in my opinion): I don't think so. This happens not to often in youtube, that the actor is precise, fast and correct. I understand some words included as a hint to commercial training.
Im not sure if there are further videos on the subject, but i would like to see how this has changed since last year! Maybe there are more posibilities.
thank you
Hi @Leila,
Please share the office script to delete the rows of the empty cells in the selected range. This will be more helpful for my daily task.
Thanks 😊.
Hi Leila, love your videos and have revisited them several times to clarify Scripts and automate functionality. I need assistance with a script. I have 2 tables. one is a master contact list and the other list customers NOT to be contacted. I have used an xlookup to identify the customers in the master list that need to be removed and when recording the script, and I filter the column to list the customer's to be deleted (then delete all rows to be left with the rest which are the #N/A) the script lists the customers. How do I point this "getvaluesfilter to the table so it is dynamic as we may add more customers to be excluded table?
Hi Leila, is it possible to use office scripts across multiple workbooks? For example, doing Xlookup across multiple workbooks so can eventually connect it with Power Automate
Hi Leila, I am currently learning office script and found your postings are really helpfull and easy to understand. I would like to ask a question, I want to do vlookup the data from the active file by opening another excel file. How to put this step in the scrip without using power automate? Would appreciate your help on this.
This is great! Do you have any videos how to attach these charts in an email?
It will be really cool when Office Scripts comes to the desktop version. I’d also like the ability to manipulate dropdowns, checkboxes and radio buttons - just like with VBA.
Agree. It would be great to have more functionality.
@@LeilaGharani The button control is being testing, currently.
Is Office Scripts good for clicking on links in Excel getting a document that will display as pdf and then save that pdf to a drive?
Hi, Leila. Is there a way in the office Excel script to generate XML and write it to file? In Excel running on Windows, we can use MSXML to read and write XML by using VBA. Do we have something similar for Excel 365 running on Mac?
Wonderful tutorial Leila..👏👏👍 but unfortunately i don't have the Automate function activated 😌
Nice
Thank you for this fantastic video. it was helpful. This gave me a fair idea about Office script future.
I want to ask if you have prepared a complete course on this so I can buy it.
Glad you liked it. Unfortunately, no course yet.
I'm eagerly waiting for your full course about office script same like power query. Is it possible?
nice. not sure if this was already covered but what if you have vlookups, sums, and other formula how do you make that dynamic when you have varying lines for different worksheets when using automate
Thanks
Hello Leila, nice video and example. Is it possible to run a PowerShell or Bat file from the Office Script? I am trying to do but I can't to see how to and the documentation of Office Script there's no any information about it =(
Hi Leila. Thank you for another great video.
How do you suggest moving forward with VB and Office Scripts? obviously creating the same macro, especially custom sophisticated macros are time-consuming and creating them in two languages is a huge effort. What is your advice for all companies that use excel as a desktop app and inside SharePoint?
Would there, actually, be a reason to create the same macro in both languages? Not sure there is. Can you give an example, please?
@@johnfromireland7551 I know at my previous workplace we used excel to perform automated analysis and reporting. The files are put on shared drive for several users to enter data. To move to excel in SharePoint, the VB must be rewritten in office scripts!
@@ManafKAMIL Once an Excel file is on SharePoint Online (SPO) click the "Open in Desktop" menu button. Then run your VBA macros. Your file never left SPO.
@@johnfromireland7551 yes, we can do that. But that is not ideal, is it. One advantage of using SharePoint is that users will only open files online, with no local copies. I know this is a transition phase, but until MS finishes the transition we will need to handle both versions
@@ManafKAMIL When a user clicks on "Open in Desktop", works on that file (i.e. runs a VBA routine), then clicks "Save", the file is saved to SPO - not the local users hard drive or network drives! It is my belief that, yes, millions of VBA macros(routines), across the world, will have to be rewritten into Office Scripts because of the migration of files and systems to the cloud (Microsoft365 and Azure). This means there will be hundreds of thousands of jobs required for Office Scripts developers. So, get learning! :-) Have a great day! Visit my RUclips channel, here, about Office Scripts!
ruclips.net/p/PL2nG2TcT_3Z6pQniv9tSOdPOTbRCD8Eg4
Hey Leila, when are you going to have more videos on Office Scripts?
nice
Hi Leila, Can you please do a video showing office script to select an empty cell in a sheet and past value to it from another sheet? For example - suppose in sheet 2, I have some values in column A up to row 10, i.e., A1-A10 full. Then I need to copy and past more data from A11 and I want to keep A11 as dynamic.
Hey Leila,
is there any way to ignore events/prompts/display allerts with script (like in VBA)? What i mean - i made a very simple script - refresh all data connections (Analyze in Excel file for Power BI), and script works fine but data doesn't update. I think the problem is that prompt window when i do manually refresh by clicking "Refresh All data connections" button in Data tab and there appear mentioned prompt abou Conection Safety (i have to click Agree/ok to run refreshing), and i think script doesn't "see" it so it just cancel refreshing...
In few words: Run script (pivot tables refresh) and it runs, but pivot tables doesn't start, nothing happens.
any idea?
unfortunately refresh all connections doesn't work in Office Script (like it does in VBA)...
typical Microsoft, releasing stuff half baked
@@Interesting-AF i cant like your answer... got same oppinion like yours ;) i hoped that this feature will work, and i'm quite piss*ed off.
This feature could be a little game changer, for now it isn't.
Great Video. Can we use the Officescript to manipulate files and folders in OneDrive?
Office Scripts object model is only Excel Online - not the OneDrive object model. Use Power Automate for that.
Any idea how to start an Office Script from the Quick Access Ribbon?
Hey Leila 'Record action' option not available in my excel automate group. Any help on this
What about UserForms? I've developed a mini app using Excel, but I understand that VBA is old & actually I should try, if I want to keep my "program" to migrate to an actual code, like Python or so.
Yeah, Office Scripts for desktop would be a definite plus.
VBA is based on Visual Basic version 6. Office Scripts is based on TypeScript - a new and language suited for web development. Which one makes good business sense to learn?
Hi Leila. Looks awesome! You make it seem so easy. Unfortunately, I don't have access to the Automate tab. Is there any way to gain access to Office Scripts if you don't have a business or enterprise account? If Microsoft wants to gain a following for Office Scripts, they should give early adopters access now.. like through the Beta Channel or via online with a personal account.. for those who are interested in experimenting. Hope that happens soon! Thanks for the look at what is to come in the future :)) Thumbs up!!
This is a good point. Google Apps Script is also available for all and not to business users only, so I hope they change this soon.
Can we run power query with office scripts
I am sorry if this post is irrelevant to the video above. I just somehow wanted an answer.
The problem in Excel is i have two columns of data worth 3000 rows. All are text. Lets say there is a text 'military personnel' in one cell and there is military in the other cell just below or above it.
I want to highlight military as it is repeated in one of the cells. There are multiple instances like this in the entire table, not just military but similar instances, but i am unable to get the desired results from Conditional Formatting or Search Fn or Find Fn. I want Excel to crosscheck duplicates in multiple words aswell where the words become part of two or three or four words. Can you help?
Hello Leila, I am new to office scripts and am trying to work out how i run a script that clears all filters and automatically finds the next available row in an excel table and to run this automatically when the excel file is opened in the browser or teams is this possible with excel scripts? Thank you for your time.
Unfortunately I don't have the "Record Actions" button available. Our organization uses the installed App. So while we can run scripts, and manually create a script, we can't record them. Do you know if this functionality will come to the installed apps?
Leila, can you simply export a sheet to csv using office scriptS
I have OFFICE 2016 PRO but don't see an AUTOMATE option. Does it exist for us home users in OFFICE 2016?
Nice but regarding the dynamic range, in the offline Excel macro, I can do that by recording with "Ctrl-Shift-Down".
I tried on the Office Scripts but it doesn't work. Hopefully "Ctrl-Shift-Down" can work in the future.
Yes - it doesn't pickup the region - at least not yet.
Here is some example the code for that :
selectedSheet.getRange("a2").getExtendedRange(ExcelScript.KeyboardDirection.right).select()
selectedSheet.getRange("a2").getExtendedRange(ExcelScript.KeyboardDirection.down).select()
I can't, currently, see how to select a 2D shape ( not one row or one column as above) using the getExtendedRange method.
How to get the value of merged cell which is not is first one in merge range by office script?
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
In my laptop whenever i click or enter control panel or any other things on which there is a white background screen brightness increases and whenever i switch to dark background, brightness decreases what the solution for this??
Can you view the list of script names in one vertical column? The horizontal layout at the top barely fits the names of the 3 scripts. What does it look like when you select the drop-down arrow? What if there were hundreds - how to search, browse and organize them all?
Click the All Scripts button in the menu bar (aka tab). You can also view the actual Scripts in your OneDrive. They appear as osts files!!
There is no "Automate" ribbon on Word's toolbar. How do I access the Office Script environment from Word's UI?
Can a repeative vlookup be automated using office script?
I have the 'Automate', but I do not have the 'Record' button . . . only 'New Script' . . . is it disabled?
I was hoping to be able to do subtotals, but I don’t think it is possible.
Could you create a shortcut to run the script ? I know in old versions you could
Can you make a button and assign one of the script
It seems like there is a lot of potential for Office Scripts. Are you going to do a course?
I would like to. But it will take some time, as I need to learn it better myself.
@@LeilaGharani can you record your journey on RUclips? Maybe every time you learn a new function/algorithm share a quick video explaining it! Would love to see you learn and learn with you,
@@navjotsingh2251 Sounds like a good idea. I'll see what I can do here :)
Do you have any course to offer for office scripts.
Thanks
Out of curiosity, is it possible to have a single office script that can run on two different workbooks with has same information ie. simply it has to create a unique ID in both scripts. Instead of having two scripts in two workbooks, I should have only one script that can create an id on both worksheets.
That's the beauty of Office scripts. They're not stored in the workbook but in your OneDrive. So, you can use the same script in any of your workbooks.
Leila i got a question.. i do have an office 365 account but is not for business instead it is personal, how ever i got installed the Add_ in ScripLab tool , wihch is the same for AUTOMATE option that you have , but for some reason is not working when i mimick the same exanple that you have provided to us in this video!! What would you say would be the reasons?
As far as I'm aware Office Scripts currently don't work for personal account. You could however, post this on the Microsoft Tech community for Excel- maybe someone can provide more insight.
Is it possible to refresh power query using office script? I don't seem to be able to do that.
I would be interested in that as well :)
no =(
Hello Ma'am
I'm really impressed by your videos
Actually I'll have to present my topic in front of my university class
So, I need your help. if happy to help me in using Automate Tab
Please make video how to fix no internet access in windows 10 while globe sign appears.
Hi
Can script be assigned to button, like macros ?
Unfortunately, not yet.
Presumably Office Scripts are not available to those with Office 2019 rather than the rented version called Office 365.
Only on the Excel web for now (Business / Enterprise versions).
Hello, my MS Office opened in google chrome, how can I stopped it
My Excel does not have the "record script" option
Doest the script work on non online Excel spreadsheet?
No.
can you create custom functions?
No, but maybe try Lambda functions, instead?