Created
May 17, 2021 03:27
-
-
Save bjarkirafn/2a8ca1db8eff89db400c59cb28be4050 to your computer and use it in GitHub Desktop.
Imports JSON data into 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: Import JSON data | |
description: Imports JSON data into a table. | |
host: EXCEL | |
api_set: {} | |
script: | |
content: | | |
$("#import-json-data").click(() => tryCatch(importJsonData)); | |
async function importJsonData() { | |
await Excel.run(async (context) => { | |
context.workbook.worksheets.getItemOrNullObject("Sample").delete(); | |
const sheet = context.workbook.worksheets.add("Sample"); | |
let expensesTable = sheet.tables.add("A1:C1", true); | |
expensesTable.name = "ExpensesTable"; | |
expensesTable.getHeaderRowRange().values = [["UserId", "Title", "Completed"]]; | |
const url = "https://jsonplaceholder.typicode.com/todos"; | |
const res = await fetch(url); | |
const data = await res.json(); | |
// const newData = transactions.map((item) => [item.DATE, item.MERCHANT, item.CATEGORY, item.AMOUNT]); | |
const newData = data.map((i) => [i.userId, i.title, i.completed]); | |
expensesTable.rows.add(null, newData); | |
sheet.getUsedRange().format.autofitColumns(); | |
sheet.getUsedRange().format.autofitRows(); | |
sheet.activate(); | |
await context.sync(); | |
}); | |
} | |
const transactions = [ | |
{ | |
DATE: "1/1/2017", | |
MERCHANT: "The Phone Company", | |
CATEGORY: "Communications", | |
AMOUNT: "$120" | |
}, | |
{ | |
DATE: "1/1/2017", | |
MERCHANT: "Southridge Video", | |
CATEGORY: "Entertainment", | |
AMOUNT: "$40" | |
}, | |
{ | |
DATE: "1/1/2017", | |
MERCHANT: "Coho Winery", | |
CATEGORY: "Restaurant", | |
AMOUNT: "$47" | |
}, | |
{ | |
DATE: "1/2/2017", | |
MERCHANT: "Contoso Ltd", | |
CATEGORY: "Shopping", | |
AMOUNT: "$56" | |
}, | |
{ | |
DATE: "1/2/2017", | |
MERCHANT: "Contoso Ltd", | |
CATEGORY: "Shopping", | |
AMOUNT: "$110" | |
}, | |
{ | |
DATE: "1/2/2017", | |
MERCHANT: "Liberty Bakery & Cafe", | |
CATEGORY: "Groceries", | |
AMOUNT: "$27" | |
}, | |
{ | |
DATE: "1/2/2017", | |
MERCHANT: "Liberty Bakery & Cafe", | |
CATEGORY: "Groceries", | |
AMOUNT: "$38" | |
}, | |
{ | |
DATE: "1/2/2017", | |
MERCHANT: "Northwind Electric Cars", | |
CATEGORY: "Transportation", | |
AMOUNT: "$42" | |
}, | |
{ | |
DATE: "1/2/2017", | |
MERCHANT: "Best For You Organics Company", | |
CATEGORY: "Groceries", | |
AMOUNT: "$27" | |
}, | |
{ | |
DATE: "1/3/2017", | |
MERCHANT: "Contoso, LTD", | |
CATEGORY: "Shopping", | |
AMOUNT: "$25" | |
}, | |
{ | |
DATE: "1/5/2017", | |
MERCHANT: "Munson's Pickles & Preserves Farm", | |
CATEGORY: "Groceries", | |
AMOUNT: "$178" | |
} | |
]; | |
/** 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"> | |
<p>This sample shows how to import json data into a new table.</p> | |
</section> | |
<section class="samples ms-font-m"> | |
<h3>Try it out</h3> | |
<button id="import-json-data" class="ms-Button"> | |
<span class="ms-Button-label">Import JSON data</span> | |
</button> | |
</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 | |
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