Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Runs a few scripts to format the Gain & Loss sheets from stock data.
/**
* 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
You can’t perform that action at this time.