• ‘A beginner's guide to automating in Excel using Office Scripts’ webinar recording is now available.

The question

Where, in your experience, do scripts save the most time in a Finance Dept?

Answer

It really depends on the processes and how they are engineered rather than the type of work.

We have a client where one Office Script and Power Automate flow (which took approx. 60 minutes to set up) saved 11% of that person’s total working time for the year. The payback period for that project was less than 2 days.

Most finance departments are not data/process driven and therefore have developed very inefficient workflows. Even the same task can vary in complexity across different organizations. So, the starting point has to be looking at the processes that are inefficient, time-consuming, and labour-intensive to determine where automation can deliver the most benefits.

The question

Are office scripts more efficient than power query when combining multiple files and manipulating the consolidated data?

Answer

Power Query is designed specifically for that purpose, therefore that would usually be the tool to use for those scenarios. However, if you want the processing to run based on a specific time or event trigger, then Office Scripts might be a better option. Sadly, Power Query is not currently supported by Office Scripts, but we hope this will change soon as that will deliver some huge benefits to data processing.

The question

What’s VBA?

Answer

VBA (Visual Basic for Applications) is the language in which Excel Macros are created. It has traditionally been the most popular language for automating tasks in Excel. While Office Scripts are a new scripting language for automating Excel, they are unlikely to replace VBA anytime soon, though they do offer some advantages. You can read more about the differences in this article – “Office Scripts vs VBA: What’s the difference?”.

The question

Do Office Scripts present a lower security risk vs VBA in your opinion?

Answer

In short, yes. VBA has the potential to access your entire local network, make changes to files on your machine, and download and execute files from the internet. Therefore, it is very high risk – this is why the default setting in Excel is to disable macros unless they are from trusted sources. Office Scripts on the other hand does not have access to your IT infrastructure and cannot download files. So, they have minimal security risk.

The question

Do Office Scripts have Message Boxes and Input Boxes?

Answer

Not in the same way as VBA.

There is the generic parameter input box, when running any script which requires inputs. You can also build worksheet-based interfaces, similar to using VBA. But there no separate mechanism for message boxes and input boxes in the same way as VBA.

The question

I get an error saying "We weren't able to save your script. Please try again." Is this due to an organisational setting set by our IT team?

Answer

Internet access is required to run any Office Script. The most likely cause is your internet has dropped out at some point, and therefore your Excel and SharePoint / OneDrive connection was broken.

The question

Can you use office script to break circularity problems like how project finance / LBO modellers do it with VBAs?

Answer

Office Scripts do not currently have worksheet events to trigger re-calculation. So, while you could write a script to do that, it would not be triggered automatically; you would need to run the script separately.

In addition, if you wanted to share the model with an external client, you would want the code in the workbook so that it is complete package which operates by itself. While Office Scripts can be “linked” to workbooks, sharing with external users is not currently supported. Therefore, VBA continues to be the best option for this.

The question

Can you explain the sharing of scripts? They have workbook scope, but are stored linked to your organisation, so wouldn't be transferred with the file if sent outside your organisation?

Answer

Correct. Office Scripts are separate files saved on OneDrive/SharePoint. We can “link” a script with a workbook which will give users inside the organization the ability to run the script if the workbook is shared with them.

Office Scripts are intended for an internal organizational Microsoft 365 environment. The scripts are saved as separate files. Therefore, external clients will not have access to the scripts and will not be able to run the script, even if a workbook is shared with them.

The question

Can you choose where in the sheet a chart lands and write this into the script?

Answer

Yes, you can.

This question was also referenced in the webinar, but the demonstration didn’t work correctly because I forgot to click Save before trying to run the code. I tested the code after the webinar, and it worked perfectly. You can find the webinar and the resources with the code here.

The question

I noticed that trying to assign the chart called “MyChart” to a variable did not produce an error when the chart didn’t already exist. Could you explain why that’s the case?

Answer

If you look in the Office Scripts API for the getChart method, it says:

“Gets a chart using its name. If there are multiple charts with the same name, the first one will be returned. If the chart doesn't exist, then this method returns undefined.”

Undefined is a specific property in JavaScript that is often used to help with error handling but is not in itself an error.

The question

How did you start up Power Automate and what do you need to access Power Automate? E.g. is it free with Personal or Business Office 365 licenses?

Answer

Power Automate is an application available from the menu of the Office 365 online platform. More information on Power Automate and licensing options can be found in Microsoft documentation.

The question

Can Power Automate work with non-Office applications as well e.g. accounting systems?

Answer

Power Automate has an extensive range of supported connectors. In short, if the system vendor has provided APIs to work with Power Automate, or the system uses a cloud-based SQL environment, then yes.

If you have a Power Automate premium licence, you can trigger Power Automate Desktop RPA flows to control the user interface on a local PC (which would include the ability to connect to locally hosted systems and data sources). But it can be very complex to set-up and manage.

The question

After the Power Automate flow runs, will the excel file be left with the value (region) for whatever was the last region in the table which was cycled through? (i.e. the changes made to the file by power automate are permanent)?

Answer

Yes. But you could set-up your flow to leave the workbook in whatever state you wish.

Or you could set up the flow to:

• Create a copy of the workbook within the flow
• Cycle through the options on the copied file
• Delete the copy

The question

Can office script help append a .csv file to another so that power automate can do something further on the bigger table (after power automate converts the .csv to a table)?

Answer

If you want to append CSV files for Power Automate to use, then you can achieve this in Power Automate, without the use of Office Scripts.

The question

If someone changes the name of the folder in SharePoint where your office scripts are saved, will it break them being used in the Excel files with the buttons that use the script?

Answer

The file saved in SharePoint are not referenced by name, but by a separate ID. Therefore, even if you rename, move, or change the name of a parent folder of an Office Script, it will still continue to work correctly.

The question

Are changes made by Office Script saved into the workbook history? (e.g. for your final example, would recipients see the other (deleted) cost center contents in the history?)

Answer

Yes and no. Changes made by scripts do not show up in the Undo history, but you can use the Version History functionality in Excel to track the changes and revert if needed.

In the final example in the webinar, as you are emailing a copy of the file, the change history doesn’t exist on the new file. So, you wouldn’t be sharing confidential information.

The question

Can you create an Add-in within Office Scripts?

Answer

No, however, Office Scripts are a simplified version of Office.JS which is specifically designed for building Add-ins.

The question

What happens if there is an error in the script, will a file with errors be emailed out or do you get an alert?

Answer

Assuming this is in relation to running Office Scripts from Power Automate, then yes, you get a notification if a flow fails. You can also build flows to include error handling and take alternative actions if there is an error.

The question

For office script, is it possible to interact with different file saved in OneDrive? As I used macro, it can open different file automatically and copy and paste data in between.

Answer

Office Scripts are workbook bound, so by themselves, no.

But Office Scripts and Power Automate together, yes, you can interact with many different files.

The question

Do office scripts also integrate with google sheets?

Answer

No. Google Sheets has a separate language called Apps Script for automating Google Sheets and other applications within the Google suite. Google Apps Script and Office Scripts are both based on JavaScript, but they are fundamentally different solutions.

The question

Is there a lexicon of instructions / commands?

Answer

The full Office Script Document Object Model is built into the interface, so you can easily access the documentation.

There is also the Office Scripts API reference from Microsoft.

The question

Can you recommend some resources for further learning?

Answer

There is some introductory information and example codes provided by Microsoft.

Otherwise, there are various small and partial resources all over the internet. So, make use of Google and start investigating.

Mark also has his own Office Scripts course which goes into detail, which you can find here.