Forked from jakobpn/Hyperlinks in a table.EXCEL.yaml
Created
January 9, 2021 02:45
-
-
Save sweetcard/8c88366496e76809b0f45e666fd81f60 to your computer and use it in GitHub Desktop.
Add hyperlinks to a column in a table.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
name: Hyperlinks in a table | |
description: Add hyperlinks to a column in a table. | |
host: EXCEL | |
api_set: {} | |
script: | |
content: | | |
$("#setup").click(() => tryCatch(setup)); | |
$("#add-hyperlinks").click(() => tryCatch(addHyperlinks)); | |
async function addHyperlinks() { | |
await Excel.run(async (context) => { | |
const sheet = context.workbook.worksheets.getItem("Sample"); | |
const table = sheet.tables.getItem("Expenses"); | |
const merchants = table.getDataBodyRange().getColumn(1).load('values'); | |
const links = table.getDataBodyRange().getColumn(2).load('values'); | |
await context.sync(); | |
for (var row = 0; row < merchants.values.length; row++) { | |
merchants.getCell(row, 0).hyperlink = { | |
textToDisplay: merchants.values[row][0], | |
address: links.values[row][0] | |
}; | |
} | |
await context.sync(); | |
}); | |
} | |
async function setup() { | |
await Excel.run(async (context) => { | |
await OfficeHelpers.ExcelUtilities.forceCreateSheet(context.workbook, "Sample"); | |
const sheet = context.workbook.worksheets.getItem("Sample"); | |
const expensesTable = sheet.tables.add("A1:E1", true /*hasHeaders*/); | |
expensesTable.name = "Expenses"; | |
expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Link", "Category", "Amount"]]; | |
expensesTable.rows.add(null /*add at the end*/, [ | |
["1/1/2017", "The Phone Company", "http://www.thephonecompany.com", "Communications", "$120"], | |
["1/2/2017", "Northwind Electric Cars", "http://www.northwindelectriccars.com", "Transportation", "$142"], | |
["1/5/2017", "Best For You Organics Company", "http://bestorganics.com", "Groceries", "$27"], | |
["1/10/2017", "Coho Vineyard", "http://www.cohovineyard.com", "Restaurant", "$33"], | |
["1/11/2017", "Bellows College", "http://bellowscollege.edu", "Education", "$350"], | |
["1/15/2017", "Trey Research", "http://treyresearch.com", "Other", "$135"], | |
["1/15/2017", "Best For You Organics Company", "http://bestorganics.com", "Groceries", "$97"] | |
]); | |
if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) { | |
sheet.getUsedRange().format.autofitColumns(); | |
sheet.getUsedRange().format.autofitRows(); | |
} | |
sheet.activate(); | |
await context.sync(); | |
}); | |
} | |
/** Default helper for invoking an action and handling errors. */ | |
async function tryCatch(callback) { | |
try { | |
await callback(); | |
} catch (error) { | |
// Note: In a production add-in, you'd want to notify the user through your add-in's UI. | |
console.error(error); | |
} | |
} | |
language: typescript | |
template: | |
content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to dd hyperlinks to a column in a table.</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Setup</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Create table</span>\n </button>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<button id=\"add-hyperlinks\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Add hyperlinks</span>\n </button>\n</section>" | |
language: html | |
style: | |
content: | | |
section.samples { | |
margin-top: 20px; | |
} | |
section.samples .ms-Button, section.setup .ms-Button { | |
display: block; | |
margin-bottom: 5px; | |
margin-left: 20px; | |
min-width: 80px; | |
} | |
language: css | |
libraries: | | |
https://appsforoffice.microsoft.com/lib/1/hosted/office.js | |
@types/office-js | |
office-ui-fabric-js@1.4.0/dist/css/fabric.min.css | |
office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css | |
core-js@2.4.1/client/core.min.js | |
@types/core-js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts | |
jquery@3.1.1 | |
@types/jquery@3.3.1 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment