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(); |
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"); |
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 = {} |
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; |
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> |
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; |
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') |
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]); | |
} |
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(); |