Originally published at: Import hyperlinks into your spreadsheet with ONLYOFFICE macro | ONLYOFFICE Blog
Hyperlinks can greatly enhance the aesthetics and functionality of your spreadsheets, making it effortless to access crucial resources within your documents. In this blog post, we’ll guide you step by step in crafting a macro that imports hyperlinks by extracting link data from another spreadsheet.
Building the macro
First we initialize two variables oWorksheetA and oWorksheetB, representing worksheets named Sheet1 and Sheet2 in the document. Also we create two empty arrays titles and links that will be used to store data from Sheet1:
var oWorksheetA = Api.GetSheet("Sheet1");
var oWorksheetB = Api.GetSheet("Sheet2");
var rowIndex = 0;
var titles = [];
var links = [];
A while loop then iterate through rows in Sheet1 (up to 10 rows in this case). Inside the loop, it retrieves the values from the cells in columns A and B, assuming that column A contains titles and column B contains links. These values are then stored in the titles and links arrays for later use:
while (rowIndex < 10) {
var titleCell = oWorksheetA.GetRangeByNumber(rowIndex, 0); // Assuming title is in column A
var linkCell = oWorksheetA.GetRangeByNumber(rowIndex, 1); // Assuming link is in column B
var title = titleCell.GetValue();
var link = linkCell.GetValue();
titles.push(title); // Store titles in an array
links.push(link); // Store links in an array
rowIndex++; // Increment the row index for the next iteration
}
Then we target the selected range in Sheet2 (oWorksheetB) and iterate through each cell in the selected range using the ForEach method. For each cell, it retrieves the cell’s value and checks if that value matches any of the titles stored in the titles array:
var rangeB = Api.GetSelection();
rangeB.ForEach(function (cell) {
var cellValue = cell.GetValue();
// Check if the cell value matches any of the titles from the array
var index = titles.indexOf(cellValue);
});
If a match is found, it retrieves the corresponding title and link from the titles and links arrays. It also gets the address of the current cell in Sheet2 using cell.GetAddress. Finally, it sets a hyperlink in Sheet2 using the retrieved title and link:
if (index !== -1) {
var title = titles[index];
var link = links[index];
var address = cell.GetAddress(true, true, "xlA1", false);
// Set the hyperlink in oWorksheetB
oWorksheetB.SetHyperlink(address, link, "Api ONLYOFFICE", title);
}
The entire macro is the following:
var oWorksheetA = Api.GetSheet("Sheet1");
var oWorksheetB = Api.GetSheet("Sheet2");
var rowIndex = 0;
var titles = [];
var links = [];
while (rowIndex < 10) {
var titleCell = oWorksheetA.GetRangeByNumber(rowIndex, 0); // Assuming title is in column A
var linkCell = oWorksheetA.GetRangeByNumber(rowIndex, 1); // Assuming link is in column B
var title = titleCell.GetValue();
var link = linkCell.GetValue();
titles.push(title); // Store titles in an array
links.push(link); // Store links in an array
rowIndex++; // Increment the row index for the next iteration
}
var rangeB = Api.GetSelection();
rangeB.ForEach(function (cell) {
var cellValue = cell.GetValue();
// Check if the cell value matches any of the titles from the array
var index = titles.indexOf(cellValue);
if (index !== -1) {
var title = titles[index];
var link = links[index];
var address = cell.GetAddress(true, true, "xlA1", false);
// Set the hyperlink in oWorksheetB
oWorksheetB.SetHyperlink(address, link, "Your Description", title);
}
})
Let’s run our macro and see how it works!
We genuinely hope this macro proves to be a valuable asset in your toolkit. By embracing ONLYOFFICE macros, you empower yourself with enhanced productivity and efficient, automated solutions.
As you immerse yourself in macro creation, don’t miss out on the opportunities presented by the ONLYOFFICE API. If you have questions or innovative ideas, we invite you to share them with us, whether through comments or direct communication. We eagerly welcome your input and look forward to the possibility of collaborating with you. Best of luck in your exploratory endeavors!