Last active
February 8, 2018 01:02
-
-
Save MIchaelMainer/23b9fdd55f011a1718a0451709996e34 to your computer and use it in GitHub Desktop.
Set and get values and formulas for a range - Shared with Script Lab
This file contains hidden or 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: Set and get values - 1 | |
| description: Set and get values and formulas for a range | |
| author: MIchaelMainer | |
| host: EXCEL | |
| api_set: {} | |
| script: | |
| content: | | |
| $("#setup").click(setup); | |
| $("#set-value").click(setValue); | |
| $("#set-values").click(setValues); | |
| $("#set-formula").click(setFormula); | |
| $("#set-formulas").click(setFormulas); | |
| $("#set-formulas-r1c1").click(setFormulasR1C1); | |
| $("#get-values").click(getValues); | |
| $("#get-texts").click(getTexts); | |
| $("#get-formulas").click(getFormulas); | |
| $("#set-migrated").click(setMigrated); | |
| $("#set-idInDesc").click(setOldIdInDescription); | |
| // Used to add the old work item id into the description for many rows. | |
| // Assumptions: | |
| // * Selection is in the target description cells. | |
| // * The id is in the A column. | |
| // * The iteration path is in the H column | |
| async function setOldIdInDescriptionMultiRow() { | |
| try { | |
| await Excel.run(async (context) => { | |
| // Load the selected range. This should be a description cell. | |
| const selectedRange = context.workbook.getSelectedRange(); | |
| selectedRange.load("values, address"); | |
| await context.sync(); | |
| // 'SheetName!B14' Get the address so that we can get the workItemId. We are assuming a single column identifier | |
| var address = selectedRange.address; | |
| // 'B14' | |
| var addressStartIndex = address.lastIndexOf("!") + 1; | |
| address = address.substr(addressStartIndex); | |
| // '14' Get the row identifier. | |
| var workItemIdAddressRow = address.substr(1); | |
| // 'A14' Get the address for the work item id column. Assuming that it is column A. | |
| var workItemIdAddress = "A" + workItemIdAddressRow; | |
| // Get and load the work item identifier. | |
| var activeWorksheet = context.workbook.worksheets.getActiveWorksheet(); | |
| var rangeOfWorkItemId = activeWorksheet.getRange(workItemIdAddress); | |
| rangeOfWorkItemId.load("values"); | |
| await context.sync(); | |
| // Now we have the work item identifier | |
| var workItemIdValue = rangeOfWorkItemId.values[0][0]; | |
| // 'H14' Get the address for the iteration path. We will want to save this too. | |
| var iterationPathAddress = "H" + workItemIdAddressRow; | |
| // Get and load the old iteration path. | |
| var rangeOfIterationPath = activeWorksheet.getRange(iterationPathAddress); | |
| rangeOfIterationPath.load("values"); | |
| await context.sync(); | |
| // Now we have the old iteration path. | |
| var oldIterationPathValue = rangeOfIterationPath.values[0][0]; | |
| // Get the value of Description column. | |
| var selectedRangeValue = selectedRange.values[0][0]; | |
| selectedRangeValue = selectedRangeValue.trim(); | |
| // Update the Description column with the old work item identifier and the old iteration path. | |
| selectedRange.values = [[selectedRangeValue + "\n" + "https://office.visualstudio.com/OC/DevX_DXTC/_workitems?id=" + workItemIdValue + "&_a=edit" + "\nOld iteration path: " + oldIterationPathValue]]; | |
| await context.sync(); | |
| }); | |
| } | |
| catch (error) { | |
| OfficeHelpers.UI.notify(error); | |
| OfficeHelpers.Utilities.log(error); | |
| } | |
| } | |
| // Used to add the old work item id into the description for a single row. | |
| // Assumptions: | |
| // * Cursor is in the target description cell. | |
| // * The id is in the A column. | |
| async function setOldIdInDescription() { | |
| try { | |
| await Excel.run(async (context) => { | |
| // Load the selected range. This should be a description cell. | |
| const selectedRange = context.workbook.getSelectedRange(); | |
| selectedRange.load("values, address"); | |
| await context.sync(); | |
| // 'SheetName!B14' Get the address so that we can get the workItemId. We are assuming a single column identifier | |
| var address = selectedRange.address; | |
| // 'B14' | |
| var addressStartIndex = address.lastIndexOf("!") + 1; | |
| address = address.substr(addressStartIndex); | |
| // '14' Get the row identifier. | |
| var workItemIdAddressRow = address.substr(1); | |
| // 'A14' Get the address for the work item id column. Assuming that it is column A. | |
| var workItemIdAddress = "A" + workItemIdAddressRow; | |
| // Get and load the work item identifier. | |
| var activeWorksheet = context.workbook.worksheets.getActiveWorksheet(); | |
| var rangeOfWorkItemId = activeWorksheet.getRange(workItemIdAddress); | |
| rangeOfWorkItemId.load("values"); | |
| await context.sync(); | |
| // Now we have the work item identifier | |
| var workItemIdValue = rangeOfWorkItemId.values[0][0]; | |
| // 'H14' Get the address for the iteration path. We will want to save this too. | |
| var iterationPathAddress = "H" + workItemIdAddressRow; | |
| // Get and load the old iteration path. | |
| var rangeOfIterationPath = activeWorksheet.getRange(iterationPathAddress); | |
| rangeOfIterationPath.load("values"); | |
| await context.sync(); | |
| // Now we have the old iteration path. | |
| var oldIterationPathValue = rangeOfIterationPath.values[0][0]; | |
| // Get the value of Description column. | |
| var selectedRangeValue = selectedRange.values[0][0]; | |
| selectedRangeValue = selectedRangeValue.trim(); | |
| // Update the Description column with the old work item identifier and the old iteration path. | |
| selectedRange.values = [[selectedRangeValue + "\n" + "https://office.visualstudio.com/OC/DevX_DXTC/_workitems?id=" + workItemIdValue + "&_a=edit" + "\nOld iteration path: " + oldIterationPathValue]]; | |
| await context.sync(); | |
| }); | |
| } | |
| catch (error) { | |
| OfficeHelpers.UI.notify(error); | |
| OfficeHelpers.Utilities.log(error); | |
| } | |
| } | |
| async function setMigrated() { | |
| try { | |
| await Excel.run(async (context) => { | |
| const selectedRange = context.workbook.getSelectedRange(); | |
| selectedRange.load("values"); | |
| await context.sync(); | |
| // [] [] | |
| var selectedRangeValue = selectedRange.values[0][0]; | |
| selectedRangeValue = selectedRangeValue.trim(); | |
| if (selectedRangeValue.indexOf("MigrateReady") !== -1) { | |
| selectedRange.values = [[selectedRangeValue.replace("MigrateReady", "Migrated")]]; | |
| } else { | |
| selectedRange.values = [[selectedRangeValue + "; Migrated"]]; | |
| } | |
| await context.sync(); | |
| }); | |
| } | |
| catch (error) { | |
| OfficeHelpers.UI.notify(error); | |
| OfficeHelpers.Utilities.log(error); | |
| } | |
| } | |
| async function setValue() { | |
| try { | |
| await Excel.run(async (context) => { | |
| // Load the selected range. This should be a tag cell. | |
| const selectedRange = context.workbook.getSelectedRange(); | |
| selectedRange.load("values,address"); | |
| await context.sync(); | |
| // Get the 2D array for the range values. Since we are assuming a single cell selected, | |
| var selectedRangeValue = selectedRange.values[0][0]; | |
| selectedRangeValue = selectedRangeValue.trim(); | |
| // Set the MigrateReady tag. | |
| if (selectedRangeValue.length < 1) { | |
| selectedRange.values = [[selectedRangeValue + "MigrateReady"]]; | |
| } else { | |
| selectedRange.values = [[selectedRangeValue + "; MigrateReady"]]; | |
| } | |
| await context.sync(); | |
| }); | |
| } | |
| catch (error) { | |
| OfficeHelpers.UI.notify(error); | |
| OfficeHelpers.Utilities.log(error); | |
| } | |
| } | |
| async function setValues() { | |
| try { | |
| await Excel.run(async (context) => { | |
| const sheet = context.workbook.worksheets.getItem("Sample"); | |
| const data = [ | |
| ["Potato Chips", 10, 1.80], | |
| ]; | |
| const range = sheet.getRange("B5:D5"); | |
| range.values = data; | |
| range.format.autofitColumns(); | |
| await context.sync(); | |
| }); | |
| } | |
| catch (error) { | |
| OfficeHelpers.UI.notify(error); | |
| OfficeHelpers.Utilities.log(error); | |
| } | |
| } | |
| async function setFormula() { | |
| try { | |
| await Excel.run(async (context) => { | |
| const sheet = context.workbook.worksheets.getItem("Sample"); | |
| const range = sheet.getRange("E3"); | |
| range.formulas = [[ "=C3 * D3" ]]; | |
| range.format.autofitColumns(); | |
| await context.sync(); | |
| }); | |
| } | |
| catch (error) { | |
| OfficeHelpers.UI.notify(error); | |
| OfficeHelpers.Utilities.log(error); | |
| } | |
| } | |
| async function setFormulas() { | |
| try { | |
| await Excel.run(async (context) => { | |
| const sheet = context.workbook.worksheets.getItem("Sample"); | |
| const data = [ | |
| ["Total Price"], | |
| ["=C3 * D3"], | |
| ["=C4 * D4"], | |
| ["=C5 * D5"], | |
| ["=SUM(E3:E5)"] | |
| ]; | |
| const range = sheet.getRange("E2:E6"); | |
| range.formulas = data; | |
| range.format.autofitColumns(); | |
| await context.sync(); | |
| }); | |
| } | |
| catch (error) { | |
| OfficeHelpers.UI.notify(error); | |
| OfficeHelpers.Utilities.log(error); | |
| } | |
| } | |
| async function setFormulasR1C1() { | |
| try { | |
| await Excel.run(async (context) => { | |
| const sheet = context.workbook.worksheets.getItem("Sample"); | |
| const data = [ | |
| ["Total Price"], | |
| ["=R[0]C[-2] * R[0]C[-1]"], | |
| ["=R[0]C[-2] * R[0]C[-1]"], | |
| ["=R[0]C[-2] * R[0]C[-1]"], | |
| ["=SUM(R[-3]C[0]:R[-1]C[0])"] | |
| ]; | |
| const range = sheet.getRange("E2:E6"); | |
| range.formulasR1C1 = data; | |
| range.format.autofitColumns(); | |
| await context.sync(); | |
| }); | |
| } | |
| catch (error) { | |
| OfficeHelpers.UI.notify(error); | |
| OfficeHelpers.Utilities.log(error); | |
| } | |
| } | |
| async function getValues() { | |
| try { | |
| await Excel.run(async (context) => { | |
| const sheet = context.workbook.worksheets.getItem("Sample"); | |
| const range = sheet.getRange("B2:E6"); | |
| range.load("values"); | |
| await context.sync(); | |
| console.log(JSON.stringify(range.values, null, 4)); | |
| }); | |
| } | |
| catch (error) { | |
| OfficeHelpers.UI.notify(error); | |
| OfficeHelpers.Utilities.log(error); | |
| } | |
| } | |
| async function getTexts() { | |
| try { | |
| await Excel.run(async (context) => { | |
| const sheet = context.workbook.worksheets.getItem("Sample"); | |
| const range = sheet.getRange("B2:E6"); | |
| range.load("text"); | |
| await context.sync(); | |
| console.log(JSON.stringify(range.text, null, 4)); | |
| }); | |
| } | |
| catch (error) { | |
| OfficeHelpers.UI.notify(error); | |
| OfficeHelpers.Utilities.log(error); | |
| } | |
| } | |
| async function getFormulas() { | |
| try { | |
| await Excel.run(async (context) => { | |
| const sheet = context.workbook.worksheets.getItem("Sample"); | |
| const range = sheet.getRange("B2:E6"); | |
| range.load("formulas"); | |
| await context.sync(); | |
| console.log(JSON.stringify(range.formulas, null, 4)); | |
| }); | |
| } | |
| catch (error) { | |
| OfficeHelpers.UI.notify(error); | |
| OfficeHelpers.Utilities.log(error); | |
| } | |
| } | |
| async function setup() { | |
| try { | |
| await Excel.run(async (context) => { | |
| const sheet = await OfficeHelpers.ExcelUtilities | |
| .forceCreateSheet(context.workbook, "Sample"); | |
| const data = [ | |
| ["Product", "Qty", "Unit Price"], | |
| ["Almonds", 2, 7.50], | |
| ["Coffee", 1, 34.50], | |
| ["Chocolate", 5, 9.56] | |
| ]; | |
| const range = sheet.getRange("B2:D5"); | |
| range.values = data; | |
| range.format.autofitColumns(); | |
| const header = range.getRow(0); | |
| header.format.fill.color = "#4472C4"; | |
| header.format.font.color = "white"; | |
| sheet.activate(); | |
| await context.sync(); | |
| }); | |
| } | |
| catch (error) { | |
| OfficeHelpers.UI.notify(error); | |
| OfficeHelpers.Utilities.log(error); | |
| } | |
| } | |
| language: typescript | |
| template: | |
| content: "<section class=\"ms-font-m\">\n <p>This sample shows how to set and get values and formulas for a range using the Excel API.</p>\n</section>\n\n<section class=\"setup ms-font-m\">\n <h3>Set up</h3>\n <button id=\"setup\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Add sample data</span>\n </button>\n</section>\n\n<section class=\"samples ms-font-m\">\n <h3>Try it out</h3>\n\n <button id=\"set-value\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Set MigrateReady</span>\n </button>\n\t<button id=\"set-migrated\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Set Migrated</span>\n </button>\n\n<button id=\"set-idInDesc\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Set id in desc</span>\n </button>\n\n<br/>\n<br/><br/>\n\n <button id=\"set-values\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Set values</span>\n </button>\n <button id=\"set-formula\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Set formula using A1-style</span>\n </button>\n <button id=\"set-formulas\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Set formulas using A1-style</span>\n </button>\n <button id=\"set-formulas-r1c1\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Set formulas using R1C1-style</span>\n </button>\n\n <br />\n\n <button id=\"get-values\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Get values</span>\n </button>\n <button id=\"get-texts\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Get texts</span>\n </button>\n <button id=\"get-formulas\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Get formulas</span>\n </button>\n</section>\n" | |
| 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: | | |
| # Office.js | |
| https://appsforoffice.microsoft.com/lib/1/hosted/office.js | |
| # CSS Libraries | |
| [email protected]/dist/css/fabric.min.css | |
| [email protected]/dist/css/fabric.components.min.css | |
| # NPM libraries | |
| [email protected]/client/core.min.js | |
| @microsoft/[email protected]/dist/office.helpers.min.js | |
| [email protected] | |
| # IntelliSense: @types/library or node_modules paths or URL to d.ts files | |
| @types/office-js | |
| @types/core-js | |
| @microsoft/[email protected]/dist/office.helpers.d.ts | |
| @types/jquery |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment