Is it possible to track Active Cell change in Google Sheet Sidebar-CodePudding

I'm writing a sidebar which displays additional information based on selected row/cell by user. It's rendering fine on sidebar open, but if the user changes active cell, I need to update content.

Apparently, I can add a "refresh" button in sidebar, but I really want to avoid clicking "refresh" every time. Putting it on timer also isn't very good cause will just spam with unnecessary requests to sheet app.

Has anyone ever did something similar and that approach did you use?

Maybe it's possible somehow to get event about user changing active cell into the sidebar javascript code?

CodePudding user response：

I've put together a prototype of a sidebar that collects all the cell the user clicks in. Starting with an onSelectionChange() trigger to record the cells the user clicks in and recording them to PropertyService Document Properties, when the user moves the mouse over the sidebar the cells that were selected will show up.

First we have a simple Sidedbar

HTML_Sidebar.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <textarea id="textArea" rows="10" cols="35" onm ouseenter="onMouseEnter()">
    </textarea>
    <?!= include('JS_Sidebar'); ?>    
  </body>
</html>

Next we have the client side code

JS_Sidebar.html

<script>
  function onm ouseEnter() {
    try {
      google.script.run.withSuccessHandler( 
        function(selections) {
          let textArea = document.getElementById("textArea");
          let text = textArea.value.trim();
          selections.forEach( cell => {
              text = text   "You clicked cell " cell "\n";
            }
          );
          textArea.value = text;
        }
      ).getLatestSelections();
    }
    catch(err) {
      alert(err);
    }
  }
</script>

Now for all the server side code.

Code.gs

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function showSidebar() {
  var html = HtmlService.createTemplateFromFile("HTML_Sidebar");
  html = html.evaluate();
  SpreadsheetApp.getUi().showSidebar(html);
}

function onSelectionChange(event) {
  try {
    // event = {"range":{"columnEnd":3,"columnStart":3,"rowEnd":1,"rowStart":1},"authMode":"LIMITED","source":{},"user":
    //                  {"email":"[email protected]","nickname":"xxxxxxxxx"}} 
    let properties = PropertiesService.getDocumentProperties();
    let property = properties.getProperty("_Selections");
    if( !property ) property = "";
    property = property   JSON.stringify(event)   "\n";
    properties.setProperty("_Selections",property);

  }
  catch(err) {
    Logger.log("Error in onSelectionChange: " err)
  }
}

function getLatestSelections() {
  try {
    let properties = PropertiesService.getDocumentProperties();
    let property = properties.getProperty("_Selections");
    if( !property ) property = "";
    properties.deleteProperty("_Selections");
    properties = property.split("\n");
    properties.pop(); // remove the last  \n element
    return properties.map( property => {
        let cell = JSON.parse(property);
        return getRangeA1(cell.range.rowStart,cell.range.columnStart);
      }
    );
  }
  catch(err) {
    Logger.log("Error in getLatestSelections: " err)
  }
}

function getRangeA1(row,col) {
  try {
    let colNum = col;
    let colName = "";
    let modulo = 0;
    while( colNum > 0 ) {
      modulo = (colNum - 1) % 26;
      colName = String.fromCharCode(65   modulo)   colName;
      colNum = Math.floor((colNum - modulo) / 26);
    }
    colName = colName (row);
    return colName;
  }
  catch(err) {
    throw "Error in getRangeA1: " err;
  }
}

Screen shot

CodePudding user response：

I understand that you want to record when the active cells change on a Sheet, and you want to do it without resorting to timers or buttons. If my understanding of your scenario is correct, then your best bet is using onSelectionChange triggers.

You can introduce an onSelectionChange trigger in your script to execute a refresh every time that the user changes the active cell. Leave a comment below if you have questions about this approach.