Created
December 4, 2020 17:58
-
-
Save p15martin/14e4a93092f2cc5209ee6b025fff8047 to your computer and use it in GitHub Desktop.
Create a new snippet from a blank template.
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: add-or-insert-timeout | |
description: Create a new snippet from a blank template. | |
host: EXCEL | |
api_set: {} | |
script: | |
content: | | |
$("#add").click(() => tryCatch(add)); | |
$("#insert").click(() => tryCatch(insert)); | |
async function add() { | |
await Excel.run(async (context) => { | |
const sheet = context.workbook.worksheets.getItem("Transactions"); | |
const table = sheet.tables.getItemAt(0); | |
table.rows.load("items"); | |
await context.sync(); | |
let items = table.rows.items; | |
let index = -1; | |
let newItems = []; | |
items.map((ele) => { | |
newItems.push(ele.values[0]); | |
}); | |
const newSheet = context.workbook.worksheets.add("newSheet"); | |
const newTable = newSheet.tables.add("B6:Q6", true); | |
let beforeAdd = Date.now(); | |
newTable.rows.add(index, newItems); | |
await context.sync(); | |
console.log("Rows add done.", "Time cost: " + (Date.now() - beforeAdd) + " ms"); | |
}); | |
} | |
async function insert() { | |
await Excel.run(async (context) => { | |
const sheet = context.workbook.worksheets.getItem("Transactions"); | |
const table = sheet.tables.getItemAt(0); | |
table.rows.load("items"); | |
const header = table.getHeaderRowRange() | |
header.load('values') | |
await context.sync(); | |
let items = table.rows.items; | |
let index = -1; | |
let newItems = []; | |
items.map((ele) => { | |
const values = ele.values[0] | |
const insert = [ | |
values[0], //date | |
values[1], // merchant | |
values[2], // category | |
values[3], // amount | |
'=XLOOKUP([@[Account_ID]], Accounts[ID], Accounts[Account nickname], "Unknown")', // account | |
'=XLOOKUP([@[Account_ID]], Accounts[ID], Accounts[Account\'#], "Unknown")', // account number | |
'=XLOOKUP([@[Institution_ID]], Institutions[ID], Institutions[Institution nickname], "Unknown")', // institution | |
values[7], // transaction id | |
values[8], // account id | |
values[9], // institution id | |
'=IFERROR(INDEX(Table2[Category Type],MATCH([@Category],Table2[Category Name],0),0),"")', // category type | |
values[11], // day | |
values[12], // month | |
values[13], // year | |
'=IF(ISBLANK([@Date]),"",[@Date]-WEEKDAY([@Date],2)+1)', // beginning of week | |
'=IF(ISBLANK([@Date]),"",DATE([@[Date_Year]],[@[Date_Month]],1))', // month | |
] | |
newItems.push(insert); | |
}); | |
newItems = newItems.slice(0, 3648); //asume items.length >= 3647 | |
const newSheet2 = context.workbook.worksheets.add("newSheet2"); | |
const newTable2 = newSheet2.tables.add("B6:Q6", true); | |
newTable2.getHeaderRowRange().values = header.values | |
const range = newSheet2.getRange(`B7:Q3653`); | |
let beforeInsert = Date.now(); | |
const inserted = range.insert(Excel.InsertShiftDirection.down); | |
inserted.values = newItems; | |
await context.sync(); | |
console.log("Range insert done.", "Time cost: " + (Date.now() - beforeInsert) + " ms"); | |
}); | |
} | |
/** 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: "<button id=\"add\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Add Rows</span>\n</button>\n<button id=\"insert\" class=\"ms-Button\">\n\t<span class=\"ms-Button-label\">Insert Range</span>\n</button>" | |
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 | |
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