Looking to find if there is a way to pause or sleep macro execution to provide feedback to the user about how the macro execution is doing. Meaning, if I set a value in sheet 15 and then set a value in sheet 4, can I add a sleep or pause for 2 seconds between macro actions to give the user some sort of message in the spreadsheet of how far the macro has progressed? Right now, the Macro completes in seconds without the user seeing anything happening.
Hello @qualm
The point of macros is to be fast. Technically, you can pause execution with regular setTimeout() or setInterval() methods, but that way you will need to separate execution of the macro in several parts or loop them. Additionally, first part of the macro will be rendered in the spreadsheet, but the second one will be available after another render, because timeouts, even though working, delay all further changes made by macros and thus they are not rendered. Workaround here is to use recalculation methods, but it is important to understand that all additional recalculations take time, so you might get a result, where execution of macro increases in time. Here is an example:
let func1 = function () {
let spread = Api.GetActiveSheet();
let range1 = spread.GetRange("G2");
range1.SetValue("test");
};
let func2 = function () {
alert('executing');
let spread = Api.GetActiveSheet();
let range2 = spread.GetRange("H2");
range2.SetValue("test2");
Api.asc_calculate(Asc.c_oAscCalculateType.All); // recalculating all to display second change
};
func1(); // calling first change
setTimeout(func2, 1000); // calling second change and recalculating after 1 second
Another problem here is that you cannot run alerts from macro directly (see this: Message box with macro) . Basically, with this approach you will increase execution time of your macro. It also depends on the complexity of the macro in general.
You can also check out this reply on promises usage: Macros: SetTimeout() value problem - #5 by Constantine
1 Like
Thank you very much @Constantine. That is very helpful.
1 Like
I’m glad to know. Should I close the topic?
Not quite, I am trying to figure out how to add additional functions with the sleep in between functions. This doesn’t appear to work to first perform function 1, then 2, then 3, then 4 with 2 seconds of delay between functions. Can you help me understand why that is?
(function()
{
let func1 = function () {
let spread1 = Api.GetActiveSheet();
let range1 = spread1.GetRange("G2");
range1.SetValue("test");
};
let func2 = function () {
alert('executing');
let spread2 = Api.GetActiveSheet();
let range2 = spread2.GetRange("H2");
range2.SetValue("test2");
Api.asc_calculate(Asc.c_oAscCalculateType.All); // recalculating all to display second change
};
let func3 = function () {
alert('executing');
let spread3 = Api.GetActiveSheet();
let range3 = spread3.GetRange("G2");
range3.SetValue("");
Api.asc_calculate(Asc.c_oAscCalculateType.All); // recalculating all to display second change
};
let func4 = function () {
alert('executing');
let spread4 = Api.GetActiveSheet();
let range4 = spread4.GetRange("H2");
range4.SetValue("");
Api.asc_calculate(Asc.c_oAscCalculateType.All); // recalculating all to display second change
};
func1(); // calling first change
setTimeout(func2, 2000); // calling another change after sleep
setTimeout(func3, 2000); // calling another change after sleep
setTimeout(func4, 2000); // calling another change after sleep
})();
This would require using sequential timeouts. You see, in your example func1 is executed, then all other functions are triggered after 2 seconds as you are setting up individual timeouts for each function. That way starting point is execution of func1 - all other functions are triggered at the same time after 2 seconds.
With sequential timeouts you can set up a timer for each function basing on previous trigger. That way each function will have its out timer and will be trigger one after another. Easiest way to demonstrate that is:
func1(); // triggers first function
setTimeout(() => { // sets timeout of 2 seconds after first function
func2(); // calls second function after 2 seconds
setTimeout(() => { // sets another 2 seconds timeout after previous function
func3(); // calls third function after another 2 seconds
setTimeout(() => { // sets last timeout of 2 seconds
func4(); // calls last function
}, 2000); // here an below actual time for time out is set
}, 2000);
}, 2000);
@Constantine Is there a way to make a SetTimeout function? Such that when triggering that function, it would start a 2 second timer every time it is triggered? The solution that is being proposed could get way out of hand when the project that I am working on could contain 50+ functions if the only way to pause the script is to wrap each function within the setTimeout branch of code.
What I was thinking was having setTimeout trigger an empty function and every time it is triggered, it would put a 2 second delay after each function trigger. Something like this code below, although the script seems to be ignoring the 2 second wait, as it executes both func1 and func2 all at once.
let EmptyFunction1 = function () {
};
let EmptyFunction2 = function () {
alert('executing');
Api.asc_calculate(Asc.c_oAscCalculateType.All); // recalculating
setTimeout(EmptyFunction1, 2000); //sleep for 2 seconds
};
func1(); // calling first change
EmptyFunction2(); // Sleeping for a bit
func2(); // calling next change
In your example, the timeout is set inside a function, so it works as expected. I am not quite following what you’d like to achieve in general. setTimeout is a generic JS method and it depends on the context it is called from. To have better understanding of what is getting called and in which order you can simply add some console.log() lines and check browser console during the execution (open console with F12 in your browser). For instance:
let EmptyFunction1 = function () {
console.log("called empty func")
};
let EmptyFunction2 = function () {
alert('executing');
Api.asc_calculate(Asc.c_oAscCalculateType.All); // recalculating
setTimeout(EmptyFunction1, 2000); //sleep for 2 seconds
console.log("called func2");
};
EmptyFunction1(); // calling first change
EmptyFunction2(); // Sleeping for a bit
EmptyFunction1(); // calling next change
This sample does exactly what it is supposed to do:
- JS initiates the call with all three functions;
- During execution of
EmptyFunction2function timeout is set; - Once all other functions are triggered synchronously, with 2 seconds delay your are calling
EmptyFunction1one more time.
In total: you have 4 calls, 3 of which are EmptyFunction1.
@Constantine Here is my full test code that hopefully provides more information as to what I am trying to accomplish.
(function()
{
let func1 = function () {
let spread = Api.GetActiveSheet();
let range1 = spread.GetRange("G2");
range1.SetValue("test");
console.log("called function 1")
};
let func2 = function () {
//alert('executing');
let spread = Api.GetActiveSheet();
let range2 = spread.GetRange("H2");
range2.SetValue("test2");
Api.asc_calculate(Asc.c_oAscCalculateType.All); // recalculating
console.log("called function 2")
};
let EmptyFunction1 = function () {
console.log("called empty function 1")
};
let EmptyFunction2 = function () {
alert('executing');
Api.asc_calculate(Asc.c_oAscCalculateType.All); // recalculating
setTimeout(EmptyFunction1, 2000);
//console.log("called empty function 2")
};
func1(); // calling first change
EmptyFunction2(); // Sleeping for a bit attempt 1 by calling empty function 2 which should add 2 seconds after calling empty function 1
setTimeout(EmptyFunction1, 2000); // Sleeping for a bit attempt 2 by setting timeout outside the function and ignoring empty function 2 and just calling empty function 1 and asking for a sleep of 2 seconds before calling funct2 on the next line
func2(); // calling next change
})();
I am trying to understand why the result of both attempts to sleep the script for 2 seconds is being ignored and both funct 1 and funct2 are being ran right away when the script is launched.
How can I create a function that can be triggered at any time to sleep the script for 2 seconds between function calls? That is what I am trying to accomplish.
Your example works as it should, because it does not use sequential timeouts. Basically, you are executing everything in a single call, so this call is a reference point for your timeouts - this means that, upon running a macro, func1, EmptyFunction2 and func2 are triggered synchronously, i.e. at the same time, and then timeouts begin to clock and function EmptyFunction1 is called two times (directly and from EmptyFunction2) with 2 seconds delay.
Creating a function with timeout won’t solve the problem, because timeouts are set upon triggering and counted from the moment it is triggered. Solution here is to use sequential timeouts or async functions and promises. Second solution is more complicated and better, but it purely relies on your JavaScript skills.
@Constantine Thank you for that information. I was able to adapt this option from this website to achieve the result that I was looking to accomplish.
You are correct, using promise was important combined with the setTimeout function. Then, using the async function call as my main script body, I am able to control the script flow with different lengths of the sleep function after different macro changes.
We can close out this topic.
https://www.sitepoint.com/delay-sleep-pause-wait/
function sleep(ms) {
return new Promise(resolve => setTimeout(resolve, ms));
}
async function delayedGreeting() {
console.log('Hello');
await sleep(2000);
console.log('World!');
await sleep(2000);
console.log('Goodbye!');
}
delayedGreeting();
1 Like
Glad to know that it helped.