On a spreadsheet I am making I often need to make a range of scaling values using only whole numbers (integers.)

For example, 12 values starting at 90 and ending at 120 scaling linearly rounding to the nearest whole number.

Using google sheets functions I can draw a line between these values with the following formula:

=(previousValue+((finalValue-firstValue))/(ROW(finalValue)-ROW(firstValue)))

You then drag this formula down after locking the finalValue and firstValues.

however this formula will produce float values. Adding a round function will cause the results to not be as linear as possible, and with heavy rounding (such as to the nearest 2 or 5) will potentially result in it not scaling at all

Ideally, since the spreadsheet is so large already and I don't want to duplicate columns for each time I do this, I need a way to accomplish this WITHOUT adding additional rows or columns to the spreadsheet.

The ideal way seems to be with appscript and a sidebar. However if you think you can solve this issue with a formula i'm open to that being the answer too.

For the sidebar though you would select the range of values to scale, and then press a button in the sidebar to run the script.

The script would take the range of values, find the Start and End values then duplicate the effect of the above formula.

After which it would round each of the results independently (ideally using a user entered value from a textbox or similar i.e 5 if I wanted to round to nearest 5) and then output the scaled values between the start and end of the selection.

This only needs to be for a one dimensional range of values arranged in a single column, it does not need to work across multiple columns. Nor does it need to work across multiple selections though bonus points if you can manage that.

Edit: This is the code I've got so far, but I've been struggling to figure out how to get the button to work

Appscript

var ui = SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
const button = document.querySelector('input')


button.addEventListener('click', scale)

function onOpen() {
 ui.createMenu('Custom Menu')
  .addItem('Show sidebar', 'showSidebar')
  .addToUi()
}

function showAlert(obj) {
  var result = ui.alert(obj)
}


function showSidebar(){

  var html = HtmlService.createHtmlOutputFromFile('Sidebar')
    .setWidth(300)
  SpreadsheetApp.getUi().showSidebar(html)

}

function scale(){
  var minVar = document.getElementById('minVar').value
  var maxVar = document.getElementById('maxVar').value

  var selection = SpreadsheetApp.getActiveSpreadsheet().getSelection()
  var activeRange = selection.getActiveRange()
  showAlert(activeRange.data)

}

HTML

<!DOCTYPE html>
<html>

<head>
  <style>
    /* Added light green background to page*/
    body {
      background-color: darkgrey;
    }
    div {
      width: 90%;
      border-style: double;
      border-width: 2px;
      border-color: red;
      margin-top: 5px;
      /*right left set to auto centers content */
      margin-left: auto;
      margin-right: auto;
      padding: 3px;
      background-color: white;
    }
    h1 {
      text-align: center;
    }
  </style>
</head>

<body>
  <div>
  <label>Minimum Value,</label>
  <input type="text" id="minVar" value="">
  </div>

  <div>
  <label>Maximum Value,</label>
  <input type="text" id="maxVar" value="">    
  </div>

  <div>
  <input type="button" value="Scale Selected Range" id="scaleStart">
  </div>

</body>

</html>

0 Answers