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

Question

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

Possible duplicate of [How do I make a Sidebar display values from cells?](https://stackoverflow.com/questions/30628894/how-do-i-make-a-sidebar-display-values-from-cells) — Rubén, Aug 05 '19 at 15:10
@Rubén Thank you, I know and found that post before but I post this question because I would like to know if there is another way that not to use setInterval() or not. Because I used to try that way before and found that made my spreadsheet sometime run slower. Thanks — Nhong, Aug 05 '19 at 16:12
You should mention that on the question. As this question already got an answer an it was upvoted and accepted you could consider to post a new / follow up question. — Rubén, Aug 05 '19 at 16:21

score 6 · Accepted Answer · answered Aug 05 '19 at 13:32

6

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();
  }
}

answered Aug 05 '19 at 13:32

Amit Agarwal

10,910
1
32
43

Thank you. But I would like to know if there is another way that give the same result. I use to try this way before and that made my spreadsheet slower. If this is the only choice. So I have to use a setInterval() to make my sidebar get live data. Thank you very much. ขอบคุณมากครับ – Nhong Aug 05 '19 at 16:22
1

If you are thinking about a contextual trigger, similar like for the Google Card Service for gmail Addons - unfortunately it is currently not available for sidebars. – ziganotschka Aug 06 '19 at 11:05

score 2 · Answer 2 · answered Oct 25 '19 at 04:46

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
  };
}

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

2 Answers2

Linked