How do I make a positive value be converted automatically into negative without adding formula to a cell when someone pastes/or added a value to a cell.

In this example I want that 5 2 and 3 be a negative value.

CodePudding user response：

In your situation, how about using the simple trigger of onEdit as follows?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. When you use this script, please put a positive value to a cell. By this, the positive value is converted to the negative value.

function onEdit(e) {
  const value = e.range.getValue();
  if (!isNaN(value) && value > 0) {
    e.range.setValue(-value);
  }
}

Note:

• If you want to limit the sheet and the range, please tell me.

• If you want to convert all cell values in a sheet by a script, how about the following script? When this script is run, the positive values in a sheet are converted to negative values.

    function myFunction() {
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
      const range = sheet.getDataRange();
      const values = range.getValues().map(r => r.map(c => !isNaN(c) && c > 0 ? -c : c));
      range.setValues(values);
    }
  

Reference:

• Simple Triggers

Added:

From your following additional request,

Is it possible to apply it with certain range only. Like to cell C45:AK76 and C84:AK115?

In this case, how about the following sample script?

Sample script:

function onEdit(e) {
  const ranges = ["C45:AK76", "C84:AK115"]; // These ranges are from your reply.
  const sheetName = "Sheet1"; // Please set your sheet name.

  const { range } = e;
  const sheet = range.getSheet();
  const value = range.getValue();
  const check = sheet.getRangeList(ranges).getRanges().some(r => {
    const startRow = r.getRow();
    const endRow = startRow   r.getNumRows() - 1;
    const startCol = r.getColumn();
    const endCol = startCol   r.getNumColumns() - 1;
    return range.rowStart >= startRow && range.rowEnd <= endRow && range.columnStart >= startCol && range.columnEnd <= endCol;
  });
  if (sheet.getSheetName() != sheetName || !check || isNaN(value) || value < 0) return;
  range.setValue(-value);
}

• In this sample, the script is run when the cells "C45:AK76", "C84:AK115" are edited.

CodePudding user response：

Try this:

function onEdit(e) {
  //e.source.toast("Entry");
  if(!isNaN(e.value) && e.value.split("")[0] != "-" ) {
    e.range.setValue(-e.value);
  }
}

With Range Limits:

function onEdit(e) {
  if (e.range.columnStart > 2 && e.range.columnStart < 37 && e.range.rowStart > 45 && e.range.rowStart < 76) {
    if (!isNaN(e.value) && e.value.split("")[0] != "-") {
      e.range.setValue(-e.value);
    }
  }
  if (e.range.columnStart > 2 && e.range.columnStart < 37 && e.range.rowStart > 83 && e.range.rowStart < 116) {
    if (!isNaN(e.value) && e.value.split("")[0] != "-") {
      e.range.setValue(-e.value);
    }
  }
}