I am working on a project that would greatly benefit from referencing function variables that change depending on the context of when those variables are called. For the script below, I am trying to figure out how to change which cell is being copied when running the CopyDataTest() function within the main function.
I have tried to define that variable in the main function and then call the CopyDataTest() function within the main function but that doesn’t appear to work.
This code below works great if I comment out line 39, but how do I get the CopyDataTest() function to use the value of var RangeSelect within the main function?
(function()
{
// Access the worksheets
var sheet1 = Api.GetSheet("Sheet1");
var sheet2 = Api.GetSheet("Sheet2");
//Sleep function
function sleep(ms) {
return new Promise(resolve => setTimeout(resolve, ms));
}
//Recalculate whole workbook again with all changes
function RecalculateWorkbook() {
Api.asc_calculate(Asc.c_oAscCalculateType.All);
}
function CopyDataTest() {
//Get Data from Sheet 1
var CopiedValue = sheet1.GetRange(RangeSelect).GetValue();
//Paste data into Sheet 1
sheet1.GetRange("B14").SetValue(CopiedValue);
}
//*******************************************************************
//Function: Main
//*******************************************************************
async function MainFunction() {
//Start Message
console.log('Greetings!');
//Declare variables for Copy Data Test
var RangeSelect = sheet1.GetRange("A2");
//Copy Data Test
CopyDataTest()
//Reclaculate Workbook
RecalculateWorkbook()
//Wait 5 seconds
await sleep(5000);
//End Message
console.log('Goodbye!');
}
MainFunction();
})();```
Hello @qualm
I’ve tried to reproduce the situation with the provided code via the connector class, but failed. It seems I’m missing something.
Could you please provide a test file where this code can be executed? As far as I understand, the main point of the scenario is copying data from cell to another one.
If I misunderstood the scenario, please clarify it.
@Alexandre Thanks for helping me look into this. Yes, this is a simple copying of data from cell A 2 to cell B14, but the RangeSelect variable is not defined in the CopyDataTest function, instead it is setup to reference the cell that is defined under RangeSelect under the main function.
I can very easily perform this macro if I explicitly define the cell to copy by changing line 20 of the macro code to the following:
var CopiedValue = sheet1.GetRange("A2").GetValue();
But, how do I setup the javascript so that I can define the GetRange cell under the main function?
This code is simply a proof of concept, I plan to use this idea for a much larger project and knowing how to define variables in one function and then reference another function with that variable definition will greatly help to simplify and improve efficiency. Otherwise, I would be repeating 20+ lines of code, with minor variations for different cell references.
(function()
{
var sheet1 = Api.GetSheet("Sheet1");
var sheet2 = Api.GetSheet("Sheet2");
var RangeSelect;
function sleep(ms) {
return new Promise(resolve => setTimeout(resolve, ms));
}
function RecalculateWorkbook() {
Api.asc_calculate(Asc.c_oAscCalculateType.All);
}
function CopyDataTest() {
var CopiedValue = sheet1.GetRange(RangeSelect).GetValue();
sheet1.GetRange("B14").SetValue(CopiedValue);
}
async function MainFunction() {
console.log('Greetings!');
RangeSelect = "A2";
CopyDataTest();
RecalculateWorkbook();
await sleep(5000);
console.log('Goodbye!');
}
MainFunction();
})();
I’ve run a few tests, so far it seems to work this way. The main difference with your macro is how the variable is declared for Copy Data Test. If I misunderstood the request, please clarify it.