Runs a few scripts to format the Gain & Loss sheets from stock data.

format-stock-spreadsheet.js

/*
 * 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++;
		}
	});
}

// 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];
		  }

		  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() {
  // 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++;
	  }
  });
}

function onOpen() {
  removeRowByName();
  cleanData();
  removeEmptyRows();
};