Catching onSelectionChange in the frontend side for a Google Sheets add-on

user_5948934 0
Viewed 412

In Google Sheets, I have an add-on that consists of two parts backend code.gs and frontend index.html, where the index.html is a sidebar shown upon a click in the menu. Here is code.gs:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('My add-on')
    .addItem('Test', 'openSideBar')    
    .addToUi();
}

function onSelectionChange(e) {
  Logger.log("Selection changed ");     
}

function openSideBar() {
  var html = HtmlService.createHtmlOutputFromFile('Index');
  SpreadsheetApp.getUi().showSidebar(html);
}

function getCurrentSelection() {
  var currentCell = SpreadsheetApp.getCurrentCell();

  return { column: currentCell.getColumn(), row: currentCell.getRow() };
}

Here is the documentation for onSelectionChange. The Logger.log("Selection changed") is indeed called when the selection is change. But, I need to notify or make a change in the frontend side when the user clicks on a cell and changes the selection.

Here is the index.html:

<!DOCTYPE html>
<html>
<head>
    <base target="_top">
</head>
<body>
    Test
    <input type="text" id="formula" />
    <script>
      function onSelectionChangeJs(e) {
        document.getElementById("formula").value = e ; 
      }

      function loadCell() {
        google.script.run.withSuccessHandler(onSelectionChangeJs).getCurrentSelection(); 
      }
    </script>
    <button onclick="loadCell()">Get cell</button>
</body>
</html>

While currently I am checking the current selection upon a button click, I need onSelectionChangeJs to get called automatically when a selection is changed in the sheet.

I believe it's a common need for an add-on development. So does anyone know how to let onSelectionChange of code.gs notify onSelectionChangeJs of index.html?

google-apps-script google-sheets
1 Answers

It sounds like when a user changes a cell in Sheets, you want that change to be reflected in an HTML page, right? What don't you send that value to the HTML? It would require refreshing the HTML page I think, but you could rebuild with that new parameter. I don't know enough about the add-on to say more. But if you have a onSelectionChange() triggering Logger.log only. It could trigger a function that refreshes the HTML with e inserted into html id "formula". You can't change the js directly with the code, but you can write the code.gs that does the same thing as the html js. Is that in the direction of what you need?

user_145497020
Related