Google Sheets – Conditionally Change Sign of Decimal Value

google sheetsgoogle-apps-script

Here's a preview of the spreadsheet I'm editing in Google Drive:

enter image description here

The transaction type cell has a value of "debit" if the row is for an expense, and "credit" if it is income.

I would like a script to go through each row, and add a minus sign before the value in the "amount" column, if the value in the "transaction Type" column has a value of "debit".

Best Answer

Maybe give this a try:

function onOpen() {
SpreadsheetApp.getUi().createMenu('Custom Menu')
    .addItem('Make Negative', 'makeNegative')
    .addToUi();
}

function makeNegative() {
var res = [],
    range = SpreadsheetApp.getActiveSheet().getRange('D2:E'),
    val = range.getValues()
        .forEach(function (r, i) {
            r[0] = r[1] === 'debit' && r[0] > 0 && typeof r[0] === 'number' ? r[0] * -1 : r[0]
            res.push(r)
        })
    range.setValues(res);
}

Add above script to the script editor and run onOpen() by clicking the 'play' button. Then go back to the sheet, where you should see a newly created menu-item 'Custom Menu'. From there click on 'Make negative' and see if this works.

Related Topic