Skip to content

Instantly share code, notes, and snippets.

@iamnewton

iamnewton/format-stock-spreadsheet.js

Last active March 14, 2021 18:29
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save iamnewton/f8fbbe111eae5cc540e17630c595c0dc to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save iamnewton/f8fbbe111eae5cc540e17630c595c0dc to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. iamnewton revised this gist Mar 14, 2021. 1 changed file with 5 additions and 1 deletion.
    6 changes: 5 additions & 1 deletion format-stock-spreadsheet.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,8 @@
    // get the active selected sheet;
    /*
    * get the active selected sheet;
    */
    const SHEET = SpreadsheetApp.getActiveSheet();

    // ranges are equivalent to rows in this context, but in reality are more
    // like A2:B2 which means Column A, Row 2 through Column B, Row 2
    // this method pulls back all information from the first column, first row
    @@ -11,6 +14,7 @@ const SHEET = SpreadsheetApp.getActiveSheet();
    // | 3 | | | |
    // @url https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()
    const RANGE = SHEET.getDataRange();

    /*
    * @return the rectangular grid of values for this range
    * @url https://developers.google.com/apps-script/reference/spreadsheet/range#getValues()
  2. iamnewton revised this gist Mar 14, 2021. 1 changed file with 69 additions and 76 deletions.
    145 changes: 69 additions & 76 deletions format-stock-spreadsheet.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,85 +1,78 @@
    /**
    * 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();
    // get the active selected sheet;
    const SHEET = SpreadsheetApp.getActiveSheet();
    // ranges are equivalent to rows in this context, but in reality are more
    // like A2:B2 which means Column A, Row 2 through Column B, Row 2
    // this method pulls back all information from the first column, first row
    // to the last column, last row
    // | | A | B | C |
    // |---|-----|-----|-----|
    // | 1 | | | |
    // | 2 | x | x | |
    // | 3 | | | |
    // @url https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()
    const RANGE = SHEET.getDataRange();
    /*
    * @return the rectangular grid of values for this range
    * @url https://developers.google.com/apps-script/reference/spreadsheet/range#getValues()
    */
    const VALUES = RANGE.getValues();

    // 1. CLEAN THE DATA/REMOVE ROWS WITH SPECIFIC NAMES
    // ---
    // if the first column/name column contains 'SubTotal' or any of the 'total' stuff then don't
    // rename it but rather delete it
    function removeRowByName(column = 0) {
    // when you delete a row, the entire spreadsheet gets reindexed, so need to keep a count outside the scope of the for loop
    let rowsDeleted = 0;

    VALUES.forEach(( row, index, arr ) => {
    if (row[column].includes('SubTotal') || row[column].includes('Long Term Total') || row[column].includes('Short Term Total') || row[column].includes('Grand Total') || row[column].includes('Gain/Loss')) {
    SHEET.deleteRow(index + 1 - rowsDeleted);

    rowsDeleted++;
    }

    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++;
    }
    }
    };
    // 2. RENAME/NORMALIZE THE DATA
    // ---
    // the format of the stock is <name> | G/L Amount: $0.00
    // or its completely empty row, we want to rename all empty rows to the
    // previous named stock
    function cleanData() {
    let name = 'SECURITY';

    VALUES.forEach(( row, index, arr ) => {
    const cell = row[0].split('|');
    if (cell.length > 1) {
    name = cell[0];
    }

    /**
    * 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
    */
    SHEET
    .getRange(index + 1, 1)
    .setValue(name.trim());
    });
    }

    // 3. REMOVE ALL EMPTY ROWS
    // ---
    // an empty row will have nothing in the second column by the format of this 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++;
    }
    }
    };
    // when you delete a row, the entire spreadsheet gets reindexed, so need to keep a count outside the scope of the for loop
    let rowsDeleted = 0;

    VALUES.forEach(( row, index, arr ) => {
    if (row[1] === '') {
    console.log(row, row[0], row[1], index, rowsDeleted);
    SHEET.deleteRow(index + 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);
    removeRowByName();
    cleanData();
    removeEmptyRows();
    };
  3. Newton revised this gist Mar 3, 2018. No changes.
  4. Newton revised this gist Mar 3, 2018. No changes.
  5. Newton created this gist Mar 3, 2018.
    85 changes: 85 additions & 0 deletions format-stock-spreadsheet.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,85 @@
    /**
    * 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);
    };