Created
May 8, 2025 22:33
-
-
Save mvanella/d4707fe10be192f07e474c81782edcca to your computer and use it in GitHub Desktop.
Google Apps Script to Export SWU Collection Tracker to Different CSV Formats
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
| function exportToSWUDB() { | |
| exportSheetToCsv('swudb'); | |
| } | |
| function exportToSWUGG() { | |
| exportSheetToCsv('swugg'); | |
| } | |
| function exportSheetToCsv(format) { | |
| // Establish sheet names and the official abbreviation | |
| const validSheetNames = [ | |
| { sheetName: 'Spark of Rebellion', abbreviation: 'SOR' }, | |
| { sheetName: 'Shadows of the Galaxy', abbreviation: 'SHD' }, | |
| { sheetName: 'Twilight of the Republic', abbreviation: 'TWI' }, | |
| { sheetName: 'Jump to Lightspeed', abbreviation: 'JTL' } | |
| ]; | |
| // Establish the header line of the CSV | |
| const swudbHeaderLine = 'Set,CardNumber,Count,IsFoil'; | |
| const swuggHeaderLine = 'Normal,Foil,Name,Set,Number'; | |
| const showcaseSheetName = 'Showcase Cards'; | |
| let headerLine = (format === 'swugg') ? swuggHeaderLine : swudbHeaderLine; | |
| let csvLines = []; | |
| csvLines.push(headerLine); | |
| // Get all the sheets on the active spreadsheet. | |
| let sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); | |
| sheets.forEach(sheet => { | |
| // If it's not a valid sheet, move on to the next sheet | |
| var sheetInfo = validSheetNames.find(item => item.sheetName === sheet.getName()); | |
| if(sheet.getName() === showcaseSheetName) { | |
| var sheetLines = processShowcaseSheet(sheet, format); | |
| csvLines = [...csvLines, ...sheetLines]; | |
| } | |
| else if(!sheetInfo) { | |
| return; | |
| } | |
| else { | |
| var sheetLines = processSheet(sheet, sheetInfo.sheetName, sheetInfo.abbreviation, format); | |
| csvLines = [...csvLines, ...sheetLines]; | |
| } | |
| }); | |
| // TODO - export CSV lines to a file. | |
| downloadFile(csvLines); | |
| } | |
| function processSheet(sheet, sheetName, abbreviation, format) { | |
| let resultData = []; | |
| const lastRow = sheet.getLastRow(); | |
| const startingRow = 5; | |
| const numRows = lastRow + 1 - startingRow; | |
| const columnCount = (format === 'swugg') ? 12 : 5; | |
| let dataRange = sheet.getRange(startingRow, 1, numRows, columnCount); | |
| let data = dataRange.getValues(); | |
| // Sample data result | |
| // [ '226', 3, '', '', '' ] | |
| // [ row, C, CF, H, HF ] | |
| if(format === 'swudb'){ | |
| data.forEach(row => { | |
| var cardNum = row[0]; | |
| var hyperspaceCardNum = Number(cardNum) + Number(numRows); | |
| // Non Foil Cards | |
| var nfCount = row[1]; | |
| if(nfCount){ | |
| resultData.push(`${abbreviation},${cardNum},${nfCount},false`); | |
| } | |
| // Foil Cards | |
| var fCount = row[2]; | |
| if(fCount) { | |
| resultData.push(`${abbreviation},${cardNum},${fCount},true`); | |
| } | |
| // Hyperspace Cards | |
| var hsCount = row[3]; | |
| if(hsCount) { | |
| resultData.push(`${abbreviation},${hyperspaceCardNum},${hsCount},false`); | |
| } | |
| // Hyperspace Foil Cards | |
| var hsfCount = row[4]; | |
| if(hsfCount) { | |
| resultData.push(`${abbreviation},${hyperspaceCardNum},${hsfCount},true`); | |
| } | |
| }); | |
| } | |
| else if(format === 'swugg'){ | |
| data.forEach(row => { | |
| // 'Normal,Foil,Name,Set,Number' | |
| var cardNum = row[0]; | |
| var cardName = row[11]; | |
| // Normal Non Foil and Foil Cards | |
| var nfCount = row[1]; | |
| var fCount = row[2]; | |
| if(!nfCount) { nfCount = 0 }; | |
| if(!fCount) { fCount = 0 }; | |
| if(nfCount > 0 || fCount > 0) { | |
| resultData.push(`${nfCount},${fCount},"${cardName}","${abbreviation}","${cardNum}"`); | |
| } | |
| // Hyperspace Non Foil and Foil Cards | |
| var hyperspaceCardNum = Number(cardNum) + Number(numRows); | |
| var hsCount = row[3]; | |
| var hsfCount = row[4]; | |
| if(!hsCount) { hsCount = 0 }; | |
| if(!hsfCount) { hsfCount = 0 }; | |
| if(hsCount > 0 || hsfCount > 0) { | |
| resultData.push(`${hsCount},${hsfCount},"${cardName}","${abbreviation}","${hyperspaceCardNum}"`); | |
| } | |
| }); | |
| } | |
| return resultData; | |
| } | |
| function processShowcaseSheet(sheet, format){ | |
| let resultData = []; | |
| const lastRow = sheet.getLastRow(); | |
| const startingRow = 5; | |
| const numRows = lastRow + 1 - startingRow; | |
| const columnCount = (format === 'swugg') ? 8 : 3; | |
| /* | |
| // Establish the header line of the CSV | |
| const swudbHeaderLine = 'Set,CardNumber,Count,IsFoil'; | |
| const swuggHeaderLine = 'Normal,Foil,Name,Set,Number'; | |
| */ | |
| let dataRange = sheet.getRange(startingRow, 1, numRows, columnCount); | |
| let data = dataRange.getValues(); | |
| if(format === 'swudb'){ | |
| data.forEach(row => { | |
| var abbreviation = row[0]; | |
| var cardNum = row[1]; | |
| var cardCount = row[2]; | |
| if(cardCount > 0) { | |
| resultData.push(`${abbreviation},${cardNum},${cardCount},false`); | |
| } | |
| }); | |
| } | |
| else if(format === 'swugg'){ | |
| data.forEach(row => { | |
| var abbreviation = row[0]; | |
| var cardNum = row[1]; | |
| var cardCount = row[2]; | |
| var cardName = row[7]; | |
| // non foil count, foil count | |
| if(cardCount > 0) { | |
| resultData.push(`${cardCount},0,"${cardName}","${abbreviation}","${cardNum}"`); | |
| } | |
| }); | |
| } | |
| return resultData; | |
| } | |
| function downloadFile(csvData){ | |
| var rawCSV = csvData.join('\n'); | |
| var html = '<textarea style="width:100%;height:300px;">' + rawCSV + '</textarea>'; | |
| SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput(html)); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment