Introduction to Office Scripts & How to Tweak Macros to Automate Tasks in Excel

[@LeilaGharani]

Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter

[@KevinChar]

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!

[@LeilaGharani]

You made my day :) I'm glad you found the VBA course useful. Thank you for sharing.

[@hasnainakram1530]

I also took the VBA course on udemy. Great teacher. God bless you 🙂

[@joellen8255]

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!

[@LeilaGharani]

Glad it was helpful!

[@chetanbangadi1686]

Dear Leila, Good afternoon, Your teaching style is very simple and easy to understand. Thanks

[@AWFarmer]

New VBA macros have never been so well commented when recording. Office script doesn't look bad.

[@ramm4012]

GREAT Teacher. Kudos. Thank you Leila. I have allways viewed your videos and learned. You make a difference. With Gratitude

[@dorothiamclellan6068]

Thanks Leila for your very informative videos. It is much appreciated! I have been trying to use Office Script to format a date that is stored as text as 20241106 for example and when I use the formula = Text(Date(Left(A1,4),Mid(A1,5,2) Right (A1,2),"mm /dd /yyyy") Excel changes the format to the correct date format, but when I record it and run the script it removes all of the dates from the column and just shows as blank cells. I am trying to figure out why this keeps happening. Are you able to advise me please?

[@estivisonmc]

Cool! It's future VBA on web office

[@vijayarjunwadkar]

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! 😊👍

[@LeilaGharani]

Thank you so much!

[@pupycron4302]

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!

[@dlep9221]

I hope that the office script language will be quickly included in Excel Desktop to have the same level of functionalities everywhere.

[@LeilaGharani]

I hope so too.

[@timolff9239]

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.

[@aruneshbahadur742]

Do you think this would be the death of VBA

[@fabiospringer6328]

@@timolff9239 Microsoft sells Office 365 for local servers? If not most big companies will not use excel online.

[@souzamotasacul]

Now they have

[@JuanMartinez-ic3sg]

Great video. I hope you continue this series of videos about Office Scripts.

[@SimpleExcelVBA]

Really nice & short example of Office Script. Appreciate your work!

[@selfmadetool]

Thank you for taking the time to record this!!

[@LeilaGharani]

My pleasure!

[@chrism9037]

Super helpful, thanks Leila!!

[@karunatayal1622]

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 :)

[@LeilaGharani]

Glad it was helpful :)

[@haowang6005]

Great to know stuff about office scripts! Hope can develop custom functions and share within teams across the web!

[@arnauddrouin1426]

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?

[@jorisvandeBrande]

So i'm late to the party. I was just thinking about the effort to make a new vba macro. Time to get back up to date!

[@gabrieleminniti7031]

Leila, you are my hero!!! Thanks a lot!

[@FarhanMerchant]

I like this approach, need to test it.

[@themen012]

Honestly (in my opinion) you became an advertiser more than a teacher.

[@igenart]

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.

[@MariaSaleem-gi4uj]

Very helpful....

[@MillersLanguageSchool]

This needs to be tested. Thank you. Good instruction!

[@tifouraamina1761]

New handy and useful tips as usual, Thank you Leila 🤩

[@LeilaGharani]

You’re welcome 😊

[@heldermartins534]

I apreciate so much your videos! Thank you.

[@nonoobott8602]

Great stuff. Your videos are always so informative and insightful. I've become way more productive on my job. Thanks for your awesome videos

[@abughaida4855]

Thank you very much, I really find your videos very worthy and helpful, really appreciate your efforts in sharing the knowledge.

[@LeilaGharani]

You're very welcome!

[@hospitaldecisionsupport3497]

Hi Leila, Thanks for your nice and easy to understand videos. I have a relatively simple project that I want to do in office scripts. Here's the project: There are 16 files in a folder like 1, 2, 3, 4...16. Each file has 5 worksheets with similar names like a, b, c, d, e. The task is to summarize all these files. I need to copy cell 'B2", 'B3' and 'C2:F2' from all files' x worksheets and paste those into a file name Summary's worksheet 'summary' cell B1, B2, 'B3:F3' respectively. All copied cells will be pasted one after another in the summary file. I can run manually without any auto trigger as it is required to do occasionally. The objective is to avoid copy paste manually which is error prone. How can I achieve this? Thanks, Ash

[@birasafabrice]

this is a great tutorial, thanks!

[@chris-hayes]

Very cool, good explanations!

[@AngeVPV]

Great video!

[@SyedMuzammilMahasanShahi]

Brilliant work. Thanks for the share.

[@LeilaGharani]

Thank you! Cheers!

[@shameemn6738]

U r simply superb ma'am... actually I was looking to learn

[@shameemn6738]

Refer me to basics of computer,like Mail,and other social media to use

[@anwarkrg9292]

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.

[@MostafaEhab122]

Really helpful, thank you a million!

[@LeilaGharani]

You're welcome!

[@ThisIsScorefun]

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?

[@garynagle3093]

Great video. Very useful!

[@LeilaGharani]

Thanks, Gary!

[@manabisrael]

Hi Leila, is it possible to use paste special function in office script - I want to paste only values - do not want to copy formulas. For example - I want to copy ranges A1:D4 , which has formula - now I want to paste the value only in the range K10-O14.

[@gc2603]

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?

[@Interesting-AF]

unfortunately refresh all connections doesn't work in Office Script (like it does in VBA)... typical Microsoft, releasing stuff half baked

[@gc2603]

@@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.

[@MMD-Cyber]

You are the best ever ❤️

[@dmstar101]

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?

[@darshitrander2741]

It's Helpful and Amazing, Thank You So much, It helped a lot!! 2nd Viewer 2nd Comment

[@LeilaGharani]

Thank you! Cheers!

[@ManafKAMIL]

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?

[@johnfromireland7551]

Would there, actually, be a reason to create the same macro in both languages? Not sure there is. Can you give an example, please?

[@ManafKAMIL]

@@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!

[@johnfromireland7551]

@@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.

[@ManafKAMIL]

@@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

[@johnfromireland7551]

​@@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 KZbin channel, here, about Office Scripts! kzbin.info/aero/PL2nG2TcT_3Z6pQniv9tSOdPOTbRCD8Eg4

[@emmafeng55]

Amazing tool!

[@andrewmoss6449]

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.

[@LeilaGharani]

Agree. It would be great to have more functionality.

[@johnfromireland7551]

@@LeilaGharani The button control is being testing, currently.

[@mars2459]

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

[@ewoudnl]

Any idea how to start an Office Script from the Quick Access Ribbon?

[@ramymahfouz1494]

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.

[@LeilaGharani]

Glad you liked it. Unfortunately, no course yet.

[@juanpablohorn6642]

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.

[@I.A.S.]

Thank you.

[@yongkyosunim]

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?

[@hugarohugaro]

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

[@Excel-power-users]

Can we run power query with office scripts

[@SampsonBert]

when you say dynamic what do you mean?

[@tedescojunior]

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 =(

[@jfamtd2770]

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?

[@KianBoon]

Is it possible to refresh power query using office script? I don't seem to be able to do that.

[@KaiWeissmann]

I would be interested in that as well :)

[@Interesting-AF]

no =(

[@edgards]

This is great! Do you have any videos how to attach these charts in an email?

[@JoshAutomates]

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?

[@vitruvian_man]

Yeah, Office Scripts for desktop would be a definite plus.

[@johnfromireland7551]

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?

[@remars-xcel]

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

[@chrissmith5347]

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.

[@yulanari1914]

Leila, thanks for sharing this truly informative learning. Would it possible to have Office Timeline? Thanks

[@kahhengyeong7947]

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.

[@LeilaGharani]

Yes - it doesn't pickup the region - at least not yet.

[@johnfromireland7551]

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.

[@internationalpolitics1275]

Hey Leila 'Record action' option not available in my excel automate group. Any help on this

[@StaticBlaster]

Awesome.

[@GenNextAnalyst]

Hey Leila, when are you going to have more videos on Office Scripts?

[@vijinvj738]

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 😊.

[@danbromberg]

I have OFFICE 2016 PRO but don't see an AUTOMATE option. Does it exist for us home users in OFFICE 2016?

[@manabisrael]

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.

[@ramanmahapatra1514]

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?

[@swaminathannarayanan2639]

I'm eagerly waiting for your full course about office script same like power query. Is it possible?

[@ManojKumar-zn2gf]

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.

[@LeilaGharani]

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.

[@eugrus]

There is no "Automate" ribbon on Word's toolbar. How do I access the Office Script environment from Word's UI?

[@KayDee2021]

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?

[@PeymanSayyadi]

Great Video. Can we use the Officescript to manipulate files and folders in OneDrive?

[@johnfromireland7551]

Office Scripts object model is only Excel Online - not the OneDrive object model. Use Power Automate for that.

[@AutoOneWork]

How to get the value of merged cell which is not is first one in merge range by office script?

[@ALIRTHCN]

سابسکرایب کردم چون همیشه به ایرانی هایی که باعث افتخار کشورشان هستند افتخار می کنم.

[@thepotbellyninja]

I have the 'Automate', but I do not have the 'Record' button . . . only 'New Script' . . . is it disabled?

[@vssuresh]

Wonderful tutorial Leila..👏👏👍 but unfortunately i don't have the Automate function activated 😌

[@OVERALLFITNESSGALLERY]

thank you

[@GameboyMufti]

Do you have any course to offer for office scripts. Thanks

[@taizoondean689]

Thanks

[@Felipe_XIV-XVI]

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.

[@sudhirsundararajan6613]

Can a repeative vlookup be automated using office script?

[@RobertLoPinto]

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?

[@johnfromireland7551]

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!!

[@tpeiNNN]

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

[@TerreneTaylor]

Could you create a shortcut to run the script ? I know in old versions you could

[@soheehan9180]

Can you make a button and assign one of the script

[@anthonyaltman4021]

Leila, can you simply export a sheet to csv using office scriptS

[@internationalpolitics1275]

'Record action' option not available in my excel automate group. Any help

[@MrLuuHai]

Your problem is the same mine, even I 've using CopuRight Office 365 Version.

[@internationalpolitics1275]

@@MrLuuHai mine is resolved now, Open excel in browser then record action will appear

[@wayneedmondson1065]

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!!

[@LeilaGharani]

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.

[@timotheospetros]

Presumably Office Scripts are not available to those with Office 2019 rather than the rented version called Office 365.

[@LeilaGharani]

Only on the Excel web for now (Business / Enterprise versions).

[@reng7777]

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?

[@LeilaGharani]

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.

[@Stef1955]

I was hoping to be able to do subtotals, but I don’t think it is possible.

[@GloriaSun-m8t]

How to use office script to save excel as PDF with ExportAsFixedFormat with friendly UI.

[@Stef1955]

It seems like there is a lot of potential for Office Scripts. Are you going to do a course?

[@LeilaGharani]

I would like to. But it will take some time, as I need to learn it better myself.

[@navjotsingh2251]

@@LeilaGharani can you record your journey on KZbin? 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,

[@LeilaGharani]

@@navjotsingh2251 Sounds like a good idea. I'll see what I can do here :)

[@Real_SD]

Nice

[@EthanJohns-j2k]

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