Google apps scripts: Serving content to sidebar

Question

I added a sidebar to a spreadsheet and I am able to pass variables from the sidebar to the sheet (using google.script.run). How do I pass values back from the spreadsheet to the sidebar, modifying the sidebar content?

EDIT: Adding my code. Basically I enter a number in the sidebar, find its row position in the spreadsheet, and need to pass that row fields back to the sidebar, to update the divs (#fieldA, fieldB).

Once I get the row number, I just need to add fieldA, fieldB values to a variable , but I don't know how to pass that back to the sidebar.

Spreadsheet

Number  FieldA  FieldB
111     1a      1b
222     2a      2b
333     3a      3b

index.html

<form>
<input type="text" id="someNumber">
<button type="button" onClick="findNumber()">Find number</button>
</form>
<div id="fieldA"></div>
<div id="fieldB"></div>

<script>
function findNumber(){
  var number = document.getElementById("someNumber").value;
  google.script.run.findNumber(number);
}
</script>

code.gs

var sheet = SpreadsheetApp.getActiveSheet();

function findNumber(a){
var lastRow = sheet.getLastRow();
var lookRange = sheet.getRange(1, 1, lastRow);
var values = lookRange.getValues();

for (i in values) {
  if (values[i] == a) {
   var updateRow = 1+ parseInt(i);
   Logger.log(values[i] + " is in row " + updateRow);
   break;
  }
 }

}

There is no "built-in" way to pass data to a sidebar except when the sidebar loads for the first time. You can put a timer into an HTML ` — Alan Wells, Feb 16 '16 at 16:54
Two pieces of documentation that might be helpful: Scriptlets, which let you run GAS code inside of the HTML you serve -https://developers.google.com/apps-script/guides/html/templates#scriptlets and this sort of sub-type of scriplet which lets you pass variables from the GAS function to the sidebar when you create it: https://developers.google.com/apps-script/guides/html/templates#pushing_variables_to_templates EDIT: Depending on what your code is, you may be able to combine the above with a success handler: https://developers.google.com/apps-script/guides/html/communication#success_handlers — Jens Astrup, Feb 16 '16 at 17:12
Have a look at this answer : http://stackoverflow.com/questions/30628894/how-do-i-make-a-sidebar-display-values-from-cells/30634581#30634581 — Serge insas, Feb 16 '16 at 22:18
Six years have passed (now 2020), unfortunately, there is still no built-in solution to this problem. Like @AlanWells said, the only way is to set a timer for monitoring the value of cells. — Eric Chang, May 22 '20 at 08:29

score 0 · Answer 1 · answered Feb 17 '16 at 23:29

I have a script run on open that gets the defaults, then populates the correct form fields. I use jquery.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>
<script>

 $(function() {
google.script.run.withSuccessHandler(populateQuestions).withFailureHandler(fail).getCEDefaults();
 });

 function populateQuestions(defaults){ //defaults is the return variable from getCEDefaults() on the .gs file
 $("#enableDisable").attr('checked', defaults.submitTrigger);
};

Google apps scripts: Serving content to sidebar

1 Answers1