Chris Ismael ismaelc
ismaelc
/ capturerange.01.js
Created
February 6, 2021 09:37
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
| let book = context.workbook | |
| let selectedRange = book.getSelectedRange() | |
| let usedRange = book.worksheets | |
| .getActiveWorksheet() | |
| .getUsedRange(); | |
| selectedRange.load("address"); | |
| usedRange.load("address"); | |
| await context.sync(); |
ismaelc
/ capturerange.js
Created
February 6, 2021 09:28
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
| export async function captureRange() { | |
| try { | |
| await Excel.run(async context => { | |
| let book = context.workbook | |
| let selectedRange = book.getSelectedRange() | |
| let usedRange = book.worksheets | |
| .getActiveWorksheet() | |
| .getUsedRange(); | |
| selectedRange.load("address"); | |
| usedRange.load("address"); |
ismaelc
/ taskpane.02.js
Created
February 6, 2021 09:17
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
| import Classifier from 'ml-classify-text' | |
| let processedData = [] | |
| let classifier = {} |
ismaelc
/ taskpane.01.js
Created
February 6, 2021 09:13
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
| //document.getElementById("run").onclick = run; | |
| document.getElementById("capture").onclick = captureRange; | |
| document.getElementById("predict").onclick = predictRange; |
ismaelc
/ taskpane.html
Created
February 6, 2021 09:02
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
| <body class="ms-font-m ms-welcome ms-Fabric"> | |
| <section id="sideload-msg" class="ms-welcome__main"> | |
| <h2 class="ms-font-xl">Please sideload your add-in to see app body.</h2> | |
| </section> | |
| <main id="app-body" class="ms-welcome__main" style="display: none;"> | |
| <div role="button" id="capture" class="ms-welcome__action ms-Button ms-Button--hero ms-font-xl"> | |
| <span class="ms-Button-label">Capture training data</span> | |
| </div> | |
| <div role="button" id="predict" class="ms-welcome__action ms-Button ms-Button--hero ms-font-xl"> | |
| <span class="ms-Button-label">Predict</span> |
ismaelc
/ processdata.js
Created
February 2, 2021 05:51
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
| // Formats data into key:array | |
| function processData(arrayData) { | |
| let processed = {}; | |
| for (let [i, [text, label]] of arrayData.entries()) { | |
| // TODO: Deal with headers | |
| if (i == 0 && this.excludeFirstRow) continue; | |
| // Do not add empty rows | |
| if (text.trim() == '' || label.trim() == '') continue; |
ismaelc
/ resolverange.js
Created
February 2, 2021 05:49
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
| // Deal with column vs range select | |
| function resolveRange(sheet) { | |
| let range = sheet.getActiveRange().getA1Notation(); | |
| let [first, second] = range.split(':') | |
| if (!/\d/.test(second)) { | |
| range = `${first}1:${second}${sheet.getLastRow()}` | |
| } | |
| return range | |
| } |
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
| // Import ml-classify-text | |
| eval(UrlFetchApp.fetch('https://cdn.jsdelivr.net/npm/ml-classify-text@2.0.0/lib/index.js').getContentText()); | |
| let classifier = new Classifier() | |
| // Add menu items | |
| function onOpen() { | |
| var ui = SpreadsheetApp.getUi(); | |
| ui.createMenu('ML Train Menu') | |
| .addItem('Get training set', 'captureRange') | |
| .addItem('Predict', 'predictRange') |
ismaelc
/ predictRange.js
Created
February 2, 2021 05:15
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
| // Predict | |
| let predicted = [] | |
| for (let [text, prediction] of data) { | |
| let predictions = classifier.predict(text); | |
| if (predictions.length) { | |
| prediction = predictions[0].label | |
| } | |
| predicted.push([text, prediction]); | |
| } |
ismaelc
/ selectPredictRange.js
Created
February 2, 2021 05:06
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
| // Get selected range and data | |
| let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); | |
| let resolved = this.resolveRange(sheet) | |
| let range = sheet.getRange(resolved) | |
| var data = range.getValues(); |