Created
August 27, 2020 15:26
-
-
Save brettwhitty/3a884b98300f1b3d5bbf398a28cee85f to your computer and use it in GitHub Desktop.
Instantiates a sample sheet in a manner like the CSV export.
:)
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: SampleSheetMagic | |
| description: |- | |
| Instantiates a sample sheet in a manner like the CSV export. | |
| :) | |
| host: EXCEL | |
| api_set: {} | |
| script: | |
| content: > | |
| const apSrc = "DEFAULT"; | |
| const apDest = "DEFAULT"; | |
| $("#run").click(() => tryCatch(importJsonData)); | |
| //function run() { | |
| const sheetData = exportSampleCSVData( | |
| // analysis-plan => from /api/v1/analysis-plans/[ID]/ | |
| { | |
| description: "PHX125_BD_Q1_221" | |
| }, | |
| // assay => from /api/v1/assays/[ID]/ | |
| { | |
| long_name: "PGDx elio blah blah blah", | |
| software_version: "v1.2.3", | |
| metadata: { | |
| run: { | |
| samples_per_run: 8 | |
| }, | |
| sample: { | |
| properties: ["", "", "", ""] | |
| } | |
| } | |
| }, | |
| // sample => /api/v1/samples/[ID]/ 'results' filtered by 'name' from a-p.samples | |
| [] | |
| ); | |
| function colLetter(intVal = 1) { | |
| const atoz = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; | |
| const prefix = ""; | |
| if (!Number.isInteger(intVal)) { | |
| console.log(`Bad call to colLetter: ${intVal}`); | |
| return null; | |
| } | |
| if (intVal % 26 != 0) { | |
| prefix = colLetter((intVal - (intVal % 26)) / 26); | |
| intVal = intVal % 26; | |
| } | |
| return `${prefix}${atoz.slice(intVal - 1, intVal)}`; | |
| } | |
| async function importJsonData() { | |
| await Excel.run(async (context) => { | |
| // user provides the input in the HTML page | |
| // | |
| // src will be for fetching data from API | |
| // dest will be for creating a new sheet (eg: copy for re-run) | |
| // | |
| if ($("#ap-src").val() !== "") { | |
| apSrc = $("#ap-src").val(); | |
| if ($("#ap-dest").val() === "") { | |
| apDest = apSrc; | |
| $("#ap-dest").val(apDest); | |
| } else { | |
| apDest = $("#ap-dest").val(); | |
| } | |
| } else { | |
| return; | |
| } | |
| context.workbook.worksheets.getItemOrNullObject(apDest).delete(); | |
| const sheet = context.workbook.worksheets.add(apDest); | |
| let colIdx = 1; | |
| let rowIdx = 1; | |
| for (let section of sheetData) { | |
| let name = section.name; | |
| let data = section.data; | |
| // set name | |
| let rangeSectionName = sheet.getRange(`${colLetter(1)}${rowIdx}`); | |
| rangeSectionName.values = [[name]]; | |
| rowIdx++; | |
| for (let rowData of data) { | |
| let dataColCount = rowData.length; | |
| let rangeSectionData = sheet.getRange(`${colLetter(1)}${rowIdx}:${colLetter(dataColCount)}${rowIdx}`); | |
| rangeSectionData.values = [rowData]; | |
| rangeSectionData.format.autofitColumns(); | |
| rowIdx++; | |
| } | |
| } | |
| sheet.getUsedRange().format.autofitColumns(); | |
| sheet.getUsedRange().format.autofitRows(); | |
| sheet.activate(); | |
| await context.sync(); | |
| }); | |
| } | |
| /** Default helper for invoking an action and handling errors. */ | |
| function tryCatch(callback) { | |
| Promise.resolve() | |
| .then(callback) | |
| .catch(function(error) { | |
| // Note: In a production add-in, you'd want to notify the user through your add-in's UI. | |
| console.error(error); | |
| }); | |
| } | |
| function exportSampleCSVData(analysisPlanData = {}, assayData = {}, | |
| sampleData = []) { | |
| // need some order here | |
| const jsonData = [ | |
| { | |
| name: "[Header]", | |
| data: [ | |
| ["Plan Description", analysisPlanData.description], | |
| ["Assay", assayData.long_name + " " + assayData.software_version] | |
| ] | |
| }, | |
| { name: "[Data]", data: [] } | |
| ]; | |
| const csvDataArray = [ | |
| ["[Header]", ""], | |
| ["Plan Description", analysisPlanData.description], | |
| ["Assay", assayData.long_name + " " + assayData.software_version], | |
| ["[Data]", ""] | |
| ]; | |
| //const sample_properties_metadata = assayData.metadata.sample.properties; | |
| const sample_properties_metadata = [ | |
| { | |
| kind: { | |
| required: true, | |
| label: "Sample Type", | |
| type: "string", | |
| options: [ | |
| { | |
| value: "SPECIMEN", | |
| label: "Specimen" | |
| }, | |
| { | |
| value: "CELL_LINE", | |
| label: "Cell Line" | |
| }, | |
| { | |
| value: "FILLER", | |
| label: "Filler" | |
| }, | |
| { | |
| value: "EXT_CTRL", | |
| label: "External Control" | |
| } | |
| ] | |
| } | |
| }, | |
| { | |
| tumor_type: { | |
| required: false, | |
| label: "Details", | |
| type: "string" | |
| } | |
| }, | |
| { | |
| index_sequence: { | |
| required: true, | |
| label: "Index Name", | |
| type: "string", | |
| options: [ | |
| { | |
| value: "GTCTGTCA", | |
| label: "Index Primer A01" | |
| }, | |
| { | |
| value: "TGAAGAGA", | |
| label: "Index Primer B01" | |
| }, | |
| { | |
| value: "TTCACGCA", | |
| label: "Index Primer C01" | |
| }, | |
| { | |
| value: "AACGTGAT", | |
| label: "Index Primer D01" | |
| }, | |
| { | |
| value: "ACCACTGT", | |
| label: "Index Primer E01" | |
| }, | |
| { | |
| value: "ACCTCCAA", | |
| label: "Index Primer F01" | |
| }, | |
| { | |
| value: "ATTGAGGA", | |
| label: "Index Primer G01" | |
| }, | |
| { | |
| value: "ACACAGAA", | |
| label: "Index Primer H01" | |
| }, | |
| { | |
| value: "GCGAGTAA", | |
| label: "Index Primer A02" | |
| }, | |
| { | |
| value: "GTCGTAGA", | |
| label: "Index Primer B02" | |
| }, | |
| { | |
| value: "GTGTTCTA", | |
| label: "Index Primer C02" | |
| }, | |
| { | |
| value: "TATCAGCA", | |
| label: "Index Primer D02" | |
| }, | |
| { | |
| value: "TGGAACAA", | |
| label: "Index Primer E02" | |
| }, | |
| { | |
| value: "TGGTGGTA", | |
| label: "Index Primer F02" | |
| }, | |
| { | |
| value: "ACTATGCA", | |
| label: "Index Primer G02" | |
| }, | |
| { | |
| value: "CCTAATCC", | |
| label: "Index Primer H02" | |
| } | |
| ] | |
| } | |
| }, | |
| { | |
| name: { | |
| required: false, | |
| label: "Sample Name", | |
| type: "string" | |
| } | |
| } | |
| ]; | |
| const keys = new Array(); | |
| // [Data] section header labels | |
| const headerLabels = new Array(); | |
| for (const eachProperty of sample_properties_metadata) { | |
| for (const key in eachProperty) { | |
| if (eachProperty.hasOwnProperty(key)) { | |
| keys.push(key); | |
| headerLabels.push(eachProperty[key].label); | |
| } | |
| } | |
| } | |
| csvDataArray.push(headerLabels); | |
| jsonData[0].data.push(headerLabels); | |
| const missingSampleCount = assayData.metadata.run.samples_per_run - sampleData.length; | |
| const filler = missingSampleCount === 0 ? false : true; | |
| //const filler = assayData.metadata.run.samples_per_run == sampleData.length ? false : true; | |
| // Data in data section | |
| for (const eachSampleData of sampleData) { | |
| const properties = eachSampleData.properties; | |
| const eachCSVRow = new Array(); | |
| for (const key of keys) { | |
| let property = properties[key]; | |
| if (typeof property == "string") { | |
| eachCSVRow.push(property); | |
| } else if ("label" in property && typeof property["label"] == "string") { | |
| eachCSVRow.push(property["label"]); | |
| } else { | |
| eachCSVRow.push(""); | |
| } | |
| } | |
| csvDataArray.push(eachCSVRow); | |
| jsonData[1].data.push(eachCSVRow); | |
| } | |
| if (filler) { | |
| for (let i = 0; i < missingSampleCount; i++) { | |
| let fillerData = ["Filler"]; | |
| for (let j = 0; j < headerLabels.length - 1; j++) { | |
| fillerData.push("N/A"); | |
| } | |
| csvDataArray.push(fillerData); | |
| jsonData[1].data.push(fillerData); | |
| } | |
| } | |
| //return Papa.unparse(csvDataArray); | |
| return jsonData; | |
| // | |
| //AnalysisPlanUtils.downloadCSVFile(papa.unparse(csvDataArray), analysisPlanData.name + '.csv'); | |
| } | |
| /* | |
| downloadSamplesCSV = (): void => { | |
| if (this.plan) { | |
| Observable.forkJoin( | |
| this._analysisPlansService.getAnalysisPlanSamples({ id: this.plan.id }), | |
| this._assaysService.getAssayDetails({ id: this.plan.assay.id }) | |
| ).subscribe((result: any[]) => { | |
| if (result[0] && result[1]) { | |
| AnalysisPlanUtils.exportSampleCSVData(this._papaParseService, this.plan, result[1], result[0]); | |
| } | |
| }); | |
| } | |
| } | |
| */ | |
| language: typescript | |
| template: | |
| content: "<section class=\"ms-font-m\">\n\t<p class=\"ms-font-m\">This sample demonstrates populating a spreadsheet with analysis plan data.</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<div>\n\t\t<div>\n\t\t\t<div>Source Analysis Plan Name:</div>\n\t\t\t<div><input type=\"text\" id=\"ap-src\" /></div>\n\t\t\t</div>\n\t\t\t<div>\n\t\t\t\t<div>New Analysis Plan Sheet:</div>\n\t\t\t\t<div><input type=\"text\" id=\"ap-dest\" /></div>\n\t\t\t\t</div>\n\t\t\t\t<!--\n <p class=\"ms-font-m\">Select some cells in the worksheet, then press <b>Highlight selected range</b>.</p>\n -->\n\t\t\t\t<br />\n\t\t\t\t<div>\n\t\t\t\t\t<button id=\"run\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">RUN</span>\n </button>\n\t\t\t\t</div>\n\t\t\t</div>\n</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 | |
| [email protected]/dist/css/fabric.min.css | |
| [email protected]/dist/css/fabric.components.min.css | |
| [email protected]/client/core.min.js | |
| @types/core-js | |
| [email protected] | |
| @types/[email protected] | |
| papaparse |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment