How can I add a horizontal scrollbar in the google sheet similar to form control scrollbar in excel?

google-apps-script google-sheets scrollbar

13,206

This kind of control is simply not available natively within Google Sheets. I found a recent response from the Google Support Forums to indicate this is still the case.

If you want to build this functionality out yourself, it is actually now possible to do so, by taking full advantage of the Apps Script platform and their Google Sheets and HTML Service scripting APIs. You can create a dialog box with HTML and JS, which can have any inputs you want, including range sliders, and it can send the values back to the Google Sheet script, which can then save it to a cell. I put together a barebones test to make sure it would work, and it does:

I used Menu -> Tools -> Script Editor, and then created these two files:

Code.gs:

// Trigger dialog to be added to menu on file open
function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Dialog')
      .addItem('Open', 'openDialog')
      .addToUi();
}

function openDialog() {
  var html = HtmlService.createHtmlOutputFromFile('index');
  SpreadsheetApp.getUi()
      .showModalDialog(html, 'Dialog title');
}

function saveSliderVal(updatedVal){
  var sheetToSaveTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Gantt');
  if (!sheetToSaveTo){
    sheetToSaveTo = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  }
  var cellToSaveTo = sheetToSaveTo.getRange('A1:A1');
  cellToSaveTo.setValue(updatedVal);
}

index.html:

<!DOCTYPE html>
<html>
<head>
    <base target="_top">
</head>
<body>
    <script>
        function update() {
            document.getElementById('val').innerText = document.getElementById('input').value;
        }
        function save() {
            // Call sheet function
            google.script.run.saveSliderVal(parseInt(document.getElementById('input').value, 10));
        }
    </script>
    <input id="input" type="range" min="0" max="365" onchange="update()" oninput="update()" value="0"
        step="1" />&nbsp<span id="val">0</span>
    <br />
    <button id="save" onclick="save()">Save to Gantt</button>
</body>
</html>

Then reload the sheet after saving your script, and you should see Dialog -> Open as a new menu item. Clicking it will bring up your custom HTML.

13,206

Author by

Karel

Updated on June 05, 2022

Comments

Karel almost 2 years

I would like to add a horizontal scrollbar that will provide the values 0-365 based on its position and use this value for calculation.

I am creating a Gantt chart, the same as is on the page https://www.vertex42.com/ExcelTemplates/excel-gantt-chart.html

There is a horizontal scrollbar that helps "to move" with the calendar.

I have put there a number that I must change manually. I haven't found any solution on the internet.