CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Nhong
Nhong

Reputation: 109

Google Sheets custom sidebar: How to display a current cell value in sidebar without reload the sidebar?

I have a list of fruits name in a sheet. Then I made a custom sidebar that will show an information of a selected name by selecting a cell in a sheet without reloading the sidebar or without clicking any button in the sidebar. Only just select a cell. Is it possible? And How can I do it? Thank you in advance

enter image description here enter image description here enter image description here

Upvotes: 1

Views: 3446

Answers (2)

acjay
acjay

Reputation: 36581

Below is a variant of Amit Agarwal's answer. I used setTimeout and a tighter iteration loop, and also added some logic to only run when the user is likely to actually be navigating the sheet. Hopefully it prevents unnecessary run-time quota usage.

In the sidebar HTML, which will run in your browser:

<h3>Location</h3>
<div id="location"> </div>

<script>
// Stop the loop when the window isn't focused, and restart when it comes back to focus.
var isFocused = true;
window.addEventListener("blur", function() {
  isFocused = false;
});
window.addEventListener("focus",function() {
  isFocused = true;
  setLocation();
});

function setLocation() {
  google.script.run
    .withSuccessHandler(function (result) {
      document.getElementById("location").innerText = `Sheet: ${result.sheet} Range: ${result.range}`;
    })
    .getLocation();

  if (isFocused) {
    setTimeout(setLocation, 200);
  }
}

setLocation();

</script>

In the Google Apps Script file, which will run on the server:

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('My Menu')
      .addItem('Open', 'showSidebar')
      .addToUi();
}

function showSidebar() {
  // You can use a template to inject initialization data
  var html = HtmlService
      .createTemplateFromFile('Sidebar')
      .evaluate()
      .setTitle('My Sidebar')
      .setWidth(300);

  SpreadsheetApp.getUi()
      .showSidebar(html);
}

function getLocation() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheetName = spreadsheet.getSheetName();
  var selectedRange = spreadsheet
    .getSelection()
    .getActiveRange()
    .getA1Notation();

  return {
    sheet: sheetName,
    range: selectedRange
  };
}

Upvotes: 2

Amit Agarwal
Amit Agarwal

Reputation: 11278

You can put a setInterval() method in the sidebar that calls a server-side function every, say, 2 seconds. The server-side function should return the value of the currently selected cell. The client-side function should update the HTML content of the sidebar base on the response.

window.setInterval(function() {
  google.script.run.withSuccessHandler(update).getActiveCell();
}, 2000);

function update(e) {
  if (e === "Apple") {
    showApple();
  }
}

Upvotes: 6

Related Questions