0

I have a sidebar in google sheets that has all the sheet names as list items:

index.html:

<div class="sidebar">
    <ul class="sidebar-nav">

      <li class="nav-item">
        <a class="nav-btn" data-sheet-name="Sheet 1" id="sheet1" href="#">
          <div>Sheet 1</div>
        </a>
      </li>

      <li class="nav-item">
        <a class="nav-btn" data-sheet-name="Sheet 2" id="sheet2" href="#">
          <div>Sheet 2</div>
        </a>
      </li>
    </ul>
</div>

When clicking each item, the selected sheet gets activated and I add the class name "active-nav" to the list item. Here is the client-side javascript:

<script>
  window.onload = function() {
  const navBtns = document.querySelectorAll('.nav-btn');

 const idToFuncMap = new Map([
    ['sheet1', google.script.run.goToSheet1],
    ['sheet2', google.script.run.goToSheet2],
  ]);

  navBtns.forEach((el) => {
      el.addEventListener('click', () => {
          const func = idToFuncMap.get(el.id);
          if (func) func();
      });
  });

  navBtns.forEach(function(link) {
      link.addEventListener("click", function() {
          navBtns.forEach(function(link) {
              if (link.classList.contains("active-nav")) {
                  link.classList.remove("active-nav");
              }
          });
          this.classList.add("active-nav");
      });
  });
}
</script>

Also, depending on the currently active sheet, I am adding the active class name to the list item (communication from server to client):

  function onSuccess(sheetName) {
    navBtns.forEach(function(navBtn) {
    if (navBtn.getAttribute("data-sheet-name") === sheetName) {
      navBtn.classList.add("active-nav");
    } else {
      navBtn.classList.remove("active-nav");
    }
  });
  }

 google.script.run.withSuccessHandler(onSuccess).getActiveSheetName();

My Code.gs looks like this:

function showNavigatorSidebar() {
  const ui = SpreadsheetApp.getUi();
  const html = HtmlService.createTemplateFromFile("index").evaluate();
  ui.showSidebar(html);

function getActiveSheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

function activateSheet(sheetName) {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(sheetName);
  sheet.activate();
}

// Go to functions
// BUTTONS
function goToSheet1() {
  activateSheet('Sheet 1');
}

function goToSheet2() {
  activateSheet('Sheet 2');
}
}

Everything works fine, however, when changing the sheets (tabs) in google sheets:

enter image description here

I want the html in the sidebar to be automatically updated (without refreshing).

Is there a way to achieve that? For example, when selecting sheet 2 in google sheets (server), I want the button (list item) for Sheet 2 to update accordingly and have the 'active-btn' class (without having to click the button in the client html).

Any help is appreciated!

SevenSouls
  • 539
  • 3
  • 12
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand `without refreshing` of `when changing the sheets (tabs) in google sheets, I want the html in the sidebar to be automatically updated (without refreshing)`. Can I ask you about the detail of it? – Tanaike Jan 19 '23 at 04:52
  • No worries my bad I might have not explained it well. By that I meant when switching tabs (sheets) in google sheets, I want the sidebar html to be updated without having to refresh the entire web page. By updating the sidebar I mean adding the class name `active-nav` to the navBtn element that corresponds to the currently active sheet – SevenSouls Jan 19 '23 at 04:54
  • Thank you for replying. I understood it. In the current stage, unfortunately, there is no method for controlling the sidebar and dialog from Google Apps Script. So, if your goal is required to be directly achieved without reloading the sidebar, it is required to check it from Javascript using the sidebar. In that case, it is required to run Javascript at regular intervals. I apologize for this. – Tanaike Jan 19 '23 at 04:59
  • Thank you for the reply. Okay... Can this be accomplished with the `onSelectionChange(e)` trigger? https://developers.google.com/apps-script/guides/triggers – SevenSouls Jan 19 '23 at 05:04
  • Thank you for replying. About `Can this be accomplished with the onSelectionChange(e) trigger?`, unfortunately, it cannot be achieved. This thread might be related to your this question. https://stackoverflow.com/q/62647068 – Tanaike Jan 19 '23 at 05:05
  • Okay thank you! What about this? It looks promising, have you seen it? https://stackoverflow.com/questions/73805440/update-scriptlets-in-html-without-reloading-page – SevenSouls Jan 19 '23 at 05:11
  • Thank you for replying. I think that unfortunately, the method of your provided URL cannot be used for achieving your goal. I apologize for this. – Tanaike Jan 19 '23 at 05:12

0 Answers0