Last active
March 14, 2021 18:29
-
-
Save iamnewton/f8fbbe111eae5cc540e17630c595c0dc to your computer and use it in GitHub Desktop.
Runs a few scripts to format the Gain & Loss sheets from stock data.
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
| /** | |
| * Format the first column of text (if its present) by removing anything | |
| * beyond the pipe character '|'; then copy down til a new name is presented | |
| * For more information on using the Spreadsheet API, see | |
| * https://developers.google.com/apps-script/service_spreadsheet | |
| */ | |
| function copyNameColumn() { | |
| var SHEET = SpreadsheetApp.getActiveSheet(); // get the active selected sheet; | |
| var RANGE = SHEET.getDataRange(); // ranges are equivalent to rows | |
| var RANGE_VALUES = RANGE.getValues(); // an array of arrays is returned for the values | |
| var nameOfStock = ''; | |
| for (var i = 0; i < RANGE_VALUES.length; i++) { | |
| var currentRange = SHEET.getRange(parseInt(i + 1), 1); | |
| var firstColumnData = RANGE_VALUES[i][0]; | |
| var arr = firstColumnData.split('|'); | |
| if (arr.length > 1) { | |
| nameOfStock = arr[0].trim(); | |
| } | |
| currentRange.setValue(nameOfStock); | |
| } | |
| } | |
| /** | |
| * Deletes rows in the active spreadsheet that contain 'word' in column A | |
| * For more information on using the Spreadsheet API, see | |
| * https://developers.google.com/apps-script/service_spreadsheet | |
| */ | |
| function removeSubTotalRow() { | |
| var SHEET = SpreadsheetApp.getActiveSheet(); // get the active selected sheet; | |
| var RANGE = SHEET.getDataRange(); // ranges are equivalent to rows | |
| var RANGE_VALUES = RANGE.getValues(); // an array of arrays is returned | |
| var rowsDeleted = 0; | |
| for (var i = 0; i < RANGE_VALUES.length; i++) { | |
| var firstColumnData = RANGE_VALUES[i][0]; | |
| // delete the rows with "SubTotal" | |
| if (firstColumnData.indexOf("SubTotal") > -1) { | |
| SHEET.deleteRow((parseInt(i) + 1) - rowsDeleted); | |
| rowsDeleted++; | |
| } | |
| } | |
| }; | |
| /** | |
| * Deletes rows in the active spreadsheet where column A & B are empty | |
| * For more information on using the Spreadsheet API, see | |
| * https://developers.google.com/apps-script/service_spreadsheet | |
| */ | |
| function removeEmptyRows() { | |
| var SHEET = SpreadsheetApp.getActiveSheet(); // get the active selected sheet; | |
| var RANGE = SHEET.getDataRange(); // ranges are equivalent to rows | |
| var RANGE_VALUES = RANGE.getValues(); // an array of arrays is returned | |
| var rowsDeleted = 0; | |
| for (var i = 0; i < RANGE_VALUES.length; i++) { | |
| var firstColumnData = RANGE_VALUES[i][0]; | |
| // delete the rows that are fully empty | |
| if (firstColumnData === '' && RANGE_VALUES[i][1] === '') { | |
| SHEET.deleteRow((parseInt(i) + 1) - rowsDeleted); | |
| rowsDeleted++; | |
| } | |
| } | |
| }; | |
| /** | |
| * Adds a custom menu to the active spreadsheet, containing a single menu item | |
| * for invoking the readRows() function specified above. | |
| * The onOpen() function, when defined, is automatically invoked whenever the | |
| * spreadsheet is opened. | |
| * For more information on using the Spreadsheet API, see | |
| * https://developers.google.com/apps-script/service_spreadsheet | |
| */ | |
| function onOpen() { | |
| var sheet = SpreadsheetApp.getActiveSpreadsheet(); | |
| var entries = []; | |
| entries.push({name: "1. Remove 'SubTotal' row", functionName: "removeSubTotalRow"}); | |
| entries.push({name: "2. Remove empty rows", functionName: "removeEmptyRows"}); | |
| entries.push({name : "3. Copy name on down", functionName : "copyNameColumn"}); | |
| // entries.push(null); // line separator | |
| sheet.addMenu("Format Stock Spreadsheet", entries); | |
| }; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment