No More VBA. ChatGPT to automates boring work with Office Scripts
HTML-код
- Опубликовано: 3 июн 2024
- Excel Office Scripts are the future of automation for Excel and other Office apps, replacing VBA and macros going forward. In this video I use ChatGPT to write an Office Script for me and the results are impressive!
⬇️DOWNLOAD the example file & Office Script here: myonlinetraininghub.com/write...
📜OFFICE SCRIPTS LICENSING REQUIREMENTS
Office Scripts is available to Excel users with the following:
1. Excel for Windows (version 2210 or higher), Excel for Mac, or Excel on the web.
2. OneDrive for Business.
3. Any commercial or educational Microsoft 365 license with access to the Microsoft 365 Office desktop apps, such as:
• Office 365 Business or Business Premium
• Office 365 ProPlus or ProPlus for Devices
• Office 365 Enterprise E3 or E5
• Office 365 A3 or A5
4. Internet connectivity with connected experiences enabled.
🎓 LEARN MORE in my Excel courses: www.myonlinetraininghub.com/
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
📢 Please leave me a COMMENT. I read them all!
🎯 CONNECT with me on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
0:00 What are Office Scripts
0:52 Where to find Excel Script Tools
1:24 The Scenario
1:49 Using ChatGPT to write Office Scripts
3:07 Using the Script form ChatGPT
3:42 Running the Script
4:08 Handling Errors in ChatGPT Scripts
4:38 Re-using Scripts you Create
5:04 Assigning the Script to a Button 
Наука
Once again, you Excel! I've loosely followed Office Scripts for a few years during which I've repeatedly resolved that I won't learn b/c I'm closer to retirement than my next job. At my age, I consciously try to avoid new shiny things & focus on savoring the old shiny things. To that end, I finally started writing VBA classes in the past year and (and!) just started creating customized ribbons. Woohoo! Now, along comes ChatGPT (itself a new shiny thing that I'm finding very useful for many applications) to make me chase a new shiny thing (Office Scripts), and along you come to encourage me to break my resolution. Ha! New shiny thing, you've been warned! Thank you, Mynda. Another fabulous tutorial. I look forward to seeing how much I can wring out of Office Scripts without actually having to learn it. 🙂
😁 have fun with them, Jim!
I'm in the same boat as you! However in my work I recently took on a client that was still using all of their Excel in .xls, had not one pivot table, and did not automate entry uploads (though was capable). It is a high transaction environment, so I learned Power Query. (I evangelize it now; and I'm surprised by the number of people who were just like me, had never used it (or heard of it). It has been a lifesaver for me to bring my client into the current century and save countless hours of staff mindlessly entering data. I wished I understood VBA better..and this is a shiny thing to my magpie eye.
Old dogs must learn new tricks...otherwise we may result to growling and snapping. Nothing better than figuring out problems to keep us sharp.
Thank you Mynda! Your educational videos are extremely perfect.
@mohammedelsakally540 🙏 Glad you think so!
@maruiacancer6 best wishes for your recovery.
The younger spreadsheet up-comers sure have it easy 👍😎✊
Indeed they do.
@@MyOnlineTrainingHub😊
Yeah but they won’t know how anything works. Perfect for things that are always easy, but soon no one will pay you much to do easy things, and won’t help you build a foundation to do the hard stuff.
Sort of. This video is, to say the least, a gross exaggeration of what gpt can do. But yes, it is very helpful getting your head around ways to do stuff, learning syntax, getting you out of the sand trap so to speak. It will not do the work for you, though. Sometimes you have to fight with it for so long to get something so simple that you realise you should just looked at the documentation an hour ago. But the lure of plain English questions with straight forward answers is very strong. Can be hard to suck it up and scroll through static text when a direct answer could be the right question away...
Thank you, Mynda. Highly informative as usual!
So glad! 🙏
Your tutorials are always usefull Mynda, thanks
🙏 Glad you like them!
Absolutely AWESOME - has helped me so much, Thanks Mynda !!
Wonderful to hear!
Thanks Mynda
You are indeed a fantastic inspiration.
Thanks so much, Ivan 😊
That was amazing. Thank you Mynda.
Glad you enjoyed it!
Brilliant! No words to thank you!
Thanks so much!
🎯 Key Takeaways for quick navigation:
00:00 📎 Office Scripts are the future of automation for Excel and other Office apps, replacing VBA and macros.
00:29 📝 Office Scripts require JavaScript or TypeScript skills for manual editing or script creation.
02:05 💡 ChatGPT can be used to automate Excel tasks using Office Scripts by providing a clear and detailed prompt.
03:54 🧰 ChatGPT-generated Office Scripts can be easily added to the script gallery, making them reusable across workbooks.
05:17 ⏩ Once attached to a workbook, Office Scripts can be executed by users with edit access, saving time and streamlining tasks.
Made with HARPA AI
Nice! Thanks for sharing.
An interesting experiment. Your version certainly worked beautifully.
When I entered exactly the same text as you. It came up with an answer containing bugs. Repeatedly listing these errors and regenerating an answer gave modifications, but after about eight iterations it ground to a halt and wouldn't provide anything new, or at least had the same bug.
The final answer it had got to also bore absolutely no resemblance to your final working code.
Think I'll stick to VBA for a while longer.
Yeah, it’s still hit and miss. I’m sure it’ll get there.
Wow, super useful! Thanks Mynda!
Cheers, Chris 🙏
Amazing!🎉Thank you for sharing!👏
My pleasure!
Love your videos and at same time get somewhat frustrated that I try to duplicate your work and seriously I can't get this to do like yours. Your teaching is amazing for sure and no doubt i have something in my excel that is not picking up the commands. My script type exactly as yours tells me I have 9 problems and 1 output error. Line 12: Cannot read properties of undefined (reading 'format') and all the problems are also exactly the same. It is interesting and I have had Chat GPT change the script a couple of times to try and address. Thank you for putting up the videos. Everyone is amazing
hmmm, I wonder if it's a regional difference. I'm using an English version of Excel.
@MyOnlineTrainingHub I do too USA
I remember thinking they'd have to pry Lotus 123 and its macro language from my cold dead hands before I'd switch to Excel. Now, decades and countless Excel macros later, I feel almost the same way about leaving VBA for Office Scripts. VBA feels like an old friend, but I can see how it's limited compared to Scripts. I'm sure VBA will be around for some time yet, but you have to roll with the punches if you want to stay relevant.
Exactly, just have some faith the new language was made by smart programmers in consultation with users so it must have some new features or benefits you'll eventually love and not want to let go.
Nice perspective, @graytonw5238. Have fun with Office Scripts 😉
@mathtutorvideos, let's hope so.
@@MathTutorVideos "new language"
JavaScript has been around since last century, a major language used extensively in websites.
Qqqqq+
Excelent video. Thank you!.
Glad you enjoyed it!
Thank you for sharing, greatly tips!
Glad it was helpful!
Thanks for your valuable lesson.
Glad you liked it!
I'd still write with my old VBA just because I know what I'm doing and how it works under the hood. Moreover, my brain works which is beneficial for me instead of copy pasting someone's code .
I agree, there's a lot to be said for having your brain do the work.
Interesting video. Thank you for sharing ur knowledge with us :)
My pleasure!
It reminds me of my use with the first automatic code generators for RPGs.
Now it is the same and improved for VBA, and so let's use it.
We do not want to rediscover the black thread. Let's ride the wave of knowledge with AI
A great video Mynda , greeting from Mexico
Thanks so much, Miguel! Glad you liked it.
@@MyOnlineTrainingHub Thanks to you greetins from México
Thank you very much! It would be great to learn more about chatgpt
My pleasure 😊
Thank you Lynda.
You are most welcome
I really like VBA, but Office Scripts is here to stay. I guess we should all start learning JavaScript at some point, but that’s ok. Life is a learning journey 😊
Indeed it is 😊
Excellent again Mynda - Thanks - might be worth mentioning how to activate the Automate tab in Excel as I had trouble finding it - will give it a try - again thanks
Thanks! The prerequisites for Office Scripts is in the video description as mentioned at the beginning of the video. I'd say you don't have it because you have a 365 Personal license.
You are correct! I eventually worked that out! Thanks
This is so cool but it you still need an understanding of the api, especially if you are getting away from really basic tasks. On other platforms I have found ChatGPT invents its own api calls that don't actually exist so this is what will pull you into learning the api. It is super helpful for learning the language and you can also paste a code snippet and have it reverse
engineer it for you
Great advice for learning the language leveraging ChatGPT.
First off, I wanted to express my gratitude for creating the video "ChatGPT to Automate Boring Work with Office Scripts." It was incredibly informative and well-presented!
I'm interested how you got ChatGPT to recognize "Excel Office Script" When attempted a prompt using that exact phrase, here's the initial response with GPT-4
"Please, be aware that currently, Office Scripts only work in Excel for the web. As of my knowledge cut-off in September 2021, they cannot be used in desktop versions of Excel. Always verify the most recent information directly from Microsoft's official resources."
Your video didn't show that response... I know the limited knowledge of GPT to 2021, but your prompt seemed to have no issues or "concerns". Any thoughts?
Once again, thank you for the valuable content, and I'm excited to explore more of your videos!
Glad it was helpful. I didn't specify in my prompt which version I would be working in, so maybe that's why ChatGPT didn't flag an issue. It is correct in that Office Scripts were first developed to work in Excel Online and since then the desktop version of Excel has been updated to also work with Office Scripts. It's the same script working in both versions.
Very helpful 👍
Wow, words in your comment this time! I'm honoured 😁
@@MyOnlineTrainingHub your work deserves many kind words 😏
This sounds ideal for solving a problem I have with a one drive file which I will be using for a charity fundraising competition. I had recorded a macro and hadn't realized it won't work on line. I'm going to give it a go tomorrow. Will probably be a disaster with me doing it
Great to hear! Good luck 🍀
Thanks about to go in !
Hi. Desperate of York UK here ! It worked . Unfortunately I seem to have hit a problem that I cannot seem to resolve and it's driving me mad and a bit worried as it's for a charity competition and I hoped to be able to show people it by now. I created the spreadsheet in Excel and used the automate option on the menu. All worked and does the job I wanted it to do. As it's for a charity competition I need it to be on a shared drive so put it on Google drive. When on there the buttons don't work. I have spent ages on Google and everyone says apps script is on Google drive or can be added to the browser. It's not on mine and I cannot see it on the browser or where to get that extension. I've been doing this on a laptop with win 10 and although I didn't think it would make any difference tried the desktop tonight but the same again. My Google sheets doesn't have an extensions tab or anything in the tools tab or when more is selected.
I would be so grateful for any thoughts on why I'm having these problems.
Excel for Windows (version 2210 or higher) should this be 2010 (in the notes). Thanks for introducing this feature.
No, 2210 is the version number you find when you go to File tab > Account > About Excel.
@@MyOnlineTrainingHub Well DOH! for me and good to know.
Fantastic!
Glad you like it!
Thanks Mynda. But, MS need to make this feature available to Consumers!!!!!!
I agree!!
No more VBA?! This year I am "celebrating" my 40th anniversary as a BASIC coder since I started on my Commodore 64 back in 1984 as a little boy...
But it seems there's more advanced stuff available anno 2024 indeed ;-) Thanks for the tutorial! Greetings from the NL.
Happy coding 😁
I find using VBA easy compared to most of excel when I need to do more than basic stuff.
Hi Mynda, I love your presentation skills and knowledge. Alas, I copied your chat and pasted it into ChatGBT, but didnt get the postive result you did. I get 10 Problems relating to ranges and formats such as [12, 25] Property 'format' does not exist on type 'Range'. However, I am really impressed with ChatGBT
Thank you! Yes, I get some errors sometimes too, but feeding the errors back to ChatGPT enabled it to eventually get it right.
ChatGPT at its best. Will solve every minute problem occurred
I fed the same errors back multiple times and ChatGPT was unable to "get it right". Still excited to have this work somewhere down the line. Thank you for keeping me on the cutting edge.
Thank you MIndy (again!) Great video and a useful example. What will be the equivelant of vba userforms in Javascript? I love userforms and use them a lot.
Thank you! There's no exact equivalent to VBA User Forms in Office Scripts, but you could try Excel Forms: ruclips.net/video/Eys3YTmtK2s/видео.html or use Power Apps to write an app interface to capture the data.
Thank you
Pleasure 😊
Great stuff Mynda - is there an equivalent to personal.xls WB in office scripts?
Yes, by default the scripts appear in the script gallery for use in any workbook.
@@MyOnlineTrainingHub Excellent-thanks Mynda, I have a few VBA macros that I run on CSV downloads from ERP systems- I should probably think about moving these to office scripts.
Great video Mynda. What will happen to companies where there are many macros created with VBA?
I imagine that they will think about migrating the code to scripts!!? Thank you!!!
Some will migrate and some will remain as VBA with the limitation that they can’t run on the web.
Thank you for a video covering office scripts! I’m curious about them, and I like your teaching style. I also love the example, a table of contents.
I’m curious about uptake. My work is on Office 2019, and I have a personal Office 365 subscription. Therefore, Office Scripts are out of my reach. I envision a long timeline for migration in an enterprise environment if there are a lot of VBA-developed Excel applications.
Yes, it's a shame about the limited licensing. Definitely another reason to keep VBA skills fresh.
Buenos días. Espectacular. Personalmente, no puedo usarlo en mi equipo doméstico porque mi suscripción es Microsoft 365 Personal. Aunque lo empezaré a probar en mi trabajo, que sí está habilitada la pestaña "Automatización" en la cinta de opciones. Comenzaré a usarlo de todos modos. Muchas gracias.
Great to hear!
How do you get that Automate tab in the ribbon? I have Office 365 on my laptop, but I can't see it in the available options.
Please see the list of compatible 365 licenses in the video description.
Hi, love your videos. I use excel for mac, and mine does not heve this automate tab. is this only for the windows version? thanks
Thank you! It's available for Mac, but you have to have the right license. See the video description for the compatible licenses.
@@MyOnlineTrainingHub thanks for the reply, didn't notice that. My licence is Microsoft 365 family, so I guess this feature is not included
Hi, Is it possible to get a used range (basically count) of any particular column from a sheet in the Excel workbook using office scripts? Though we have a getusedrange method it always gives us a used range of the entire sheet, but I need it only for a particular column.
It depends how you define 'used'. You could use COUNTA to count how many cells have data in them. If that's not what you were after, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Great❤
Thank you!
Terrific
Glad you liked it!
I have a Microsoft 365 Family subscription (recently renewed), and I'm on the Beta Insider Edition of Excel, but I don't have the Automate tab in either Excel Desktop (Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16721.20000) 64-bit) or in Excel Web. Would you be able to provide a link to find out how I can get the feature, or does it require an "organizational" (School/Business) account?
This sounds like a license limitation. See the video description for the licenses that have the Automate tab.
@@MyOnlineTrainingHub I believe Power Automatic comes with the Family Version of M$365. Could you work with that version of Power Automate and run a script in the Family Version of Excel? Though I do not know if you can make a script that way.
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.
Ooh, good question. I'm not a Power Automate expert, sorry. You're best to post your question in the Power Automate support forum: powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity
is there a video on how to get the automate tab? I have office 365 but it is not present
I'd say it's a licensing issue as you don't turn it on as such. You either have it or you don't. The required licenses are listed in the video description.
I really Like your You tube Videos. I tried also more then 10 Times without near to your Ressponse with Chat GPT. I currently have Version 3.5 where cutoff was Sept. 2021. For Chat GPT4 I need to apply 20 USD per month. Which Version Are you using?
I used the free version. If it returns errors, copy the error and paste it back into ChatGPT and ask it to fix it.
@@MyOnlineTrainingHub Thanks for your answer! I tried again couple of times, then moved to Google.bard, which provided me after 5 tries similar code as yours. But use still use chat gpt standard, vor other excel/sap scripts. Thanks for your videos! Have a good day!
I do not have a tab "Automate" and I cannot add it like for example the "Developer" tab. Do you know what should I do ?
I’d say it’s a licensing limitation. See the video description for the list of licenses that have office scripts.
Can anyone recommend any books on using Typescript in Excel?
Tis really great but even in beta mode I can't get Automate to appear, just going to have to wait
Automate requires specific licenses. See the video description for the prerequisites.
I have windows 11 ... version 22H2 ... is it available ?
It's not a Windows requirement, it's Microsoft 365. See the licensing requirements in the video description.
actually where to find automate office script option?
Please see here for the requirements for Office Scripts: www.myonlinetraininghub.com/write-excel-office-scripts-with-chatgpt#req
What I have seen is Office Script fails to refresh an Online Excel file through Power Queries. The same will work if you refresh the file manually locally. In a Power Automate Flow, it shows successful but when you open up the file, data doesn't get refreshed. I was hoping that Microsoft would have solved this issue but so far I have no solution to this.
I haven't tested Power Automate for this, but I'd have thought you have to have Power Automate actually open the file for the refresh to happen. Nothing recalcs in Excel without the file being open.
I've tried over 20 times with various AI options: ChatGPT 3.5, ChatGPT 4 and Claude...constant errors ... doesn't seem to work with my 365 subs
hmmm, did you try my code?
@@MyOnlineTrainingHub I'll try it once I figure out how to copy it!
Yours works. The various codes I got from ChapGPT 4 were different.
Why it worked for you and not me is a mystery to me. I got a batch of errors like: See line 5, column 9: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable. after entering the same text you used for the TOC. Hmmm.
I got some errors initially too. Then I tweaked my prompt and fed the errors back into ChatGPT for it to correct them. It got there eventually.
Having used VBA for so many years I'm not going to spend time learning how they have twisted the Excel object-model and given methods, functions and attributes new names. Nope, not me.
😁fair enough!
One important note. The Office Scripts generated through the automation are written in Typescript, which is a superset of JavaScript. You can indeed also write script in JavaScript, but since it is an awful language, it is better to use Typescript.
Good to know 🙏
can😢find automate tab, i am on beta
It's probably a licensing limitation. See the video description for the licensing requirements.
😲
😁
Who uses excel online?
Its a crippled version of desktop
Yes, I agree. Online has a long way to go. I don't use it, other than to 'open in desktop app'.
I agree, it still has some way to go, but it's getting new features every month.
@@MyOnlineTrainingHub Can you write scripts with Excel Online and use Power Automatic to run the Scripts in Excel Desktop?
☹️ Promo-SM
? not sure what you mean.
So vba is dying slowly 🤷🏼♂️
very slowly. I think VBA will be around for a long time because there are so many systems that run on it, but going forward people will want to automate online as well, so OS will become more popular.
@@MyOnlineTrainingHub If you think VBA is going out of style, there is a demand for Cobol programmers today, still.
We are going to lose our jobs :S
I don't see it as that black and white. I think our jobs will change, sure and some jobs will go, but someone still needs to operate the AI....for now 😉
@@MyOnlineTrainingHub for now... that's a good point :). Anyway, Thanks for the video thats really awesome!
This is nonsense. I can assure you. I'm learning JavaScript/OfficeScript right now, and gpt is a big help. But if you can't read what it writes, you have absolutely zero chance of getting anything faster than it would take to just do it manually without using code anyway. You flat out will not save time if you can't read and fix what gpt wrote. You shouldn't really have anything left that gpt wrote by the end. It's a good place to start. Worthless place to end
Thanks, this is a nice process to get your Office Script knowledge to a higher level.
As to be expected, I received a different code sample from ChatGPT. After solving some issues, I added some code so the existing sheets gets a link to the TOC sheet as well.
On the condition that the cell A1 on thatsheet is empty.
I also introduced a variable to mimic the With ... End With construction from VBA, for setting the properties of font in the cell.
I wonder if this will increase or decrease the performance.
It might make the code better readable.
function main(workbook: ExcelScript.Workbook) {
// Step 1: Check if a sheet called "TOC" already exists. If it does, delete it.
let tocSheet = workbook.getWorksheet("TOC");
if (tocSheet) {
tocSheet.delete();
}
// Step 2: Create a new sheet named "TOC" and set its properties, including hiding gridlines.
let newTocSheet = workbook.addWorksheet("TOC");
newTocSheet.activate();
newTocSheet.getRange("B1").setValue("Table of Contents");
// Step 3: Format the heading in cell B1 with black bold font, size 18.
let myFont = newTocSheet.getRange("B1").getFormat().getFont();
myFont.setBold(true);
myFont.setSize(18);
myFont.setColor("#000000"); // Black font color
// hold a link to the TOC sheet
let hyperlinkTOC = '=HYPERLINK("#TOC!A1", "TOC")';
// Step 4: Start the table of contents hyperlinks in cell B3.
let currentRow = 3;
// Step 5: Loop through all sheets in the workbook and create hyperlinks to each sheet in the "TOC" sheet.
for (const sheet of workbook.getWorksheets()) {
if (sheet.getName() !== "TOC") {
// Create the hyperlink formula using the HYPERLINK function.
let hyperlinkFormula = `=HYPERLINK("#${sheet.getName()}!A1", "${sheet.getName()}")`;
// Write the formula to the "TOC" sheet.
newTocSheet.getRange(`B${currentRow}`).setFormula(hyperlinkFormula);
// Format the hyperlink with blue font and an underline.
let myFont = newTocSheet.getRange(`B${currentRow}`).getFormat().getFont()
myFont.setColor("#0000FF"); // Blue font color
myFont.setUnderline(ExcelScript.RangeUnderlineStyle.single);
// get the top left cell of the sheet in the loop
let topLeftCell = sheet.getRange("A1");
// check if the top left cell is empty
if (topLeftCell.getValues().toString().length == 0 ) {
// insert te link to the TOC sheet
topLeftCell.setFormula(hyperlinkTOC);
topLeftCell.getFormat().getFont().setColor("#00FF00");
newTocSheet.getRange(`D${currentRow}`).setValue("Aap");
}
currentRow++;
}
}
// Step 6: Hide the gridlines on the "TOC" sheet.
newTocSheet.setShowGridlines(false);
}
Nice! Thanks for sharing.
Does automate exist in 2016?
No. 365 only.
Hello Greetings from Pakistan. I have tried using chat GPT, But I am using Excel Older Version i.e Excel 2010. Can you guide me that how can i use the same way using chat GPT Code for creating TOC - Tabel of Contents in to excel 2010.
Unfortunately, you can't get Office Scripts with Excel 2010. See the video description for the list of licenses it's available in.