1

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".

2
+50

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.