Using cell input into function and ovewriting result into result in same cell in Google Sheet

Question

In Google Sheet, I would like to take the input of a cell, make a calculation, and display the result in the same cell in a different format. This would likely always be a percentage value that I would use conditional formatting to color the cell to provide a 'dashboard' view of statistics.

Example would be usage statistics for a month.

	Assets	Records
limit	50	1000
November	29	295

	Assets	Records
limit	50	1000
November	58%	30%

I found a Quora post detailing how to create your own scripts, so I believe I have the baseline of taking and modifying content of a cell:

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var cell = ss.getActiveSelection();
var cell_input = cell.getValue();

var cell_address = cell.getA1Notation()
var cell2 = ss.getRange(cell_address);

cell2.setFormula('=2*'+cell_input)
}

In this example, I'm unsure how to reference the cell it should be dividing against.

The problem with your idea is if you set the number format to % then every time you enter a number it assumes its a percentage. So the first time you enter 29 the result will be 58% but if the next time you enter 28 the result will be 0.56% — TheWizEd, Dec 02 '22 at 15:18

score 0 · Answer 1 · answered Dec 02 '22 at 21:07

Here's a general example that should give you an idea of how to manipulate the cells, formats and values in Sheets:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getActiveSelection();
  var cell_input = cell.getValue();
  var divisor = cell.offset(-1, 0)

  if (e.range.getA1Notation()=="B3" || "C3"){
    cell.setNumberFormat("@").setValue(Math.ceil((cell_input / divisor.getValue()) * 100) + "%")
  }
}

If you create a sheet that looks like this you will get the behavior you were looking for in B3 and C3:

How it works:

Add the e parameter to the onEdit(e) trigger so you can use e.range to read the range that called the function.
To get the cell you're dividing against you need to either know its exact range or its relative position to the cell that called it. Since in your sample the percentage cell is below the divisor, you can just offset() it by -1, which returns the cell above it. If the relative position changes you'll need to take this into account to modify the offset but it should work in general if your table has a consistent structure.
You'll need to use an if to fire the trigger only in a specific range, otherwise the entire sheet will be affected. You do this by comparing the caller e.range to your desired range. In this example for the sake of simplicity I just had two cells so I just compared the individual B3 and C3 cells to e.range.getA1Notation(), but if you want a bigger range you'll probably want to use a more advanced technique like the ones in this question
To get around the format problem described in TheWizEd's comment I'm forcing the cell to a text value using setNumberFormat("@"). This way if you enter a value a second time it will read it as a number rather than a percent. I tested using the value elsewhere in the sheet and it still works as a percentage when needed, but watch out for unintended behavior.

Sources:

score 0 · Answer 2 · answered Dec 04 '22 at 22:04

When working with triggers, take advantage of the event object.

Replace

function onEdit(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getActiveSelection();
  var cell_input = cell.getValue();

by

function onEdit(e){
  var cell_input = e.value ?? 0;

Notes:

SpreasheetApp.Spreadsheet.getActiveSeletion() returns the first range that belongs to the active selection, this might cause problems because the active seleccion could include multiple cells, and the current cell could be any cell in the selection even one cell that is not part of the returned range.
SpreadsheetApp.Range.getValue() returns the value of the top left cell in the range. A user could select multiple cells and use the tab key to change the current cell, so under certain circunstances the value obtained this way might not be the value of the edited cell.
e.value ?? 0 is used to get 0 as the default value, i.e., when a cell is cleared.

As onEdit is triggered when any cell is edited your script should include a condition to only change the corresponding cells.

function onEdit(e){
  const cell_input = e.value ?? 0;
  if(e.range.rowStart === 3 && [2,3].includes(e.range.columnStart)){
    const newValue = (100 * cell_input / e.range.offset(-1,0).getValue()).toFixed() + '%';
    e.range.setValue(newValue);
  }
}

The above uses

SpreadsheetApp.Range.offset to get the cell above of the edited cell.
Writes a the percentage as string, taking advange of the Google Sheets automatic data type assignation.

References

Using cell input into function and ovewriting result into result in same cell in Google Sheet

2 Answers2

Sources: