Originally published at: Use ONLYOFFICE macros to copy columns into separate spreadsheets | ONLYOFFICE Blog
ONLYOFFICE macros open up endless possibilities, transforming complex tasks that would take hours into processes completed with a single click. Recently, we received a request from an ONLYOFFICE user for a macro that could automate their workflow by copying data efficiently.
This article dives into the specifics of that request, detailing how we developed a macro to meet their needs and exploring the broader implications of ONLYOFFICE macros.
Building the macro
const oWorksheet = Api.GetActiveSheet(); const oRange = oWorksheet.GetSelection();
Starting off, we get the active worksheet and the selection in the oWorksheet and oRange variables.
Now, we will go through the 3 functions that make up this macro, namely: getColHeader(), copyColumns() and copyValues().
First up, the getColHeader() function.
const getColHeader = (colNumber) => {
const targetColumn = oRange.GetCols(colNumber);
const colHeader = targetColumn.GetValue()[0][0];
return colHeader !== null && colHeader !== "" ? colHeader : -1;
};
The getColHeader() function takes a single parameter, colNumber, which specifies the column number. This function checks for a valid value in the first row of the specified column. If a valid value exists, it returns that value; otherwise, it returns -1.
Next up, we will see how the copyColumns() function works.
const copyColumns = () => {
let end = false;
let i = 2;
while (!end) {
i++;
const sheetName = getColHeader(i);
if (sheetName === -1) {
end = true;
break;
} else if (sheetName) {
const columns = oRange.GetCols(i);
columns.ForEach((cell) => {
copyValues(cell, sheetName, 1);
});
for (let j = 1; j <= 2; j++) {
const col = oRange.GetCols(j);
col.ForEach((cell) => {
copyValues(cell, sheetName);
});
}
} else {
console.error("Fatal error in copy horizontal logic.");
end = true;
break;
}
}
};
The copyColumns() function iterates through the columns of the current worksheet, starting from the third column. For each column, it retrieves the column header using the getColHeader() function. If the header is valid, it copies the values and formatting from that column to the target sheet specified by the header, using the copyValues() function.
Additionally, it ensures that the first two columns, which are considered common columns, are also copied to every target sheet. The process continues until an invalid header is encountered, at which point the loop terminates. This is the main function of the macro and calls other helper functions.
Note: In this code, we assume there are two common columns, and the specific columns to be copied separately into each sheet start from the third column onward. These values can be adjusted depending on your specific use case.
Now, the copyValues() function.
const copyValues = (range, sheetName, copyType = 0) => {
The copyValues() function copies the values and formatting from the source range to the target sheet. It takes three parameters: range, which is the range object containing the cell data, sheetName, which is the name of the target sheet, and copyType, which specifies if the column to be copied is a default column or a specific column.
const oValue = range.GetValue();
const oCharacters = range.GetCharacters(0, 2);
const oFont = oCharacters.GetFont();
const fontName = oFont.GetName();
const oSize = oFont.GetSize();
const isBold = oFont.GetBold();
const isItalic = oFont.GetItalic();
const rowNo = range.GetRow() - 1;
let colNo;
First, the function retrieves the value and formatting (font name, size, bold, italic) of the source cell.
const targetSheet = Api.GetSheet(sheetName);if (!targetSheet) { console.error(`Sheet with name ${sheetName} not found.`); return; } if (copyType === 1) { colNo = 2; } else { colNo = range.GetCol() - 1; }
Next, the target sheet is retrieved using the sheetName variable, obtained from the getColHeader() function.
If the target sheet does not exist, an error is logged, and the macro execution is aborted.
if (oValue === null || oValue === "") {
targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(" ");
} else {
oFont.SetName(fontName);
targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(oValue);
targetSheet.GetRangeByNumber(rowNo, colNo).SetFontName(fontName);
targetSheet.GetRangeByNumber(rowNo, colNo).SetFontSize(oSize);
if (isBold) {
targetSheet.GetRangeByNumber(rowNo, colNo).SetBold(true);
}
if (isItalic) {
targetSheet.GetRangeByNumber(rowNo, colNo).SetItalic(true);
}
}
};
Lastly, if everything is alright, and no errors occur, the copyValues() function sets the value and formatting in the corresponding cell of the target sheet.
copyColumns();
Finally, we end the macro by invoking the copyColumns() function, which is the entry point into the macro.
The full macro code
Here is the code for the entire macro:
(function () {
const oWorksheet = Api.GetActiveSheet();
const oRange = oWorksheet.GetSelection();
const getColHeader = (colNumber) => {
const targetColumn = oRange.GetCols(colNumber);
const colHeader = targetColumn.GetValue()[0][0];
return colHeader !== null && colHeader !== "" ? colHeader : -1;
};
const copyColumns = () => {
let end = false;
let i = 2;
while (!end) {
i++;
const sheetName = getColHeader(i);
if (sheetName === -1) {
end = true;
break;
} else if (sheetName) {
const columns = oRange.GetCols(i);
columns.ForEach((cell) => {
copyValues(cell, sheetName, 1);
});
// Copy the common rows in every sheet.
for (let j = 1; j <= 2; j++) {
const col = oRange.GetCols(j);
col.ForEach((cell) => {
copyValues(cell, sheetName);
});
}
} else {
console.error("Fatal error in copy horizontal logic.");
end = true;
break;
}
}
};
const copyValues = (range, sheetName, copyType = 0) => {
const oValue = range.GetValue();
const oCharacters = range.GetCharacters(0, 2);
const oFont = oCharacters.GetFont();
const fontName = oFont.GetName();
const oSize = oFont.GetSize();
const isBold = oFont.GetBold();
const isItalic = oFont.GetItalic();
const rowNo = range.GetRow() - 1;
let colNo;
const targetSheet = Api.GetSheet(sheetName);
if (!targetSheet) {
console.error(`Sheet with name ${sheetName} not found.`);
return;
}
if (copyType === 1) {
colNo = 2;
} else {
colNo = range.GetCol() - 1;
}
if (oValue === null || oValue === "") {
targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(" ");
} else {
oFont.SetName(fontName);
targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(oValue);
targetSheet.GetRangeByNumber(rowNo, colNo).SetFontName(fontName);
targetSheet.GetRangeByNumber(rowNo, colNo).SetFontSize(oSize);
if (isBold) {
targetSheet.GetRangeByNumber(rowNo, colNo).SetBold(true);
}
if (isItalic) {
targetSheet.GetRangeByNumber(rowNo, colNo).SetItalic(true);
}
}
};
copyColumns();
})();
Now, let’s see how our macro works!
That’s it! We just demonstrated how even the most complex functionalities can be automated using ONLYOFFICE macros. The ONLYOFFICE API is a powerful tool, capable of performing a wide range of tasks and providing immense potential for developing even more advanced macros and plugins. With this API, users can harness the full power of ONLYOFFICE to enhance their productivity and streamline their workflows.
If you have any questions or innovative concepts, we encourage you to share them with us. We value your input and look forward to collaborating with you. Best of luck in your exploratory endeavors!
Useful links
Use ONLYOFFICE macro to trim spreadsheet data
How to insert YouTube search results into your spreadsheet using macros